Skip to content

Open-source agentic AI Search Execution Platform. Track AI visibility, find opportunities, and let agents turn signals into prioritized actions and drafted fixes across ChatGPT, Claude, Gemini, Google AI Overviews, Google AI Mode, Perplexity, Grok, and Microsoft Copilot.

4.7 KB
Raw Blame
#!/usr/bin/env bash
#
# Prints a read-only SQL query that reports anything this repo expects to exist
# in the database but which may not.
#
# A migration file is only half of a migration; the other half is applying it.
# That step has been missed twice (#709, and 00050 in #700), and both times the
# gap surfaced as a customer-visible error rather than as something we caught.
# The CI label (.github/workflows/migration-guard.yml) is the reminder; this is
# the way to confirm afterwards that the reminder was acted on.
#
# It reads the repo and writes SQL. It never connects to anything, so it needs
# no credentials and cannot change any data:
#
#     bash supabase/check-drift.sh | pbcopy      # then paste into the SQL Editor
#     bash supabase/check-drift.sh > /tmp/drift.sql
#
# An empty result means no drift. Any row is something the code expects and the
# database does not have.
#
# Three checks, weakest to strongest:
#   1. every function the migrations declare exists
#   2. every table the migrations declare exists
#   3. every RPC the application actually calls resolves to a function
#
# The third is the one that matters most: it checks what the code calls rather
# than what the migrations declare, so it catches a function that was renamed,
# never applied, or applied with a different argument list.
set -euo pipefail

cd "$(dirname "$0")/.."

# Deduplicated, sorted identifiers. `|| true` throughout: a grep that matches
# nothing exits 1 under `set -e`, and "no functions declared" is a valid state.
# Quotes are stripped before the name is read. Migrations here are written
# both ways — `public.topics` and `"public"."topics"` — and a pattern that
# keeps the quotes reads the second form as the schema name, silently checking
# `public` 26 times instead of the 26 tables it stands for.
strip_quotes() { tr -d '"'; }

# Greedy `.*` so the split happens at the LAST keyword: it lands after
# `EXISTS` when the statement has it, and after `TABLE`/`FUNCTION` when not.
functions=$(grep -rhoiE 'CREATE (OR REPLACE )?FUNCTION[[:space:]]+[a-z0-9_."]+' supabase/migrations/ 2>/dev/null |
  strip_quotes | sed -E 's/.*FUNCTION[[:space:]]+//I; s/^public\.//' |
  tr '[:upper:]' '[:lower:]' | sort -u || true)

tables=$(grep -rhoiE 'CREATE TABLE[[:space:]]+(IF NOT EXISTS[[:space:]]+)?[a-z0-9_."]+' supabase/migrations/ 2>/dev/null |
  strip_quotes | sed -E 's/.*(TABLE|EXISTS)[[:space:]]+//I; s/^public\.//' |
  tr '[:upper:]' '[:lower:]' | sort -u || true)

# Matches supabase.rpc('name') and .rpc('name', {...}) in both apps.
rpcs=$(grep -rhoE "\.rpc\(\s*'[a-z0-9_]+'" web/src server/src 2>/dev/null |
  sed -E "s/.*'([a-z0-9_]+)'.*/\1/" | sort -u || true)

# One-per-line -> a SQL VALUES list. Emits NULL when empty so the query stays
# valid rather than becoming a syntax error on a repo with none of that kind.
as_values() {
  if [ -z "$1" ]; then
    echo "    (NULL)"
  else
    printf '%s\n' "$1" | sed -E "s/^/    ('/; s/\$/'),/" | sed '$ s/,$//'
  fi
}

cat <<SQL
-- ============================================================================
-- ansvisor — schema drift check    (read-only; generated by check-drift.sh)
--
-- Paste into the Supabase SQL Editor. No rows = no drift.
-- Each row is something the repo expects and this database does not have.
-- ============================================================================

WITH expected_functions(name) AS (VALUES
$(as_values "$functions")
),
expected_tables(name) AS (VALUES
$(as_values "$tables")
),
called_rpcs(name) AS (VALUES
$(as_values "$rpcs")
)
SELECT 'missing function' AS problem, e.name AS object,
       'declared in supabase/migrations/ but absent from pg_proc' AS detail
FROM expected_functions e
WHERE e.name IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = 'public' AND p.proname = e.name
  )

UNION ALL

SELECT 'missing table', e.name,
       'declared in supabase/migrations/ but absent from pg_tables'
FROM expected_tables e
WHERE e.name IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM pg_tables t WHERE t.schemaname = 'public' AND t.tablename = e.name
  )

UNION ALL

-- The strongest of the three: what the application calls at run time, which is
-- what a user hits when it is missing.
SELECT 'unresolved rpc', c.name,
       'called via .rpc() in web/src or server/src but no such function exists'
FROM called_rpcs c
WHERE c.name IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = 'public' AND p.proname = c.name
  )

ORDER BY 1, 2;
SQL

{
  echo ""
  echo "-- Checking $(printf '%s\n' "$functions" | grep -c . || true) function(s), \
$(printf '%s\n' "$tables" | grep -c . || true) table(s), \
$(printf '%s\n' "$rpcs" | grep -c . || true) called RPC(s)."
} >&2