Skip to content
Canonical

ast-dataflow-schema-audit

Audit assessment

No — the current tool cannot audit the entire Supabase database by itself. It can provide useful application-code column lineage, which could become one input to a broader schema audit.

What it currently covers

• TypeScript .from(‘table’) chains: ◦ Reads through .select(), filters, ordering, .match(), and RPC payloads. ◦ Writes through .insert(), .update(), .upsert(), and .match(). ◦ Reports exact, wildcard, or indirect confidence. • Python under scripts/: ◦ Raw SQL passed to asyncpg-style calls. ◦ supabase-py fluent chains. ◦ SQL parsing via sqlglot when installed, otherwise regex fallback. • Symbol, type, import, re-export, flow, and dead-export analysis for the TypeScript corpus.

Representative results show the limitation: the TypeScript query found 103 estimated references to source_documents.id, while the Python query found one indirect SQL write and reported sqlglot: false in the current environment.

What it cannot currently determine

Foreign keys

It does not:

• Connect to Supabase or query pg_catalog, information_schema, or pg_constraint. • Enumerate actual tables, columns, primary keys, or foreign keys. • Compare declared FKs with generated types or migration history. • Infer with confidence that foo_id should reference foo.id. • Analyse all joins, RPC implementations, views, triggers, or external consumers.

supabase/types/database.types.ts does contain generated Relationships metadata, but the AST tool does not parse that metadata as a schema catalogue. It only benefits from the types indirectly when classifying Supabase clients.

Naming and usage heuristics would also produce false positives. For example, supabase/platform/001_canonical_source_tables.sql explicitly documents reference_items.source_document_id as an intentionally omitted cross-class FK. Static code usage alone cannot distinguish that deliberate design from a missing constraint.

Dead columns

It could identify candidate unused columns, but not prove that a column is safe to remove.

A complete candidate scan would require:

  1. An external inventory of every table and column.
  2. One lineage query per column; there is no current all-schema/all-column command.
  3. Coverage of TypeScript, Python, SQL migrations, database functions, views, triggers, jobs, and other clients.
  4. Manual treatment of wildcard reads, spreads, dynamic payloads, JSONB keys, and indirect queries.

A zero-result query currently means “no matching static reference was found in the scanned corpus”, not “the database column is dead”. Wildcard selects, dynamic writes, RPC implementations, database-side code, and external consumers can all invalidate that conclusion.

There is also a known gap in the current TypeScript implementation: rpc-payload exists in the write result type but RPC write detection is deferred.

Recommended direction

Build a separate schema-aware schema-audit layer rather than extending the current queries into an unsafe deletion tool:

  1. Snapshot the live schema, preferably from PostgreSQL catalog queries, including tables, views, columns, PKs, FKs, indexes, functions, triggers, and policies.
  2. Parse generated Supabase types and migrations as additional provenance, while treating the live catalog as authoritative.
  3. Expand lineage collection to TypeScript, Python, raw SQL files, SQL functions, views, triggers, and RPC definitions.
  4. Produce an evidence matrix for each column: ◦ exact application reference ◦ wildcard reference ◦ indirect/dynamic reference ◦ database-side reference ◦ external/unknown usage
  5. Report likely missing FKs separately from intentional or uncertain relationships.
  6. Report dead-column candidates only, with no automatic removal.

The committed tool is therefore suitable for application reference discovery and migration impact analysis, but not yet for a trustworthy whole-Supabase schema audit.