Database health investigation

Yes: there are avoidable problems around the database. The most urgent one is not PostgreSQL corruption or lock contention. The ingestion system stopped updating every major feed on 27 August, while the API health endpoint continued to report healthy. The database also contains duplicate analytical rows that change API results, query/index mismatches that make paged requests scan far more data than they return, and weak operational safeguards around recovery, privilege, observability, and request cost.

PostgreSQL itself was stable at the time of capture. The audit found no blocked sessions, deadlocks, invalid indexes, unvalidated constraints, sequence exhaustion, or meaningful dead-tuple buildup. Those healthy signals do not offset stale product data or incorrect joins.

Verdict

Incident: the database stopped getting current data

The failure is broad, simultaneous, and reproducible. At 05

The halt left 176 unprocessed scheduled_lineup_tasks; all were overdue, with scheduled times between 27 and 29 August. The database still had 2,463 future notstarted events through 2 December, so the empty future-facing pages were not explained by an empty fixture calendar.

The recovery target referenced by the database, https://statshubcronjob.onrender.com, currently returns HTTP 503 with x-render-routing: suspend-by-user. Its suspension and the database's common stop time make it the strongest proximate cause found in this investigation. That is a correlation, not proof that this one service owns every ingestion path. The protected ingestion API responds with HTTP 403 to anonymous health probes, so its internal state could not be verified.

The ingestion log also shows earlier reliability failures that should be fixed after service is restored:

• 2,246 events_venue_id_fkey violations from update-event, last seen on 23 August;
• 119 write attempts rejected as read-only transactions: 108 deletes and 11 updates;
• migrations or workers looking for relations such as system_stats, commentary_maps, or events on a search path where they did not exist.

The 8,999 non-success statuses observed in the seven-day audit window are not all errors; they include work left in queued and processing. Only 26 rows had a non-null error field in that window. The grouped error counts above come from the logged error text across the wider retained history.

Why monitoring did not catch it

GET /api/health reports process uptime, environment, and a literal "healthy"; it does not ping PostgreSQL or evaluate feed freshness (apps/api/internal/api/health.go). The database is pinged once when the process opens its pool, but not by the load-balancer probe. A running API can therefore remain green for days after ingestion stops.

Use three separate signals:

1. Liveness: the process can answer HTTP without checking dependencies.
2. Readiness: a short, bounded SELECT 1 succeeds and the process can serve requests.
3. Data freshness: each product-critical feed is within an explicit age threshold and overdue queues are below an explicit threshold.

Freshness should page an operator independently of readiness. Returning 503 for every stale analytical feed would unnecessarily remove otherwise useful directory and historical routes.

Confirmed correctness defect: duplicate team-season statistics

team_statistics has neither a primary key nor a unique constraint on its logical identity, (team_id, season_id). The audit found:

• 347 duplicated team-season keys;
• 713 rows in those groups, or 366 excess rows;
• every duplicate payload was identical within its group, so this is repeated ingestion rather than conflicting values;
• as many as four copies of one logical row.

This changes responses today. Joining standings to team_statistics expands 42 rows to 56 for season 79317, 20 to 49 for La Liga season 32501, and 10 to 15 for HNL season 76980. The defensive pack and team comparison handlers perform this join directly. The shots model computes league averages over all physical rows, so duplicated teams receive extra weight even when a UI later deduplicates its display.

The repair needs to be transactional and ordered:

1. identify the canonical row for every (team_id, season_id) group and verify again that duplicate payloads do not conflict;
2. remove only the redundant rows, with a backup or reversible copy of the affected IDs;
3. add a unique constraint on (team_id, season_id);
4. change ingestion to an idempotent upsert against that constraint;
5. add a regression test proving a retry cannot create a second logical row;
6. re-run representative standings, defensive-pack, comparison, and shots-model responses before and after the cleanup.

This should precede query tuning. Faster incorrect aggregates are still incorrect.

Pagination limits output, but not always database work

The canonical V2 resource layer has the right contract: signed keyset cursors, a default page size of 25, a maximum of 100, and limit + 1 retrieval instead of an exact total count. V1 remains unchanged. That contract is not the cause of the empty experimental pages.

One V2 player directory plan still shows why pagination must change the query, not only the response envelope. The query orders by COALESCE(player.name, ''), player.id, joins team and country, and searches with COALESCE(name, '') ILIKE '%…%' OR COALESCE(slug, '') ILIKE '%…%'. Existing player indexes do not match those expressions.

On 202,097 players, EXPLAIN (ANALYZE, BUFFERS) measured:

