Database schema audit and migration plan. Public summary.

The full M1 deliverable is a 15-section audit of the client's production database: every table, index, materialized view, cron job, and RPC named and measured, with verbatim excerpts from the platform's code and schema annotations. Publishing that would publish the client's private architecture, so this page carries the audit's shape, method, and aggregate findings instead, with every identifying detail removed. The metrics are carried unchanged from the private report.

• A managed Postgres 17.6 instance (Supabase) behind a multi-game TCG portfolio and market-intelligence platform: 10 active games, 55 tables across three application schemas plus the managed auth schema, 5 materialized views, a ~2,100-line schema-as-code file, a ~2,700-line RPC file, and 29 active + 182 archived migrations.
• Access was a dedicated read-only role; six recon rounds were run against production over two days, each saved as a numbered evidence file the report cites line-by-line.
• Scope was the database side only. The matcher's TypeScript internals were covered by the platform team's own 89 KB self-audit; this audit deliberately started where that document ends (schema, indexes, MV strategy, queue mechanics, arbitrage data layer, cron health, drift) so the two documents compose instead of overlapping.

The index-to-heap ratio on the price-history table (2.44x) was audited index-by-index against live scan counts and found justified: every index on that table earns its keep on a real access path. The growth math was stated in advance: at the measured monthly insert rate the table crosses 50M rows within a year, at which point the DELETE-based compaction pattern becomes the bottleneck and a declarative monthly partition (drop old partitions instead of DELETE) is the named successor, with the trigger thresholds written down before they fire.

• Two schema-vs-prod drift cases. One materialized view existed live but appeared in neither the schema-as-code file nor any migration; one index was declared in schema-as-code but did not exist in production under that name (a phantom that would make a future drop silently no-op and a future create silently duplicate). Both slipped past the team's otherwise good pre-commit drift hook, and both were surfaced as inputs to the next drift sweep, not accusations: the team's drift discipline was explicitly graded as genuinely good.
• Two silently dead cron jobs. Both stopped in the same week, both at 100% failure, neither alerting. The report's concern was not the missed work but the silence: the cron-log infrastructure to alert on fail-rate existed, and nothing used it.
• One freshness gap feeding a money surface. The scraper that feeds PSA population data into the arbitrage layer had stopped 29 days earlier after a clean success streak, while the arbitrage compute kept running daily on month-old population data. The failure mode was spelled out: population counts lag a month while spreads move daily, so a projected ROI can be inflated exactly when it looks most attractive. (The platform team re-armed the scraper within days; by M2 the staleness window was closed and verified fresh.)
• The near-miss queue profiled for M3. The 0.50-0.70 review band held 5,732 rows, 99.8% of them single-card listings, with 85% concentrated in the 0.60-0.64 sub-band: a working middle, not safe enough for blind auto-promote, not weak enough to discard. The M3 drain design (auto class, offline re-match class, manual class, abstain class) came straight from this distribution, and a partial index to make the band traversal fast was proposed as the first migration.
• A self-correction, kept in the record. The v1 draft flagged a 593 MB primary key as a drop candidate because compaction was the only inferred consumer. Live scan counts showed it was the table's second-most-used index (9.85M scans). The recommendation was retracted in the shipped report, and the lesson (never propose an index drop from inference; only from observed scan counts over a verified window) became the audit's stats-observation protocol.

Ten actions, ordered by impact-to-risk ratio, tiered additive → invariant → operational, with Phase-2 candidates explicitly fenced out of contract scope:

Every action shipped with an explicit rollback and an explicit test. A third de-facto-enum column was deliberately excluded from the constraint batch: pre-validation found 18 distinct live values where the naive constraint assumed 2, and adding it would have failed on 80K+ rows. The check that kept it out of the plan is the same check that makes the other two safe.

The plan also wrote down what it would NOT do: no schema redesign of the arbitrage table, no matcher-code changes, no Phase-2 architecture inside the milestone. Open questions were routed to named owners with a stated default if unanswered, so the next milestone could start without waiting. Outcome, verified in the M2 deliverable: seven of the nine near-term actions were deployed and independently validated against production within the next milestone; the remaining two were deliberately gated (a 14-day observation window; a Phase-2 partition trigger).

The platform refreshes five MVs, one of them after every marketplace sync. The audit's verdict: CONCURRENTLY refresh is the right pattern at current scale (unique indexes present, dashboard reads never blocked, refresh fits comfortably), and the escalation triggers were written down in advance rather than left to be discovered: escalate when a single refresh crosses 5 minutes of wall-clock, when the largest MV approaches 10 GB, or when the active-game count triples. The named next pattern is partition-aligned refresh (refresh only the current month), and the managed image already ships the extensions that work would need (available but not installed). One MV is locked as materialized by an incident note in the schema itself: it had once been a regular view, and a DISTINCT ON over 222K rows re-running on every API call caused a 12-hour outage. The audit's recommendation was to keep that lock exactly as it is.

The platform's strict matcher opens with a warning comment (quoted verbatim in the private report; the client's name is replaced here):

"Wrong matches = wrong arbitrage = [the client] buys the wrong card. Return null rather than guess."

Every recommendation in the audit is gated on that constraint: prefer reversible and additive change over destructive rewrite, prefer abstain over confidently-wrong, and never drop or rewrite anything on inference when an observation window can be had first.