db-check

name	db-check
description	Query actual database schema before writing any query, migration, or API endpoint. Prevents wrong column names — the

Database Schema Check

Run this BEFORE writing any query, migration, or API endpoint.

Step 1: Identify Target Tables

List every table your implementation will touch (read or write).

Step 2: Query Actual Schema

For EACH table, run via Supabase MCP:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = '<TABLE>'
ORDER BY ordinal_position;

Step 3: Check Constraints

For tables you'll be upserting into:

SELECT
  tc.constraint_name,
  tc.constraint_type,
  kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = '<TABLE>'
ORDER BY tc.constraint_type, kcu.column_name;

Step 4: Check for Existing Views

SELECT table_name FROM information_schema.views
WHERE table_schema = 'public'
AND table_name LIKE 'v_%<keyword>%';

If a view already does what you need, use it instead of writing a complex join.

Step 5: Print Summary

Before writing any implementation code, print:

Schema for <table>:
- column_name (data_type, nullable/required, default)
- ...
Primary key: <column>
Unique constraints: <columns>
Existing views: <any matching views>

Money tables — extra checks (xero_invoices / xero_transactions / bank_statement_lines)

Before any query that emits a dollar figure:

• Column traps: xero_invoices.type (NOT invoice_type); PK is xero_id; xero_transactions has no reference. BAS-excluded items live in line_items[]->>tax_type='BASEXCLUDED'.
• Always exclude voided rows: xero_transactions voids are status='DELETED'; xero_invoices carry BOTH 'DELETED' and 'VOIDED' (FY26: 96 VOIDED vs 23 DELETED). Filter them NULL-safely (status IS DISTINCT FROM 'DELETED' AND status IS DISTINCT FROM 'VOIDED' for invoices; DELETED-only for transactions) from every money read — or use an allow-list (status IN ('AUTHORISED','PAID')). Voided rows once inflated the R&D claim ~$32K.
• Never SUM money through supabase-js: the PostgREST 1000-row cap silently truncates .select() at 1000 rows. Aggregate in SQL (execute_sql / psql) — SQL SUM() is not capped.
• Two-account rule: ACT project totals include only NAB Visa ACT #8815 + NJ Marchesi T/as ACT Everyday (exclude NM Personal / NJ Marchesi T/as ACT Maximiser).
• Verify against the canonical P&L: reconcile any project/org total against project_monthly_financials.

→ Full money guards: .claude/skills/tag-transactions/SKILL.md + memory command-center-finance-truth.md.

Rules

• NEVER assume column names from memory, grep, or context — always verify here
• Check NOT NULL columns without defaults — these will cause silent insert failures
• Check unique constraints — wrong onConflict columns cause upsert bugs
• If a column doesn't exist, STOP — don't guess alternatives, query the schema