Keyset pagination removes deep-page OFFSET cost, but it cannot make the first page cheap when ordering and search expressions have no matching access path. The fix should be plan-driven: either make the order expression match a purpose-built (normalized_name, id) index, or change the null ordering so a simpler (name, id) index preserves the API contract. Search needs a separate normalized trigram expression; the legacy search already demonstrates an indexed normalization path. Verify both no-search and search plans before shipping the index.

The database-wide counters reinforce the need to measure under real traffic: events has read about 18.4 billion tuples through sequential scans, incidents 2.4 billion, screener_table 2.3 billion, and players 1.6 billion. These counters identify investigation targets, not individual query fingerprints, because pg_stat_statements is absent.

One admin route also defeats response safeguards. /api/dashboard/systemstats defaults to 50 rows but does not cap limit, performs count(*) for every request, and selects the complete system_stats row including stored request and response payloads. It is production-superadmin-only, which limits exposure, but a mistaken large limit can still produce a large response from a 1.7 million-row, 1.28 GB table. Cap the page size and return a projection; fetch payload bodies only from a detail route.

Storage, indexes, and capacity

The mirror contains 108 public tables, an estimated 88.2 million rows, no partitioned tables, and 34 GB of public relations inside a 35 GB database. The host filesystem was 83% full: 341 GB used with 75 GB free. This is not an immediate database-full condition, but it leaves less room for index builds, temporary files, restores, and archive growth than the raw database size suggests.

Archives have no enforced stopping point

betting_markets_archive is 6.95 GB and value_bets_archive is 5.15 GB. The two archives account for about 35% of the database. Neither is partitioned, and no retention boundary is enforced in the database. Their current spans are roughly one year, but that is observed history rather than a policy.

Define what these rows are for before changing them. If they support a fixed audit or modelling window, partition by time and expire partitions outside the window. If they are permanent evidence, move old immutable partitions to a cheaper tier and include their restore procedure in the recovery plan.

commentary_maps is another concentrated storage cost: 50,720 rows occupy 1.23 GB, almost all TOAST data from raw_data, with an average logical row of about 24 KB. Current response queries generally avoid selecting that raw body, so this is a retention and backup-cost candidate rather than proof of response bloat.

Index cleanup requires a longer observation window

The catalog reports 250 non-constraint indexes with zero scans; 44 are at least 10 MB and together occupy about 3.12 GB. Twelve pairs have the same indexed columns and predicate, including duplicates on commentary fouls, commentary events, betting odds, incidents, commentary maps, and basketball statistics. There are also indexes that duplicate primary or unique lookup paths.

Do not drop 3 GB of indexes from this snapshot. The mirror was restored on 27 August, stats_reset is null, and the ingestion outage removed normal write and read traffic. idx_scan = 0 over this window means “candidate for review,” not “unused in production.” Exact duplicate definitions can be reviewed first; all other removals need a representative production observation window and DROP INDEX CONCURRENTLY with a rollback list.

The reverse problem also exists: 41 foreign keys do not have a leading index on the referencing columns. The largest include references from shot_events, player_statistics_event, odds_movements, value_bets, and commentary tables. Add only indexes justified by parent update/delete plans or measured joins; indexing every foreign key blindly would recreate the write cost above.

Recovery and security posture

The mirror exposes no database-visible recovery chain:

• archive_mode is off and no WAL files have been archived;
• there are no replication slots or replicas;
• no backup history is visible from PostgreSQL;
• the host filesystem is already 83% full.

This does not prove the hosted project lacks Supabase backups or PITR. It means recovery cannot be demonstrated from the system audited here. The production follow-up must record backup frequency, retention, PITR window, off-host copies, last successful restore test, target RPO, and target RTO. Backups are not a control until a restore is timed and verified.

The configured statshub role is a superuser with BYPASSRLS, CREATEROLE, and CREATEDB, and it owns all 108 public tables. The Go connector does provide a useful guard: remote connections are wrapped read-only unless writes are explicitly enabled. That reduces accidental remote mutations, but it does not limit reads, expensive statements, or the impact once a production process is deliberately write-enabled. Use separate least-privilege roles for serving reads, ingestion writes, migrations, and administration.

RLS cannot be graded from this mirror. It contains no anon, authenticated, service_role, or authenticator roles and the superuser bypasses every policy. Production Data API grants and RLS policies need a control-plane audit. The local Supabase config exposes public through the Data API with max_rows = 1000, but local configuration is not evidence of production exposure. Supabase's current guidance is to enable RLS on exposed-schema tables and pair it with correct grants; test that using the actual production roles, not an owner connection. See row-level security.

