# Schema Design Audit — E-commerce Order System (PostgreSQL) **Scope:** Users, Orders, OrderItems, Products **Engine:** PostgreSQL **Volume:** 500k orders/month; frequent date-range + user_id filtering on Orders; full-text search on Products **Application type:** OLTP > **Input limitation:** The schema was described at the table-name level, with one structural detail (Orders.shipping_address as JSON). No column list, data types, existing indexes, or FK definitions were provided. Every finding below is tagged STATED or INFERRED, and INFERRED findings are built from standard OLTP e-commerce schema patterns, not from your actual DDL. Treat this as a hypothesis set to verify against the real schema, not a confirmed audit. --- ## STEP 0 — Schema Context & Risk Profile | Dimension | Classification | Rationale | |---|---|---| | Schema maturity | Existing (reviewing) | The JSON shipping_address detail implies a live design choice, not a blank slate — INFERRED | | Write/read ratio | Mixed — write-heavy at checkout (Orders/OrderItems insert), read-heavy on order history lookup | INFERRED from stated query pattern | | Consistency requirement | Strong (financial/transactional) | STATED — OLTP + order/payment domain | | Scale trajectory | 500k orders/mo now; 10x/100x horizon not stated | INFERRED trajectory used below | | Most dangerous failure class (top 2) | 1) Data corruption (stale derived order totals) 2) Query degradation (unbounded date-range scans as Orders grows) | INFERRED | **Failure mode priority map:** | Failure Mode | Priority | Evidence Type | Conf | |---|---|---|---| | Missing indexes on high-frequency query predicates (user_id + date range) | HIGH | STATED | 85 | | N+1 query trap (OrderItems → Products lookups per line item) | MED | INFERRED | 60 | | Nullable foreign keys allowing orphaned records | MED | INFERRED | 50 | | Schema migration lock risk at 10x–100x scale | MED-HIGH | INFERRED | 65 | | Storing computed/derived values that go stale (order totals, item price) | HIGH | INFERRED | 70 | | Over-normalized design creating expensive joins at scale | LOW | INFERRED | 40 | --- ## STEP 1 — Normalization & Data Integrity Audit | Table / Entity | Issue | Normal Form Violation | Integrity Risk | Severity | Evidence Type | Conf | |---|---|---|---|---|---|---| | Users | Column structure unknown — cannot verify 1NF/3NF compliance (e.g., repeating address/phone fields) | Unknown | Med | 🟡 | INFERRED | 30 | | Orders | `shipping_address` as JSON (not JSONB) loses indexing, binary storage efficiency, and operator support that JSONB provides | None (design choice, not NF violation) | Med | 🟡 | INFERRED | 55 | | Orders | If order total is a stored column derived from OrderItems, it can drift from the sum of line items | None (derived-value staleness) | High | 🔴 | INFERRED | 65 | | OrderItems | If unit price is not snapshotted at purchase time and instead joins live to Products.price, historical orders will misreport what the customer actually paid | None (derived-value staleness) | High | 🔴 | INFERRED | 70 | | Products | If category/tags are stored as free-text repeated per row rather than a normalized lookup, this is a 3NF risk — but unconfirmed | 3NF (possible) | Low | 🟢 | INFERRED | 25 | **Referential integrity map:** | Relationship | FK Defined? | ON DELETE behavior | ON UPDATE behavior | Risk | Evidence Type | |---|---|---|---|---|---| | Orders.user_id → Users.id | UNCLEAR | UNCLEAR | UNCLEAR | High if CASCADE — would silently delete financial/order history when a user is removed | INFERRED | | OrderItems.order_id → Orders.id | UNCLEAR | UNCLEAR (CASCADE is usually correct here) | UNCLEAR | Low if CASCADE — line items have no meaning without the parent order | INFERRED | | OrderItems.product_id → Products.id | UNCLEAR | UNCLEAR | UNCLEAR | High if CASCADE — deleting a product would erase historical line-item records needed for order history/accounting | INFERRED | --- ## STEP 2 — Indexing Strategy Audit | Query Pattern | Current Index | Missing Index | Index Type Recommended | Performance Risk | Evidence Type | |---|---|---|---|---|---| | Orders filtered by user_id + date range | Not stated / assume none | Composite `(user_id, created_at)` | B-tree | High — this is the stated primary hot path and will full-scan without it | STATED | | Orders filtered by date range alone (e.g. admin/reporting) | Not stated | `(created_at)` or covered by composite above depending on selectivity | B-tree | Med | INFERRED | | Products full-text search | Not stated | Generated `tsvector` column + GIN index | GIN | High — sequential ILIKE-style search will not scale | STATED | | OrderItems join to Orders (order_id) | Not stated | Index on `order_id` | B-tree | High — Postgres does not auto-index FK columns | INFERRED | | OrderItems join to Products (product_id) | Not stated | Index on `product_id` | B-tree | Med-High — needed for per-product sales reporting and avoiding N+1-style plan costs | INFERRED | | Orders filtered by status (e.g. pending/processing dashboards) | Not stated | Partial index `WHERE status IN (...)` | Partial B-tree | Med | INFERRED | **Index anti-patterns to flag:** | Anti-Pattern | Present? | Table | Impact | Evidence Type | |---|---|---|---|---| | Index on low-cardinality column (e.g. status, boolean flags) | UNCLEAR | Orders | Wasted write cost if a full index rather than partial | INFERRED | | Composite index with wrong column order | UNCLEAR — depends on whether user_id or created_at is more selective in practice | Orders | Could force wider scans than necessary | INFERRED | | Over-indexing on a write-heavy table | UNCLEAR | Orders / OrderItems | Checkout-time insert latency if excessive | INFERRED | | Missing covering index for frequent SELECT | Likely YES, since no indexes were confirmed to exist | Orders | Every date-range + user_id lookup pays a scan cost | STATED (no index confirmed) | --- ## STEP 3 — Assumption Ledger & Scale Projection **Assumption ledger:** | Assumption | Where It Enters | If False — Effect on Schema | |---|---|---| | Orders/OrderItems/Products/Users use surrogate PKs (bigint or UUID) | All PK/FK columns, scale projection | Storage and join costs differ significantly between bigint and UUID at 100M+ rows | | OrderItems snapshots unit price/quantity at purchase time | Step 0 & Step 1 stale-value risk | If false, historical order totals are unreliable and change if Products.price changes later — a real financial-integrity bug | | `shipping_address` uses `jsonb`, not `json` | Step 2 indexing feasibility | If it's actually `json` type, no GIN indexing is possible and every read re-parses text | | A single `created_at` (or `order_date`) column backs the "date range" query | Step 2 indexing choice | If the date filter is actually on a different column (e.g. delivery date, updated_at), the recommended index targets the wrong column | | No table partitioning exists yet on Orders/OrderItems | Step 3 scale projection | If partitioning already exists, breaking-point estimates below shift significantly later | **Scale projection** (based on 500k orders/month = ~6M orders/year): | Metric | Current (~1yr) | 10x | 100x | First Breaking Point | Evidence Type | |---|---|---|---|---|---| | Row count (Orders) | ~6M | ~60M | ~600M | Sequential/index scan performance and vacuum overhead degrade meaningfully past ~20–50M rows without partitioning | INFERRED | | Row count (OrderItems, ~3 items/order assumed) | ~18M | ~180M | ~1.8B | Same threshold as Orders, reached sooner due to multiplier | INFERRED | | Index size | Fits comfortably in RAM | Approaches or exceeds cache-friendly size | Disk-I/O bound for cold index pages | Between 10x and 100x | INFERRED | | Migration lock duration (unindexed ALTER TABLE) | Seconds–low minutes | Several minutes | 30+ minutes — unacceptable for OLTP without `CONCURRENTLY`/online migration tooling | Between current and 10x | INFERRED | | Query latency (user_id + date range scan) | Sub-100ms if indexed | Similar, if indexed | Degrades without partitioning + archival strategy for old orders | 100x, absent partitioning | INFERRED | --- ## STEP 4 — Security & Compliance Audit | Dimension | Finding | Risk | Recommendation | Evidence Type | |---|---|---|---|---| | PII / sensitive data storage | Users table almost certainly holds email/name; `shipping_address` JSON holds name, address, phone — both are PII surfaces | High | Inventory exact PII fields; consider field-level encryption or tokenization for shipping_address contents | INFERRED | | Encryption at rest | Not stated whether column-level or disk-level encryption is in place | Unknown | Confirm at minimum disk/volume-level encryption; consider pgcrypto for high-sensitivity fields | INFERRED | | Soft delete implementation | Not stated | Unknown | For Orders/Users, prefer `deleted_at` soft-delete over hard delete to preserve financial audit trail and avoid FK cascade data loss | INFERRED | | Audit trail | Not stated whether writes are logged with actor + timestamp | Unknown | Add `created_at`/`updated_at` on all tables if missing; consider a separate audit log table for order status changes | INFERRED | | Least-privilege access | Not stated whether row-level security (RLS) is used | Unknown | If any multi-tenant or per-user data isolation is needed beyond app-layer checks, Postgres RLS is available and worth evaluating | INFERRED | --- ## STEP 5 — Prioritized Schema Improvements | Priority | Issue | Specific Fix | Migration Risk | Evidence Type | |---|---|---|---|---| | 🔴 P1 | No confirmed index supporting the stated hot query | `CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders(user_id, created_at);` | Low | STATED | | 🔴 P1 | OrderItems likely missing purchase-time price snapshot | Add `unit_price_at_purchase numeric` column; backfill from historical data if recoverable | Med — backfill correctness matters | INFERRED | | 🔴 P1 | FK constraints and ON DELETE behavior unconfirmed | Add explicit FKs: `user_id` → RESTRICT (or soft delete), `product_id` → RESTRICT (preserve order history) | Med — requires orphan cleanup first if any exist | INFERRED | | 🟡 P2 | `shipping_address` may be `json` not `jsonb` | `ALTER COLUMN shipping_address TYPE jsonb USING shipping_address::jsonb;` | Low–Med depending on table size | INFERRED | | 🟡 P2 | No full-text search infrastructure on Products | Add generated `tsvector` column + `GIN` index for name/description | Low | STATED | | 🟡 P2 | No FK-backing indexes on OrderItems join columns | Index `order_id` and `product_id` on OrderItems | Low | INFERRED | | 🟡 P2 | No audit columns confirmed | Add `created_at`/`updated_at` (and `updated_by` if actor tracking matters) across all tables | Low | INFERRED | | 🟢 P3 | No soft-delete pattern confirmed | Add `deleted_at timestamptz` to Users/Orders instead of hard deletes | Low | INFERRED | | 🟢 P3 | Partitioning not yet needed but should be planned | Design range partitioning on Orders/OrderItems by `created_at`, activate before ~20–50M rows | High — plan early, execute before it's urgent | INFERRED | | 🟢 P3 | PII fields unencrypted at column level | Evaluate pgcrypto or app-layer encryption for shipping_address and Users contact fields | Med — requires app changes | INFERRED | --- ## STEP 6 — Schema Integrity Check | Check | Status | Note | |---|---|---| | All tables have a primary key with appropriate type for scale | ⚠ | PK types not stated — assumed, not verified | | All FK relationships have explicit referential integrity rules | ⚠ | No FK/ON DELETE behavior confirmed anywhere in the input | | Index audit covers all stated query patterns | ✅ | Date-range + user_id filtering and Products full-text search both addressed | | Scale projection identifies the first breaking point explicitly | ✅ | Partitioning need flagged at ~20–50M rows on Orders/OrderItems | | PII storage is flagged and a recommendation given | ✅ | Users and shipping_address JSON both flagged | | All INFERRED findings are labeled | ✅ | | **AUDIT CONFIDENCE: 45/100** — Confidence is capped because the input describes table names and one column detail rather than actual DDL; most findings are pattern-matched from typical OLTP e-commerce schemas rather than verified against your real structure. **Biggest unknown:** Whether OrderItems already snapshots price/quantity at purchase time. This single fact determines whether the highest-severity finding in this audit (stale derived financial values) is real or a non-issue, and it can't be inferred from what was provided. **Findings from schema alone (STATED):** - Four tables: Users, Orders, OrderItems, Products - Orders has a JSON column for shipping_address - Engine is PostgreSQL - Volume: 500k orders/month - Hot query pattern: date range + user_id filtering on Orders - Full-text search required on Products - Application type is OLTP **Findings requiring production schema/query data (VERIFY):** - Actual column lists and data types for all four tables - Whether `shipping_address` is `json` or `jsonb` - Existing indexes, if any - FK constraints and ON DELETE/ON UPDATE behavior - Whether OrderItems stores a price/quantity snapshot at purchase time - Which timestamp column backs the "date range" filter (order date vs. delivery date vs. updated_at) - Presence of audit columns, soft-delete flags, encryption, and RLS policies - Real column cardinalities (e.g., order status values) to validate partial-index recommendations