Database Audit

Database Audit

Overview

The database is the foundation. If the schema is wrong, everything built on top is fragile.

Core principle: Schema should enforce business rules. Don't trust application code to maintain data integrity.

The Iron Laws

1. NO NULLABLE COLUMN WITHOUT DOCUMENTED REASON. NO MISSING INDEX ON FOREIGN KEYS.
2. EVERY QUERY THAT TOUCHES PRODUCTION MUST HIT AN INDEX — NEVER A FULL TABLE SCAN ON LARGE TABLES.
3. TEXT-HEAVY TABLES THAT GROW UNBOUNDED (logs, activity, notifications) MUST HAVE RETENTION, PARTITIONING, OR ARCHIVAL STRATEGY.

When to Use

• Auditing database architecture
• Reviewing migration files
• Investigating data inconsistency
• Before schema changes
• Performance investigation (query-related)
• During any codebase audit
• When tables have many text columns (logs, activity feeds, notifications)
• When tables are expected to grow unbounded (audit trails, event stores)
• When query performance degrades over time (missing index symptoms)

When NOT to Use

• Application-level performance only (use performance-audit)
• API design concerns (use api-design-audit)
• Code-level architecture (use architecture-audit)

Anti-Shortcut Rules

YOU CANNOT:
- Audit schema from ORM models alone — check the ACTUAL database schema or migration files
- Say "indexes are fine" without checking query patterns — indexes serve queries, not tables
- Skip nullable column analysis — every NULL column is a potential bug waiting to happen
- Accept missing FK constraints because "the app handles it" — apps crash, constraints don't
- Ignore migration rollback plans — every migration needs a reverse
- Trust column names match their purpose — verify types and constraints match semantics
- Skip data integrity checks — orphaned records, duplicates, and invalid states exist silently
- Assess schema health without understanding the query patterns — schema exists to serve queries
- Ignore large-table strategy — any table that grows unbounded WILL become a bottleneck
- Skip EXPLAIN/ANALYZE on critical queries — "it works" does not mean "it performs"
- Assume TEXT columns don't need indexing — if you query them, you index them
- Accept "we'll add indexes later" — by the time you notice, production is already slow