Two public SECURITY DEFINER functions in the mirror have no pinned search_path and retain the default PUBLIC execute privilege. They call net.http_post, but the mirror has neither pg_net nor a net schema, so the functions would fail locally. The repository migration also creates http while calling net.http_post, which is a different extension interface. In production, revoke PUBLIC, grant only the scheduler/admin role, pin an empty or trusted search_path, schema-qualify every object, and verify the required extension before installing the functions. Do not use a database function call as the health test because it produces an external side effect.

Safeguards and observability

The database had a 98.1% block cache hit rate, zero deadlocks, and no blocked or idle-in-transaction sessions during capture. It had also written 57 GB of temporary data across 1,117 temp files and recorded 14,159 rolled-back transactions. Without query fingerprints and a stats reset timestamp, those totals cannot be assigned to a route or time window.

The missing controls are concrete:

• pg_stat_statements is not installed;
• track_io_timing is off;
• the server-wide statement_timeout, idle_in_transaction_session_timeout, and idle_session_timeout are zero;
• no slow-statement duration is logged by PostgreSQL;
• the API has a ten-connection pool but no general request rate limiter or 429 path;
• health is process-only and freshness has no alerting contract.

HTTP request cancellation and 60-second server timeouts are useful, but a database role should also carry a bounded statement_timeout so abandoned or misbehaving clients do not depend on application cancellation working perfectly. Set different ceilings for interactive API reads, ingestion, and administrative jobs. Add per-client or per-route rate limits at the edge, with tighter budgets for analytical and search endpoints. Pool size is a capacity limit, not a fairness or abuse control.

Enable pg_stat_statements, track_io_timing, and slow-query logging with a deliberate retention and redaction policy. PostgreSQL's monitoring statistics are cumulative and must be interpreted with their reset time; see the PostgreSQL monitoring statistics documentation.

Migration history is not reproducible from this mirror

The repository contains six duplicated numeric migration versions: 0003, 0004, 0012, 0015, 20251016, and 20260819. With the exception of the two 0003 files, which differ only by trailing whitespace, the same-version files contain different schema changes. The mirror has no supabase_migrations schema or schema_migrations table, so it cannot show which file history produced the current schema.

Do not rename already-applied migrations blindly. First export the hosted migration ledger, compare every version and checksum with the repository, and use the Supabase migration repair workflow where history and schema disagree. Then establish one unique timestamp version per migration and make CI reject duplicate prefixes. The mirror should be rebuilt from migrations plus a documented data restore, not treated as the migration ledger.

What is healthy

The audit deliberately looked for counter-evidence. The following checks passed:

• connectivity and a repeatable read-only health transaction;
• no blocking, idle-in-transaction sessions, or deadlocks at capture;
• no invalid indexes and no unvalidated constraints;
• no material dead-tuple buildup on the largest tables after the restore;
• sequence usage below 4% of the available signed integer range;
• all 82 tables referenced by API query source exist in the mirror;
• all 411 statically checked scan-structure columns agree with live PostgreSQL types;
• the canonical V2 cursor contract is bounded and avoids deep OFFSET pagination.

These results narrow the incident. Reverting V2 pagination would not restart ingestion, remove duplicate statistics, or improve recovery posture.

Remediation plan

Reproducing the high-signal checks

Every database query in this investigation ran with default_transaction_read_only=on, a statement timeout, and a lock timeout. The following shapes are sufficient for the incident and integrity checks; timestamps should be evaluated against explicit product thresholds rather than merely compared with now():

-- Feed freshness and overdue work
SELECT max(updated_at) FROM screener_table;
SELECT max(predicted_at) FROM predicted_lineups;
SELECT max(updated_at) FROM value_bets;
SELECT count(*) FROM scheduled_lineup_tasks
WHERE processed = false AND scheduled_time < now();

-- Duplicate logical rows
SELECT team_id, season_id, count(*)
FROM team_statistics
GROUP BY team_id, season_id
HAVING count(*) > 1;

-- Immediate database health
SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock';
SELECT count(*) FROM pg_stat_activity
WHERE state = 'idle in transaction';
SELECT count(*) FROM pg_index WHERE indisvalid = false;
SELECT count(*) FROM pg_constraint WHERE convalidated = false;

For related measured API work, see Go API query optimization, measured. This report supersedes none of its benchmark data; it adds the freshness, integrity, security, recovery, and migration evidence captured on 2 September.