数据库完整性与生产可靠性审计
检查表结构、查询、事务、迁移和备份恢复链路,输出按证据与严重程度分级的风险及修复报告。
完整提示词
I want an in-depth, systematic, evidence-first, and production-oriented analysis of the entire application database layer. Main objective: > Determine whether the schema, constraints, relationships, indexes, queries, transactions, migrations, locking, connection pooling, isolation levels, data lifecycle, backup/recovery, and application-to-database interactions can cause data corruption, lost updates, duplicate records, cross-tenant exposure, performance collapse, deadlocks, deployment failures, or permanent compromise of data integrity. This is not: - a generic SQL checklist - solely a query performance audit - solely an index audit - solely a schema review - an automatic demand for normalization - an automatic demand for denormalization - an automatic demand to migrate to PostgreSQL/MySQL - an assumption that an ORM guarantees correctness - advice to blindly add indexes everywhere - advice to wrap every operation in a transaction Priority: **data corruption > tenant/data boundary violation > lost updates > broken constraints > unsafe migrations > deadlocks > connection exhaustion > performance cliffs > maintainability > hardening** ## 1. DATABASE INVENTORY Determine: - engine - version - deployment topology - primary/replicas - schemas/databases - extensions - ORM - connection pool - migration tool - backup/PITR - read replicas - caches If not verified: **DATABASE TOPOLOGY NOT VERIFIED** ## 2. TABLE INVENTORY For every critical table: ```text Table: Purpose: Primary key: Tenant key: Owner key: Foreign keys: Unique constraints: Indexes: High-volume: Write frequency: Retention: ``` ## 3. SOURCE OF TRUTH For each business entity, establish which datastore is authoritative. ## 4. PRIMARY KEY Verify: - type - generation strategy - collision resistance - sequence limits - UUID semantics - composite keys ## 5. TENANT KEY A multi-tenant table lacking an explicit tenant boundary is a high-value review surface. ## 6. FOREIGN KEY Do not automatically treat the absence of a foreign key as a bug. Determine whether integrity is enforced by another layer and whether that mechanism truly functions. ## 7. UNIQUE CONSTRAINT A business invariant requiring uniqueness must not rely solely on: ```text SELECT ↓ if not exists ↓ INSERT ``` under concurrency. ## 8. NULLABILITY Verify that the schema accurately reflects the actual domain model. ## 9. DEFAULTS Database defaults vs application-level defaults. ## 10. ENUM Verify migration and backward compatibility implications. ## 11. CHECK CONSTRAINT Useful for enforcing hard business invariants. ## 12. CASCADE Audit: - ON DELETE CASCADE - SET NULL - RESTRICT ## 13. ACCIDENTAL MASS DELETE Parent record deletion can inadvertently purge an extensive relation graph. ## 14. SOFT DELETE If present: - unique constraints - query scopes - restoration logic - related entities - index coverage ## 15. DELETED DATA LEAK Default query scopes must be uniformly enforced. ## 16. ARCHIVE Archived state is not automatically deleted state. ## 17. TEMPORAL STATE Created, updated, and deleted timestamps. ## 18. CLOCK Database time vs application runtime time. ## 19. MONEY Never use floating-point types for precise financial amounts where inappropriate. ## 20. CURRENCY An amount missing an explicit currency designator can be an architectural flaw. ## 21. DECIMAL PRECISION Numeric overflow and truncation risks. ## 22. TIMEZONE Timestamp with vs without timezone semantics. ## 23. DATE-ONLY Do not unnecessarily convert date-only business concepts into timestamps. ## 24. TEXT LENGTH Unbounded text columns can carry substantial storage and performance consequences. ## 25. JSON COLUMN Can be valid, but verify: - schema drift - queryability - index strategies - security-sensitive attributes ## 26. ARRAY COLUMN An architectural trade-off, not automatically an anti-pattern. ## 27. NORMALIZATION Identify duplicate authoritative facts. ## 28. DENORMALIZATION If employed, must possess an explicit synchronization and invalidation model. ## 29. MATERIALIZED VIEW Refresh mechanisms and concurrency impact. ## 30. GENERATED COLUMN Engine compatibility and source authority. ## 31. INDEX INVENTORY Identify: - primary keys - unique constraints - foreign keys - filter predicates - sort orders - composite indexes - partial indexes - functional expressions ## 32. MISSING INDEX Evidence must incorporate actual query patterns or production execution plans. ## 33. UNUSED INDEX Can increase write amplification. Do not prune based solely on a brief observation window. ## 34. DUPLICATE INDEX Completely redundant indexes. ## 35. COMPOSITE INDEX ORDER Must align with query predicate selectivity and sort orders. ## 36. SELECTIVITY An index on a low-cardinality column is not automatically beneficial. ## 37. COVERING INDEX Deploy only where the workload explicitly justifies the overhead. ## 38. QUERY INVENTORY Identify: - hot paths - expensive analytical reports - search queries - reporting dashboards - background workers - data export pipelines ## 39. `SELECT *` Can inflate payload size and network I/O, but do not flag without context. ## 40. FULL TABLE SCAN Not problematic on small lookup tables. ## 41. UNBOUNDED QUERY High-value finding: ```text SELECT ... without LIMIT ``` executed against large, user-controlled result sets. ## 42. SORT Explicit sorting without supporting index structures on large tables. ## 43. OFFSET PAGINATION High offsets induce severe performance degradation. ## 44. KEYSET PAGINATION Preferred alternative where product requirements permit. ## 45. N+1 ORM loop yielding a distinct query per parent item. ## 46. BULK WRITE Row-by-row iteration vs batched or multi-row writes. ## 47. TRANSACTION Define crisp, atomic business boundaries. ## 48. TOO LARGE TRANSACTION Prolonged lock retention, WAL/undo log growth, and heavy contention. ## 49. TOO SMALL TRANSACTION Inadvertent partial state persistence upon failure. ## 50. LOST UPDATE Read-modify-write sequences lacking concurrency controls. ## 51. OPTIMISTIC LOCK Version or timestamp column where appropriate. ## 52. PESSIMISTIC LOCK Deploy only when the contention profile explicitly warrants it. ## 53. ISOLATION LEVEL Determine the actual database default and any per-transaction overrides. ## 54. WRITE SKEW Snapshot and repeatable-read isolation semantics where relevant. ## 55. PHANTOMS Queries validating business invariants against concurrent insertions. ## 56. DEADLOCK Investigate inconsistent lock acquisition ordering. ## 57. DEADLOCK RETRY Deadlocks can represent expected concurrency outcomes. The application must safely retry if the operation is idempotent and retry-safe. ## 58. LOCK WAIT Prolonged lock contention can manifest as intermittent application latency. ## 59. `FOR UPDATE` Employ with caution and tight row-locking scopes. ## 60. ADVISORY LOCK Do not treat advisory locks as a universal concurrency panacea. ## 61. CONNECTION POOL Calculate: ```text instances × pool per instance ``` ## 62. DB MAX CONNECTIONS Compare aggregate pool allocation against database limits. ## 63. SERVERLESS Connection burst storms exhausting database capacity. ## 64. IDLE CONNECTION Pool idle timeout and reaping configurations. ## 65. CONNECTION LEAK Request codepaths failing to release pooled connections. ## 66. TRANSACTION LEAK Open transactions persisting across outbound external HTTP calls. ## 67. READ REPLICA Replication lag implications. ## 68. READ-AFTER-WRITE Critical operational flows may mandate primary node routing. ## 69. REPLICA FAILOVER Application recovery and failover behavior. ## 70. DB FAILOVER Connection retry semantics during primary failovers. ## 71. PREPARED STATEMENTS Compatibility with connection pooling proxies (e.g., PgBouncer transaction mode). ## 72. STATEMENT TIMEOUT Safeguard against runaway or pathological queries. ## 73. LOCK TIMEOUT Essential for preventing indefinite lock acquisition hangs. ## 74. QUERY CANCELLATION Handling client disconnect events. ## 75. MIGRATIONS Inventory all database migration files. ## 76. MIGRATION ORDER Ensure fully deterministic execution ordering. ## 77. MIGRATION IMMUTABILITY Applied migrations should never be altered silently. ## 78. DESTRUCTIVE CHANGE Column drops, renames, and type mutations. ## 79. BACKFILL Managing large-scale data transformation workloads. ## 80. INDEX CREATION Table locking and blocking semantics during builds. ## 81. NOT NULL MIGRATION Handling existing rows and compatibility with older application code. ## 82. ROLLBACK Can the previous application binary function against the migrated schema? ## 83. DATA MIGRATION Verification of data transformation correctness. ## 84. PARTIAL MIGRATION Handling failures midway through migration execution. ## 85. MIGRATION CONCURRENCY Managing concurrent execution across multiple application instances. ## 86. SEED Ensuring seed scripts are safe against production environments. ## 87. TEST DATA Prevent test data leakage into production environments. ## 88. BACKUP Cross-reference disaster recovery audit requirements. ## 89. PITR Verify point-in-time recovery is active, not merely assumed. ## 90. DATA ENCRYPTION Encryption at rest and in transit per the threat model. ## 91. DATABASE CREDENTIAL Enforce least privilege principles. ## 92. APP DB USER Does the runtime application user require DDL or DROP permissions? ## 93. READ-ONLY USER Dedicated accounts for analytical reporting and read replicas. ## 94. ROW-LEVEL SECURITY If utilized: audit policies, service accounts, and bypass mechanisms. ## 95. RLS SERVICE ROLE Can bypass all row-level security policies. ## 96. RLS POLICY Tenant isolation predicate correctness. ## 97. SECURITY DEFINER High-value PostgreSQL-specific privilege escalation review surface. ## 98. SEARCH PATH Fix search paths on security-definer functions. ## 99. STORED PROCEDURE Authentication and encapsulated business logic reviews. ## 100. TRIGGER Hidden side effects executing implicitly. ## 101. TRIGGER ORDER Execution ordering where the engine supports multiple triggers. ## 102. AUDIT TABLE Permissions governing modification or deletion of audit logs. ## 103. PII Avoid redundant duplication of personally identifiable information. ## 104. RETENTION Automated technical cleanup and purging policies. ## 105. ARCHIVE JOB Can induce severe locking and I/O load. ## 106. DELETE BATCHING Unbatched mass deletions stalling database performance. ## 107. VACUUM/GC Engine-specific maintenance mechanisms. ## 108. TABLE BLOAT Bloat monitoring where applicable. ## 109. STATISTICS Stale optimizer statistics generating suboptimal execution plans. ## 110. QUERY PLAN Execute EXPLAIN or EXPLAIN ANALYZE only in safe environments, avoiding destructive operations on production. ## 111. PLAN ESTIMATE ERROR Cardinality estimation mismatches. ## 112. PARAMETER SKEW Same query shape with differing parameters yielding radically different plans. ## 113. SLOW QUERY LOG Essential empirical evidence. ## 114. METRICS - CPU utilization - IOPS - query latency - connection counts - lock waits - deadlock frequency - buffer cache hit ratio - replication lag - storage growth ## 115. ALERTING Align alerts with concrete operational failure modes. ## 116. CAPACITY Predictable storage volume growth. ## 117. AUTOGROW Provider storage autogrowth ceilings. ## 118. DISK FULL Catastrophic database outage scenario. ## 119. LARGE TABLE Growth projection modeling. ## 120. PARTITIONING Do not recommend partitioning without concrete query and maintenance workload evidence. ## 121. SHARDING Never recommend sharding as the default response to scaling. ## 122. EVIDENCE, STATUS AND FALSE POSITIVES Evidence tiers: ```text A - reproduced: production metrics, query plans with actual execution, logs or a safe reproduction show the behavior B - complete path: schema, constraints, transaction code and data flow fully show the failure C - strong static evidence: code or schema shows the path, but data volume, configuration or runtime behavior are not verified D - inference: depends on engine version, isolation level, data distribution or topology not verified E - hardening: stronger design or configuration without a current failure path ``` Status: - **CONFIRMED** - tier A or B evidence shows the failure or exploit path. - **LIKELY** - tier C evidence. - **NOT VERIFIED** - depends on runtime state, settings or versions that could not be checked (tier D). Never present tier D as confirmed. - **NOT APPLICABLE** - the component or pattern is not used. - **CONTROLLED** - the risk exists but another control contains it. - **HARDENING** - improvement without a current failure path (P4). False-positive rules: - A missing index on a small or rarely queried table is not a performance defect. - Intentional denormalization with a defined source of truth and a reconciliation path is a design choice, not an integrity defect. - The engine's default isolation level is a finding only with a concrete anomaly (lost update, write skew) on a real code path. - Missing partitioning, sharding or read replicas is not a defect without a measured or projected capacity problem. - Integrity enforced in the application is weaker than a constraint, but a finding only when a concrete path bypasses it (concurrent requests, other writers, imports). - Engine behavior (locking, planner, replication) differs by version; state the version the finding depends on. Do not report a missing best practice as a confirmed defect unless there is a concrete failure, exploit, correctness, reliability, or operational path. ## 123. FINDING FORMAT ```text ID: Severity: Category: Database: Schema/Table: Query/Transaction: Evidence tier: Status: Trigger: Failure path: Data impact: Performance impact: Blast radius: Evidence: Root cause: Fix: Regression test: Production verification: Complexity: ``` ## 124. SEVERITY P0: - global or irrecoverable data corruption - practical tenant boundary collapse at the database layer - catastrophic production deletion lacking viable recovery paths P1: - repeatable lost updates on critical state - unsafe migration with a concrete corruption or outage path - connection pooling or locking failures capable of triggering major outages - broken uniqueness or integrity constraints with significant business impact P2: - significant performance bottlenecks or integrity weaknesses P3: - limited scope database defects P4: - tuning and hardening suggestions ## 125. OUTPUT `ULTIMATE_DATABASE_AUDIT.md` ## 126. SECOND PASS Simulate or analyze: - two concurrent updates to identical rows - duplicate entity creation attempts - parent record deletion cascading - reading stale data from a replica - database failover handling - maximum connection pool allocation across autoscaled replicas - slow query performance scaled to 10x row volume - migrations running concurrently across old and new application instances - rollback execution following schema migration - disk storage approaching maximum capacity - database backup restoration in an isolated sandbox ## 127. FINAL QUALITY GATE Verify: - schema definitions - constraint completeness - index alignment - query plans - transaction boundaries - concurrency safeguards - connection pool sizing - replica synchronization - migration safety - credential permissions - backup and recovery readiness - capacity and growth planning # FINAL RULE Looking for issues such as: ```text checkout flow: SELECT stock ↓ stock = 1 Request A and B both read 1 ↓ both write stock = 0 ↓ two orders created ↓ inventory invariant violated ``` or: ```text max app replicas = 30 pool = 20 ↓ potential DB connections = 600 DB limit = 250 ↓ autoscaling under load accelerates database outage ```
分类与标签
编程开发、数据库审计、数据完整性、事务并发、迁移安全、备份恢复