Common Rationalizations (Don't Accept These)

| Rationalization | Reality | |----------------|---------| | "The ORM handles foreign keys" | ORMs create references, databases enforce them. Check for DB-level constraints. | | "We don't need indexes yet, it's fast enough" | It's fast with 100 rows. Production has millions. | | "Nullable is easier, we'll fix it later" | NULL propagates through joins and aggregations, causing subtle bugs. | | "Money as FLOAT works fine for us" | Until you have rounding errors in financial reports. | | "We don't use migrations, we change the schema directly" | Direct changes = no rollback, no history, no reproducibility. | | "VARCHAR(255) is fine for everything" | It's a red flag that types weren't considered. | | "The log table doesn't need indexes" | It does when you query it. And you WILL query it. | | "We'll archive old data when it gets big" | Define "big". Set the threshold NOW, automate it NOW. | | "Full-text search is overkill" | TEXT columns without search indexes cause LIKE '%query%' full scans. | | "We only read recent rows" | Without a time-based index, "recent" still scans the whole table. | | "Notifications don't need optimization" | Users have thousands. Unread counts on every page load. Do the math. |

Iron Questions

1. Does every foreign key column have a database-level FK constraint? (not just ORM)
2. Does every foreign key column have an index?
3. Is every nullable column intentionally nullable? (documented reason?)
4. Is DECIMAL/NUMERIC used for all monetary values? (never FLOAT)
5. Does every table have timestamps (created_at, updated_at)?
6. Does every migration have a reversible rollback?
7. Are composite indexes in the correct order? (most selective first)
8. Are there tables with > 30 columns? (normalization issue)
9. Are there orphaned records? (FK data without parent)
10. Does the schema match the ORM models exactly?
11. Does EVERY query path hit an index? (run EXPLAIN on the top 20 queries)
12. Are high-volume tables (logs, activity, notifications) partitioned or have retention policies?
13. Are TEXT/JSONB columns that are queried/filtered indexed appropriately?
14. Does unread_count / badge_count hit an index or does it full-scan?
15. Are there queries doing LIKE '%text%' on unindexed TEXT columns?

The Audit Process

Phase 1: Schema Analysis

1. READ all migration files OR inspect actual schema
2. MAP all tables, columns, types, and constraints
3. IDENTIFY relationships (1:1, 1:N, M:N)
4. CHECK for orphaned tables (not referenced by any code)
5. CLASSIFY tables by growth pattern:
   - Static (config, settings) — rarely changes
   - Transactional (orders, trades) — grows with business
   - Unbounded (logs, activity, notifications) — ⚠️ grows forever

For each table, verify:

| Check | Question | |-------|----------| | Primary key | Does it exist? Is it appropriate (auto-increment, UUID, composite)? | | Column types | Are they appropriate? (VARCHAR(255) for emails? DECIMAL for money?) | | Nullable columns | Is NULL semantically meaningful or just lazy? | | Default values | Do they make business sense? | | Constraints | CHECK, UNIQUE, NOT NULL — are business rules enforced? | | Timestamps | createdat, updatedat present where needed? | | Soft deletes | If used, is deleted_at indexed? | | Growth pattern | Static, transactional, or unbounded? | | Retention | If unbounded, what's the archival/purge strategy? |

Phase 2: Relationship Integrity

1. EVERY foreign key MUST have a database-level FK constraint
2. EVERY FK column MUST have an index
3. CHECK cascade behavior (ON DELETE, ON UPDATE)
4. CHECK for orphan records (FK without matching parent)

Cascade rules:

| Relationship | ON DELETE | Reason | |-------------|-----------|--------| | Order → User | RESTRICT | Don't delete users with orders | | OrderItem → Order | CASCADE | Deleting order removes items | | Comment → User | SET NULL | Keep comments, lose attribution | | Session → User | CASCADE | User deletion clears sessions | | Notification → User | CASCADE | User deletion clears notifications | | ActivityLog → User | SET NULL | Keep audit trail, lose attribution |

Cascade decision tree:

| Question | If Yes | If No | |----------|--------|-------| | Should child records survive parent deletion? | SET NULL or RESTRICT | CASCADE | | Is the child meaningless without the parent? | CASCADE | RESTRICT or SET NULL | | Would deleting the parent cause data loss? | RESTRICT | CASCADE or SET NULL | | Is the FK column nullable? | SET NULL is an option | CASCADE or RESTRICT | | Is it an audit/compliance table? | SET NULL (preserve record) | CASCADE or RESTRICT |

Phase 3: Index Analysis — Core

1. EVERY foreign key column — indexed? (mandatory)
2. Frequent WHERE clause columns — indexed?
3. Frequent ORDER BY columns — indexed?
4. Composite queries — composite index in correct order?
5. Unused indexes — consuming write performance?
6. Missing covering indexes — queries reading table unnecessarily?

Index ordering rule for composite indexes:

Most selective column FIRST
= conditions before RANGE conditions

Example:
WHERE status = 'active' AND created_at > '2024-01-01'
→ INDEX (status, created_at) ✅
→ INDEX (created_at, status) ❌ (range before equality)

Index analysis checklist:

| Column Usage | Needs Index? | Notes | |-------------|-------------|-------| | Foreign key | ✅ Always | Required for JOIN performance | | WHERE clause (frequent) | ✅ Yes | Check query logs for frequency | | JOIN condition | ✅ Yes | Both sides of the join | | ORDER BY (on large tables) | ✅ Usually | Prevents filesort | | GROUP BY | ✅ Usually | Can improve aggregation | | SELECT only | ❌ No | Unless using covering index | | Boolean with low cardinality | ❌ Usually not | Unless combined in composite | | Soft-delete (deletedat) | ✅ Yes | Partial index WHERE deletedat IS NULL |

Phase 4: Index Analysis — High-Volume Tables (CRITICAL)

This phase is mandatory for any table that grows unbounded: logs, activity feeds, notifications, audit trails, event stores, analytics, sessions, messages.

4.1 Identify High-Volume Tables

HIGH-VOLUME INDICATORS:
- Table name contains: log, activity, event, notification, audit, session, message, analytics, metric, tracking
- Table has a timestamp column used for ordering/filtering
- Table has a user_id/entity_id FK for per-user queries
- Table grows by 100+ rows/day per user
- Table has no DELETE or PURGE mechanism
- Table has TEXT/JSONB columns storing variable-length data

4.2 Common Query Patterns on High-Volume Tables

Every high-volume table is queried in predictable patterns. Each pattern MUST hit an index:

| Query Pattern | Example | Required Index | Priority | |--------------|---------|----------------|----------| | Recent by user | WHERE user_id = ? ORDER BY created_at DESC LIMIT 20 | (user_id, created_at DESC) | 🔴 Critical | | Unread count | WHERE user_id = ? AND read = false | (user_id, read) WHERE read = false | 🔴 Critical | | Unread items | WHERE user_id = ? AND read_at IS NULL ORDER BY created_at DESC | (user_id, read_at, created_at DESC) | 🔴 Critical | | Filter by type | WHERE user_id = ? AND type = ? ORDER BY created_at DESC | (user_id, type, created_at DESC) | 🟠 High | | Date range | WHERE user_id = ? AND created_at BETWEEN ? AND ? | (user_id, created_at) | 🟠 High | | Global recent | ORDER BY created_at DESC LIMIT 50 | (created_at DESC) | 🟡 Medium | | Search by content | WHERE body ILIKE '%search%' | Full-text index (GIN/tsvector) | 🟠 High | | Aggregate count | SELECT COUNT(*) WHERE user_id = ? AND type = ? | (user_id, type) | 🟡 Medium | | Batch mark-read | UPDATE WHERE user_id = ? AND read = false | (user_id, read) | 🟠 High | | Cleanup old | `DELETE WHERE created_at 100,000: needs partitioning or archival □ Retention policy exists? → Auto-delete after N days? → Archive to cold storage? → Soft-delete with cleanup job?

#### 4.4 Text Column Indexing Strategy

Tables with many TEXT columns are performance traps. Audit each TEXT column:

| TEXT Column Purpose | Index Strategy | DDL Example |
|--------------------|---------------|-------------|
| Displayed only (title, body) | ❌ No index | — |
| Filtered with `=` | B-tree on hash | `CREATE INDEX ON t USING hash (column);` |
| Filtered with `LIKE 'prefix%'` | B-tree with text_pattern_ops | `CREATE INDEX ON t (column text_pattern_ops);` |
| Searched with `LIKE '%term%'` | GIN trigram | `CREATE EXTENSION pg_trgm; CREATE INDEX ON t USING gin (column gin_trgm_ops);` |
| Full-text searched | GIN tsvector | `CREATE INDEX ON t USING gin (to_tsvector('english', column));` |
| JSON queried by key | GIN jsonb_path_ops | `CREATE INDEX ON t USING gin (column jsonb_path_ops);` |
| Used in WHERE + ORDER | Composite B-tree | `CREATE INDEX ON t (status, column);` |

**The cardinal sin:**
```sql
-- 🔴 NEVER do this on a large table without an index:
SELECT * FROM notifications WHERE body LIKE '%payment%';
-- This is a FULL TABLE SCAN. On 10M rows, this takes seconds.

-- ✅ Instead, add a trigram index:
CREATE INDEX idx_notifications_body_trgm ON notifications USING gin (body gin_trgm_ops);
-- Now the same query uses the index: milliseconds.

4.5 Partial Indexes (Postgres)

Partial indexes are essential for high-volume tables. They index ONLY the rows that matter:

-- Instead of indexing ALL notifications:
CREATE INDEX idx_notif_user_read ON notifications (user_id, read);
-- This indexes millions of read=true rows you'll never query

-- ✅ Use a partial index — only index unread:
CREATE INDEX idx_notif_unread ON notifications (user_id, created_at DESC)
WHERE read_at IS NULL;
-- 95% smaller. 10x faster for the query that matters.

-- More examples:
-- Active sessions only
CREATE INDEX idx_active_sessions ON sessions (user_id, created_at)
WHERE expired_at IS NULL;

-- Pending items only
CREATE INDEX idx_pending_orders ON orders (user_id, created_at)
WHERE status = 'pending';

-- Non-deleted records
CREATE INDEX idx_active_users ON users (email)
WHERE deleted_at IS NULL;

4.6 Table Partitioning Strategy

For tables exceeding 10M rows, partitioning is not optional:

-- Time-based partitioning (most common for logs/activity)
CREATE TABLE activity_log (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id BIGINT NOT NULL,
    action VARCHAR(100) NOT NULL,
    details JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE activity_log_2024_01 PARTITION OF activity_log
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE activity_log_2024_02 PARTITION OF activity_log
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automate with pg_partman extension

| Partitioning Strategy | When to Use | Example Tables | |----------------------|-------------|----------------| | Range (time) | Time-series data, logs | activitylog, audittrail, metrics | | List (category) | Categorical data, tenants | notifications (by type), orders (by region) | | Hash (distribution) | Even distribution needed | sessions, cache entries |

4.7 High-Volume Table Health Score

Score each high-volume table:

| Criteria | Points | Check | |----------|--------|-------| | Primary query has index | +3 | EXPLAIN shows Index Scan | | Unread/status query has partial index | +2 | Partial index on status condition | | TEXT columns queried → has search index | +2 | GIN/tsvector index exists | | Retention policy defined | +2 | Auto-cleanup or archival documented | | Row count manageable ( lastid | | SELECT * FROM notifications WHERE userid = ? (no LIMIT) | Returns ALL rows for user | Always LIMIT + pagination | | WHERE body LIKE '%search%' on unindexed TEXT | Full table scan | Add trigram or full-text index | | WHERE DATE(createdat) = '2024-01-01' | Function prevents index use | WHERE createdat >= '2024-01-01' AND created_at 90 days | M |

Schema Recommendations

[Specific DDL statements for each fix]

Verdict

[PASS / CONDITIONAL PASS / FAIL]

## Red Flags — Escalate Immediately

- No foreign key constraints at database level
- Money stored as FLOAT
- No indexes on FK columns
- Tables with > 50 columns (normalization issue)
- No migrations (schema managed manually)
- Migrations without rollbacks
- Orphaned records in production
- Schema/model mismatch
- **High-volume table (logs, activity, notifications) with no indexes on user_id + created_at**
- **TEXT columns queried with LIKE without search index**
- **Tables with > 1M rows and no partitioning or retention strategy**
- **Counting queries (unread badges) doing full table scans**
- **Pagination using OFFSET on tables with > 100K rows**
- **No partial indexes on status/boolean columns in high-volume tables**

## Integration

- **Part of:** Full audit with `architecture-audit`
- **Complements:** `performance-audit` for query optimization
- **Follow-up:** `refactoring-safely` for schema changes
- **Pair with:** `security-audit` for data exposure concerns
- **Combine with:** `ui-ux-redesign` when slow queries cause poor UX
- **Triggers:** `observability-audit` if no query monitoring exists

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [boparaiamrit](https://github.com/boparaiamrit)
- **Source:** [boparaiamrit/skills-by-amrit](https://github.com/boparaiamrit/skills-by-amrit)
- **License:** MIT

Install and usage instructions live in the source repository linked above.