Test that the checker still fails a table with RLS off, still refuses a cross-tenant read, and still says it could not check when it cannot seed a table, so an edit to the checker cannot quietly turn it green.
Add "Database access check tests" from ntent to this repo.
Fetch https://ntent.app/r/f/check-backend-test as plain text. Write it verbatim to scripts/check-backend.test.mjs.
It needs check-backend.mjs. If this repo does not have it, stop and tell me.
Then check it: node --test passes. Remove the grant pre-check so a permission error on a probe counts as denied, and the missing-grants test fails.Reads https://ntent.app/r/f/check-backend-test
Read the code
check-backend.test.mjsscripts/check-backend.test.mjs
4187 lines
/**
* Tests for the backend checker.
*
* Two halves, because they cost different amounts. The engine tests import
* the checker's functions and run PGlite in this process: they boot once, run
* the shim, dump that database, and load every fixture from the dump, which
* is about a fifth of the cost of a boot. The rest go through run(), which
* spawns the checker's child process exactly as the hook does, because the
* cache, the timeout and the exit codes live there. One test spawns the CLI
* itself, for the lines the hook reads.
*
* Most of what is here is about exit 2. A check that cannot load a migration,
* cannot find an extension or runs out of time must say so and never pass,
* and a check that reports clean on a database it never built prints exactly
* what a passing one prints.
*
* Every config is built from DEFAULT_CONFIG, never from CONFIG, so this file
* still passes after a consumer customises CONFIG. Fixtures are strings in
* this file, written to temp directories. Nothing here touches the repository
* you are running from.
*
* Run: node --test scripts/check-backend.test.mjs
*/
import test, { after } from 'node:test'
import assert from 'node:assert/strict'
import { execFileSync } from 'node:child_process'
import { chmodSync, existsSync, mkdirSync, mkdtempSync, readFileSync, rmSync, symlinkSync, utimesSync, writeFileSync } from 'node:fs'
import { tmpdir } from 'node:os'
import { dirname, join } from 'node:path'
import { fileURLToPath } from 'node:url'
import {
CannotCheck,
DEFAULT_CONFIG,
USERS,
alembicBindUses,
denyTests,
denyTestsPart,
detectPlatform,
discoverSources,
engineFix,
evaluate,
introspect,
introspectSqlite,
jsonGroups,
loadFailed,
loadSchema,
loadSqlite,
loadSqliteSchema,
makeSnapshot,
openDatabase,
placeholderFor,
planCommand,
planSources,
prepareInputs,
prune,
prepareSqlite,
resolveInputs,
run,
runCommand,
runLints,
scanRepo,
schemaGate,
stripNonSql,
validateConfig,
} from './check-backend.mjs'
// Every temp dir this file makes. They are removed when the file's tests end, pass or fail.
const made = []
const tmp = (prefix) => {
const dir = mkdtempSync(join(tmpdir(), prefix))
made.push(dir)
return dir
}
after(() => {
for (const dir of made) rmSync(dir, { recursive: true, force: true })
})
const CHECK = join(dirname(fileURLToPath(import.meta.url)), 'check-backend.mjs')
const MIGRATION = 'supabase/migrations/20260101000000_init.sql'
const TODAY = '2026-10-01'
// -------------------------------------------------------------- fixtures ---
/** The green fixture: a Supabase-shaped repo that mirrors the example contract. */
const GREEN_SQL = `create table public.customers (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references auth.users(id),
name text not null,
email text not null,
billing_address jsonb
);
create table public.invoices (
id uuid primary key default gen_random_uuid(),
invoice_number text unique,
customer_id uuid not null references public.customers(id),
total_amount numeric not null check (total_amount >= 0),
due_date date not null,
tax_rate numeric,
pdf_url text,
created_at timestamptz default now()
);
create table public.payments (
id uuid primary key default gen_random_uuid(),
customer_id uuid not null references public.customers(id),
invoice_id uuid not null references public.invoices(id),
amount numeric not null,
method text check (method in ('card', 'bank_transfer'))
);
alter table public.customers enable row level security;
alter table public.invoices enable row level security;
alter table public.payments enable row level security;
create policy "own customer" on public.customers for all to authenticated
using (user_id = (select auth.uid())) with check (user_id = (select auth.uid()));
create policy "own invoices" on public.invoices for all to authenticated
using (customer_id in (select id from public.customers where user_id = (select auth.uid())))
with check (customer_id in (select id from public.customers where user_id = (select auth.uid())));
create policy "own payments" on public.payments for all to authenticated
using (customer_id in (select id from public.customers where user_id = (select auth.uid())))
with check (customer_id in (select id from public.customers where user_id = (select auth.uid())));
`
/**
* The contract the green fixture keeps: the example contract's invoice,
* customer and payment, with tables, refs, the tenant column and membership.
* A string, so this file never reads files/examples/contract.json.
*/
const FIXTURE_CONTRACT = `{
"actors": [
{ "id": "finance_admin", "description": "Staff who issue and void invoices. Internal." },
{ "id": "customer", "description": "The billed party's contact. External, scoped to one customer record." },
{ "id": "billing_webhook", "kind": "system", "description": "Stripe. Tells us a charge settled or failed." }
],
"access": {
"tenancy": {
"scope_entity": "customer",
"column": "customer_id",
"membership": { "table": "public.customers", "user_column": "user_id", "tenant_column": "id" },
"rule": "Every invoice and payment row carries customer_id. A customer actor reads only rows whose customer_id is their own.",
"staff_exception": "finance_admin reads across all customers."
},
"matrix": [
{ "actor": "finance_admin", "entity": "invoice", "can": ["read", "create", "void"], "cannot": ["update"] },
{ "actor": "customer", "entity": "invoice", "can": ["read", "pay"], "scope": "own" },
{ "actor": "customer", "entity": "payment", "can": ["read"], "scope": "own" },
{ "actor": "billing_webhook", "entity": "payment", "can": ["create"], "scope": "all" }
]
},
"entities": [
{
"id": "invoice",
"table": "public.invoices",
"fields": [
"invoice_number",
{ "name": "total_amount", "type": "money", "required": true },
{ "name": "customer_id", "type": "id", "required": true, "ref": "customer" },
"created_at",
{ "name": "due_date", "type": "date", "required": true },
"line_items",
{ "name": "tax_rate", "type": "decimal" },
{ "name": "pdf_url", "type": "url" }
]
},
{
"id": "customer",
"table": "public.customers",
"fields": [
{ "name": "customer_id", "type": "id", "column": "id" },
{ "name": "name", "type": "text", "required": true, "pii": true },
{ "name": "email", "type": "email", "required": true, "pii": true },
{ "name": "billing_address", "type": "address", "pii": true }
]
},
{
"id": "payment",
"table": "public.payments",
"fields": [
{ "name": "amount", "type": "money", "required": true },
{ "name": "method", "type": "enum", "values": ["card", "bank_transfer"] },
{ "name": "customer_id", "type": "id", "required": true, "ref": "customer" },
{ "name": "invoice_id", "type": "id", "required": true, "ref": "invoice" }
]
}
],
"journeys": [
{ "id": "pay-invoice", "actor": "customer", "entities": ["invoice", "payment"], "state": "partial" }
]
}
`
const contract = () => JSON.parse(FIXTURE_CONTRACT)
// Pinned to supabase: most fixtures here are Supabase-shaped. The 'auto' tests say so themselves.
const configWith = (overrides = {}) => ({ ...structuredClone(DEFAULT_CONFIG), platform: 'supabase', ...overrides })
// ---------------------------------------------------------------- engine ---
// One boot with the shim, dumped once. Every fixture that needs nothing more
// loads from this instead of booting.
let shimDump = null
const shimDataDir = async () => {
if (!shimDump) {
const db = await openDatabase({ config: configWith() })
shimDump = await db.dumpDataDir('gzip')
await db.close()
}
return shimDump
}
/**
* Build a snapshot from { path: sql } in this process. `keep` hands back the
* open database too, for tests that query it; the caller closes it.
*/
async function build(files, overrides = {}, { keep = false } = {}) {
const config = configWith(overrides)
// A value may be { text, late: true } for a source with late: true.
const inputs = Object.entries(files).map(([path, v]) => ({ path, text: v.text ?? v, sections: null, late: v.late === true }))
const prepared = await prepareInputs(inputs, config)
const fast =
config.platform === 'supabase' &&
config.supabase.defaultGrants &&
prepared.extensions.every((e) => ['pgcrypto', 'uuid-ossp'].includes(e))
let db = await openDatabase({ config, extensions: prepared.extensions, dataDir: fast ? await shimDataDir() : null })
try {
db = await loadSchema(db, prepared, config)
const found = await introspect(db, config)
const lints = await runLints(db, config, found)
const snapshot = makeSnapshot({ found, lints, prepared, config, inputsHash: 'test', pgliteVersion: 'test' })
return keep ? { snapshot, db, config } : { snapshot, config }
} finally {
if (!keep) await db.close()
}
}
/** The CannotCheck a build throws, or a failure if it does not throw one. */
async function refusal(files, overrides) {
try {
await build(files, overrides)
} catch (e) {
if (e instanceof CannotCheck) return e.toJSON()
throw e
}
assert.fail('expected the build to refuse, and it loaded')
}
const codes = (list) => list.map((x) => x.code)
/**
* The access gate on { path: sql }: build, run the deny tests on the same
* database, and evaluate. `edit` changes the contract; `check` runs against
* the open database before it closes. `report(config)` evaluates again with
* other allowlists.
*/
async function access(files, overrides = {}, { edit = (c) => c, check = null } = {}) {
const { snapshot, db, config } = await build(files, overrides, { keep: true })
try {
const c = edit(contract()) ?? null
const deny = c ? await denyTests(db, snapshot, { config, contract: c }) : null
if (check) await check(db, snapshot, config, c)
// Allowlists apply at report time, so one deny run serves every config
// that differs only in them.
const report = (more = {}) => {
const out = evaluate({ snapshot, contract: c, config: { ...config, ...more }, gates: { schema: false, access: true }, today: TODAY, deny })
const exit = out.findings.length ? 1 : out.untested.length ? 2 : 0
return { ...out, exit, deny, snapshot, all: [...codes(out.findings), ...codes(out.untested)], report }
}
return report()
} finally {
await db.close()
}
}
test('shim grants: authenticated can read a table created after the shim, and not auth.users', async () => {
// Without the default grants, a table with RLS off reads as "permission
// denied", which a deny test counts as a pass (review B1).
const { db } = await build({ [MIGRATION]: GREEN_SQL }, {}, { keep: true })
try {
const q = async (sql) => (await db.query(sql)).rows[0].ok
assert.equal(await q("select has_table_privilege('authenticated', 'public.invoices', 'SELECT') as ok"), true)
assert.equal(await q("select has_table_privilege('anon', 'public.invoices', 'SELECT') as ok"), true)
assert.equal(await q("select has_table_privilege('authenticated', 'auth.users', 'SELECT') as ok"), false)
} finally {
await db.close()
}
})
test('shim grants: turned off, the shim assumes none', async () => {
const { db } = await build({ [MIGRATION]: GREEN_SQL }, { supabase: { defaultGrants: false } }, { keep: true })
try {
const { rows } = await db.query("select has_table_privilege('authenticated', 'public.invoices', 'SELECT') as ok")
assert.equal(rows[0].ok, false)
} finally {
await db.close()
}
})
test('the snapshot holds what the checks read', async () => {
const { snapshot } = await build({ [MIGRATION]: GREEN_SQL })
assert.equal(snapshot.format, 'ntent-schema/1')
assert.equal(snapshot.loader.shim, 'supabase/2')
assert.deepEqual(snapshot.loader.extensions, ['pgcrypto', 'uuid-ossp'])
assert.deepEqual(
Object.keys(snapshot.tables).sort(),
['auth.users', 'public.customers', 'public.invoices', 'public.payments'],
'platform schemas are left out, except auth.users',
)
assert.equal(snapshot.tables['auth.users'].platform, true)
const invoices = snapshot.tables['public.invoices']
assert.equal(invoices.kind, 'table')
assert.deepEqual(invoices.primaryKey, ['id'])
assert.deepEqual(invoices.unique, [['invoice_number']])
assert.deepEqual(invoices.rls, { enabled: true, forced: false })
assert.deepEqual(invoices.foreignKeys, [
{ name: 'invoices_customer_id_fkey', columns: ['customer_id'], references: 'public.customers', referencedColumns: ['id'] },
])
assert.deepEqual(invoices.columns.total_amount, {
type: 'numeric', family: 'num', nullable: false, default: null, identity: null, generated: null, array: false, enum: null,
})
assert.equal(invoices.columns.id.family, 'uuid')
assert.equal(invoices.columns.id.default, 'gen_random_uuid()')
assert.equal(invoices.columns.created_at.family, 'time')
assert.equal(invoices.checks[0].def, 'CHECK ((total_amount >= (0)::numeric))')
assert.deepEqual(invoices.policies.map((p) => [p.name, p.command, p.roles]), [['own invoices', 'ALL', ['authenticated']]])
assert.deepEqual(invoices.grants.authenticated, ['DELETE', 'INSERT', 'SELECT', 'UPDATE'])
assert.equal(invoices.securityInvoker, null)
})
test('the snapshot resolves enums, domains, arrays, identity and views', async () => {
const { snapshot } = await build({
[MIGRATION]: `create type public.status as enum ('draft', 'issued');
create domain public.cents as bigint check (value >= 0);
create table public.things (
id bigint generated always as identity primary key,
status public.status not null default 'draft',
price public.cents,
tags text[],
doubled bigint generated always as (id * 2) stored
);
create view public.v with (security_invoker = true) as select id, status from public.things;
create view public.u as select id from auth.users;`,
})
const things = snapshot.tables['public.things']
assert.equal(things.columns.id.identity, 'always')
assert.deepEqual(things.columns.status.enum, ['draft', 'issued'])
assert.equal(things.columns.status.family, 'text')
assert.equal(things.columns.price.family, 'num', 'a domain resolves to its base type')
assert.deepEqual([things.columns.tags.family, things.columns.tags.array], ['text', true])
assert.equal(things.columns.doubled.generated, '(id * 2)')
assert.deepEqual(snapshot.enums['public.status'], ['draft', 'issued'])
assert.equal(snapshot.tables['public.v'].kind, 'view')
assert.equal(snapshot.tables['public.v'].securityInvoker, true)
assert.deepEqual(snapshot.tables['public.v'].dependsOn, ['public.things'])
assert.equal(snapshot.tables['public.u'].securityInvoker, false)
assert.deepEqual(snapshot.tables['public.u'].dependsOn, ['auth.users'])
})
test('shim 2: storage.buckets, extensions.gen_random_bytes and auth.role() load', async () => {
const { snapshot, config } = await build({
[MIGRATION]: `insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values ('avatars', 'avatars', true, 5242880, array['image/png']);
create table public.tokens (
id uuid primary key default gen_random_uuid(),
secret bytea not null default extensions.gen_random_bytes(16),
made_by text default auth.role()
);
alter table public.tokens enable row level security;
create policy "service only" on public.tokens for all using (auth.role() = 'service_role');`,
})
assert.ok(snapshot.tables['public.tokens'])
const out = evaluate({ snapshot, contract: null, config, gates: { schema: true, access: false }, today: TODAY })
assert.deepEqual(out.findings, [])
assert.deepEqual(out.untested, [])
})
test('pg_dump style: search_path and row_security are reset after the load', async () => {
const { snapshot, db } = await build(
{
[MIGRATION]: `set search_path = '';
set row_security = off;
create table public.notes (id uuid primary key default gen_random_uuid(), body text);
alter table public.notes enable row level security;`,
},
{},
{ keep: true },
)
try {
assert.ok(snapshot.tables['public.notes'])
assert.equal((await db.query('show row_security')).rows[0].row_security, 'on')
assert.match((await db.query('show search_path')).rows[0].search_path, /public/)
} finally {
await db.close()
}
})
test('extensions: a built-in stub loads, one PGlite lacks is exit 2, and a stub entry loads it', async () => {
const stubbed = await build({ [MIGRATION]: 'create extension if not exists pg_net with schema extensions;\nselect net.http_post(url := \'https://x\');' })
assert.deepEqual(stubbed.snapshot.loader.stubbed, [{ name: 'pg_net', reason: 'built in stub', by: 'builtin' }])
assert.equal(stubbed.snapshot.loader.rewritten[0].line, 1)
const postgis = 'create extension postgis;\ncreate table public.places (id int primary key);'
const refused = await refusal({ [MIGRATION]: postgis })
assert.equal(refused.code, 'EXTENSION-UNAVAILABLE')
assert.equal(refused.object, 'postgis')
assert.match(refused.fix, /CONFIG\.stubExtensions/)
const entry = { reason: 'geometry is never read by the checks', until: '2027-01-01' }
const loaded = await build({ [MIGRATION]: postgis }, { stubExtensions: { postgis: entry } })
assert.ok(loaded.snapshot.tables['public.places'])
const out = evaluate({ snapshot: loaded.snapshot, contract: null, config: loaded.config, gates: { schema: true, access: false }, today: TODAY })
assert.deepEqual(out.waived, [{ list: 'stubExtensions', key: 'postgis', code: 'EXTENSION-UNAVAILABLE', until: '2027-01-01', reason: entry.reason }])
})
test('extensions: a PGlite contrib module is loaded, not stubbed', async () => {
const { snapshot } = await build({ [MIGRATION]: 'create extension citext;\ncreate table public.users2 (email citext primary key);' })
assert.ok(snapshot.loader.extensions.includes('citext'))
assert.equal(snapshot.tables['public.users2'].columns.email.family, 'text')
})
test('create index concurrently is rewritten, keeping the line count', async () => {
const { snapshot } = await build({
[MIGRATION]: 'create table public.t (id int primary key, n int);\n\ncreate index concurrently t_n on public.t (n);',
})
assert.deepEqual(snapshot.loader.rewritten, [{ file: MIGRATION, line: 3, what: 'create index concurrently -> create index' }])
})
test('load error: a typo names the file and the line', async () => {
const second = 'supabase/migrations/20260102000000_more.sql'
const e = await refusal({ [MIGRATION]: GREEN_SQL, [second]: 'create table public.a (id int);\n\ncreat table public.b (id int);\n' })
assert.equal(e.code, 'LOAD-FAILED')
assert.equal(e.object, `${second}:3`)
assert.equal(e.sqlstate, '42601')
})
test('runtime load error: the statement\'s line, the SQLSTATE; the preamble fixes it', async () => {
const files = { [MIGRATION]: 'create table public.a (id int);\nalter table public.nope add column x int;' }
const e = await refusal(files)
assert.equal(e.code, 'LOAD-FAILED')
assert.equal(e.object, `${MIGRATION}:2`, 'no position, so the line of the statement')
assert.equal(e.sqlstate, '42P01')
assert.match(e.message, /^42P01 relation "public.nope" does not exist/)
assert.match(e.fix, /CONFIG\.preamble/)
const fixed = await build(files, { preamble: [{ sql: 'create table public.nope (id int)', reason: 'made by the platform' }] })
assert.ok(fixed.snapshot.tables['public.nope'].columns.x)
})
test('a preamble that fails is CONFIG-INVALID naming the entry', async () => {
const e = await refusal({ [MIGRATION]: 'select 1;' }, { preamble: [{ sql: 'creat table x ()', reason: 'typo' }] })
assert.equal(e.code, 'CONFIG-INVALID')
assert.equal(e.object, 'preamble[0]')
})
// ----------------------------------------------------------- schema gate ---
const RED_SQL = GREEN_SQL.replace('due_date date not null', 'due_date date').replace('total_amount numeric not null check (total_amount >= 0)', 'total_amount boolean not null')
test('schema gate: green is clean', async () => {
const { snapshot, config } = await build({ [MIGRATION]: GREEN_SQL })
const out = evaluate({ snapshot, contract: contract(), config, gates: { schema: true, access: false }, today: TODAY })
assert.deepEqual(out.findings, [])
assert.deepEqual(codes(out.notes), ['MISSING-COLUMN'])
assert.match(out.notes[0].message, /invoice -> public\.invoices: 7 of 8 fields have a column \(missing: line_items\)/)
})
test('schema gate: required but nullable, a type contradiction, an entity with no table', async () => {
const { snapshot, config } = await build({ [MIGRATION]: RED_SQL })
const out = schemaGate(snapshot, contract(), config)
assert.deepEqual(codes(out.findings), ['TYPE-CONTRADICTS', 'REQUIRED-BUT-NULLABLE'], 'in field order')
assert.equal(out.findings[0].message, 'invoice.total_amount is money in the model; the column is boolean')
assert.equal(out.findings[1].object, 'public.invoices.due_date')
assert.equal(out.findings[1].message, 'invoice.due_date is required in the model; public.invoices.due_date allows null')
const misspelt = contract()
misspelt.entities[0].table = 'public.invoice'
const gone = schemaGate(snapshot, misspelt, config)
assert.deepEqual(codes(gone.findings), ['ENTITY-NO-TABLE'])
assert.equal(gone.findings[0].message, 'invoice maps to public.invoice, which the migrations never create')
})
/** A snapshot built by hand, for the gate's rules that need no database. */
const fakeSnapshot = (tables) => ({ loader: { stubbed: [] }, tables })
const column = (type, family, extra = {}) => ({ type, family, nullable: true, default: null, identity: null, generated: null, array: false, enum: null, ...extra })
const table = (columns, extra = {}) => ({ kind: 'table', columns, primaryKey: [], foreignKeys: [], unique: [], checks: [], policies: [], ...extra })
test('schema gate: did you mean, for a Prisma-style table name', () => {
const snapshot = fakeSnapshot({ 'public.Invoice': table({ id: column('text', 'text') }) })
const out = schemaGate(snapshot, { entities: [{ id: 'invoice', table: 'invoice', fields: [] }] }, configWith())
assert.equal(out.findings[0].message, 'invoice maps to public.invoice, which the migrations never create (did you mean public."Invoice"?)')
})
test('schema gate: arrays, views, relations, column names, refs and the coverage floor', () => {
const snapshot = fakeSnapshot({
'public.orders': table({
id: column('uuid', 'uuid', { nullable: false }),
tags: column('text[]', 'text', { array: true }),
customerId: column('uuid', 'uuid'),
}),
'public.order_view': table({ total: column('numeric', 'num') }, { kind: 'view' }),
'public.customers': table({ id: column('uuid', 'uuid', { nullable: false }) }),
})
const c = {
entities: [
{
id: 'order',
table: 'orders',
fields: [
{ name: 'tags', type: 'text' },
{ name: 'customer_id', type: 'id', ref: 'customer', required: true },
{ name: 'lines', many: true },
{ name: 'buyer', type: 'customer' },
{ name: 'gone', type: 'text' },
],
},
{ id: 'order_total', table: 'order_view', fields: [{ name: 'total', type: 'money', required: true }] },
{ id: 'customer', table: 'customers', fields: ['id'] },
],
}
const out = schemaGate(snapshot, c, configWith({ columnNames: 'camel', minFieldCoverage: 0.9 }))
assert.deepEqual(
out.findings.map((f) => [f.code, f.object]),
[
['TYPE-CONTRADICTS', 'public.orders.tags'],
['REQUIRED-BUT-NULLABLE', 'public.orders.customerId'],
['FIELD-COVERAGE-BELOW-FLOOR', 'minFieldCoverage'],
],
'an array against a scalar contradicts; a view is never REQUIRED-BUT-NULLABLE; camel finds customerId',
)
assert.equal(out.findings[2].message, '80% of mapped fields have a column; the floor is 90%')
const notes = Object.fromEntries(out.notes.map((n) => [n.code, n.message]))
assert.match(notes['REF-NO-FK'], /order\.customer_id points at customer, and public\.orders\.customerId has no foreign key to public\.customers/)
assert.match(notes.RELATION, /order: lines, buyer: relation, not checked/)
assert.match(notes['MISSING-COLUMN'], /order -> public\.orders: 2 of 3 fields have a column \(missing: gone\)/)
})
test('framework tables are not UNLISTED-TABLE; an unmapped app table is', async () => {
const { snapshot, config } = await build({
[MIGRATION]: `${GREEN_SQL}
create table public._prisma_migrations (id text primary key);
create table public.django_session (session_key text primary key);
create table public.notes (id int primary key);`,
})
const out = schemaGate(snapshot, contract(), config)
const unlisted = out.notes.find((n) => n.code === 'UNLISTED-TABLE')
assert.deepEqual(unlisted.tables, ['public.notes'])
})
test('no contract: nothing is mapped, every table is unlisted, and it says so', async () => {
const { snapshot, config } = await build({ [MIGRATION]: GREEN_SQL })
const out = evaluate({ snapshot, contract: null, config, gates: { schema: true, access: false }, today: TODAY })
assert.deepEqual(out.findings, [])
assert.deepEqual(codes(out.notes), ['NO-CONTRACT', 'UNLISTED-TABLE'])
})
// ----------------------------------------------------------- access gate ---
const G = { [MIGRATION]: GREEN_SQL }
const sql = (text) => ({ [MIGRATION]: text })
test('access: green is clean, every probe runs, and every test ends in rollback', async () => {
let again = null
const out = await access(G, {}, {
check: async (db, snapshot, config, c) => {
const left = async () =>
(await db.query(
`select (select count(*) from auth.users)::int + (select count(*) from public.customers)::int
+ (select count(*) from public.invoices)::int + (select count(*) from public.payments)::int as n`,
)).rows[0].n
assert.equal(await left(), 0, 'nothing the first run seeded is left')
again = await denyTests(db, snapshot, { config, contract: c })
assert.equal(await left(), 0)
},
})
assert.deepEqual(out.all, [], JSON.stringify([...out.findings, ...out.untested], null, 2))
assert.equal(out.exit, 0)
assert.deepEqual(
out.deny.tests.map((t) => [t.actor, t.entity, t.probes]),
[
['customer', 'invoice', { P1: 'pass', P2: 'pass', P3: 'pass', P4: 'pass', P5: 'pass' }],
['customer', 'payment', { P1: 'pass', P2: 'pass', P3: 'pass', P4: 'pass', P5: 'pass' }],
],
)
const skipped = out.notes.filter((n) => n.code === 'NOT-DENY-TESTED').map((n) => n.object)
assert.deepEqual(skipped, ['finance_admin on invoice', 'billing_webhook on payment'])
assert.deepEqual([again.findings, again.untested], [[], []], 'a second run on the same database is as clean')
})
// The green invoices policy, and the tenant condition it uses, so a variant
// can swap one policy without retyping the rest.
const TENANT = 'customer_id in (select id from public.customers where user_id = (select auth.uid()))'
const OWN_INVOICES = `create policy "own invoices" on public.invoices for all to authenticated
using (${TENANT})
with check (${TENANT});
`
const withInvoices = (policies) => {
assert.ok(GREEN_SQL.includes(OWN_INVOICES))
return sql(GREEN_SQL.replace(OWN_INVOICES, policies))
}
const plus = (extra) => sql(`${GREEN_SQL}\n${extra}`)
// Invoices with one policy per command, scoped, except what `read` says.
const splitInvoices = (read = `using (${TENANT})`) =>
withInvoices(`create policy "invoices read" on public.invoices for select to authenticated ${read};
create policy "invoices insert" on public.invoices for insert to authenticated with check (${TENANT});
create policy "invoices update" on public.invoices for update to authenticated using (${TENANT}) with check (${TENANT});
create policy "invoices delete" on public.invoices for delete to authenticated using (${TENANT});
`)
const NO_RLS_INVOICES = 'alter table public.invoices enable row level security;\n'
const find = (out, code) => [...out.findings, ...out.untested, ...out.notes].find((x) => x.code === code)
test('access: missing grants never pass: DENIED-BY-GRANT notes, and RLS-OFF', async () => {
const out = await access(sql(GREEN_SQL.replace(NO_RLS_INVOICES, '').replace(OWN_INVOICES, '')), { supabase: { defaultGrants: false } })
assert.equal(out.exit, 1)
assert.deepEqual(out.all, ['RLS-OFF'], 'every probe stopped at a grant, by Supabase roles: a real result, and not a hollow one')
assert.equal(out.findings[0].object, 'public.invoices')
const denied = out.notes.filter((n) => n.code === 'DENIED-BY-GRANT').map((n) => n.object)
assert.equal(denied.length, 8, 'four commands on two tables')
assert.ok(denied.includes('public.invoices as customer'))
})
test('access: RLS off is RLS-OFF and every leak; with the policy left, POLICY-BUT-RLS-OFF', async () => {
const off = await access(sql(GREEN_SQL.replace(NO_RLS_INVOICES, '').replace(OWN_INVOICES, '')))
assert.deepEqual(off.all, ['RLS-OFF', 'LEAK-READ', 'LEAK-UPDATE', 'LEAK-DELETE', 'LEAK-INSERT'])
assert.equal(off.findings[0].object, 'public.invoices')
assert.match(off.findings[0].fix, /^Enable row level security on public\.invoices and add a policy, or add it to CONFIG\.publicTables/)
assert.match(off.findings[1].message, /policies for SELECT: none apply, row level security is off on public\.invoices/)
const left = await access(sql(GREEN_SQL.replace(NO_RLS_INVOICES, '')))
assert.deepEqual(left.all, ['POLICY-BUT-RLS-OFF', 'LEAK-READ', 'LEAK-UPDATE', 'LEAK-DELETE', 'LEAK-INSERT'])
assert.equal(left.findings[0].fix, 'The policies on public.invoices do nothing until RLS is enabled: alter table public.invoices enable row level security.')
})
test('access: read true is SCOPED-READ-TRUE and LEAK-READ, in the finding text section 7 gives', async () => {
const out = await access(splitInvoices('using (true)'))
assert.deepEqual(out.all, ['SCOPED-READ-TRUE', 'LEAK-READ'])
const leak = out.findings[1]
assert.deepEqual([leak.object, leak.actor], ['public.invoices', 'customer'])
assert.equal(
leak.message,
'user A read 1 invoice row that belongs to another customer.\npolicies for SELECT: "invoices read" using (true)',
)
// A using (true) select policy looks public on purpose: the contract change comes first, the old fix second (fix4).
assert.match(leak.fix, /^This policy looks public on purpose: it is using \(true\)/)
assert.ok(leak.fix.endsWith('the policy is the leak: scope the policy to the tenant: using (customer_id in (select id from public.customers where user_id = auth.uid())).'), leak.fix)
assert.match(out.findings[0].fix, /^This policy looks public on purpose: it is using \(true\).*give customer scope 'all' on invoice for read/)
assert.match(out.findings[0].fix, /If it is not, the policy is the leak: policy invoices read lets any signed-in user read every row of public\.invoices, which the model scopes own\./)
})
test('access: update true and delete true go red through the blind probes', async () => {
// Each blind probe reads only its own command's policies, so one build
// serves both.
const out = await access(
plus(`create policy "anyone updates" on public.invoices for update to authenticated using (true);
create policy "anyone deletes" on public.invoices for delete to authenticated using (true);`),
)
assert.deepEqual(out.all, ['POLICY-ALWAYS-TRUE', 'POLICY-ALWAYS-TRUE', 'LEAK-UPDATE', 'LEAK-DELETE'])
assert.deepEqual(out.deny.tests[0].probes, { P1: 'pass', P2: 'pass', P3: 'leak', P4: 'leak', P5: 'pass' })
const update = out.findings[2]
assert.match(update.message, /^user A changed, with an update that reads no column, an invoice row that belongs to another customer\./)
assert.match(update.message, /"anyone updates" using \(true\); "own invoices" using \(customer_id IN/)
})
test('access: update steal is LEAK-UPDATE, from the moved row and from the 42501', async () => {
const steal = `create policy "steal" on public.invoices for update to authenticated using (true) with check (${TENANT});`
// P3 sets customer_id, the first column it may update: the move succeeds.
const moved = await access(plus(steal))
assert.deepEqual(moved.all, ['POLICY-ALWAYS-TRUE', 'LEAK-UPDATE'])
assert.match(moved.findings[1].message, /^user A changed, with an update that reads no column, an invoice row/)
// With customer_id not updatable, P3 sets total_amount on B's row, and only
// WITH CHECK refuses it: the 42501 is the leak.
const refused = await access(
plus(`${steal}
revoke update on public.invoices from authenticated;
grant update (total_amount, due_date) on public.invoices to authenticated;`),
)
assert.deepEqual(refused.all, ['POLICY-ALWAYS-TRUE', 'LEAK-UPDATE'])
assert.match(refused.findings[1].message, /^user A reached, with an update that reads no column, and could move to their own customer by setting customer_id,/)
})
test('access: insert with check (true) is LEAK-INSERT, found by counting, not RETURNING', async () => {
const out = await access(
withInvoices(`create policy "own invoices" on public.invoices for all to authenticated using (${TENANT}) with check (true);\n`),
)
assert.deepEqual(out.all, ['POLICY-ALWAYS-TRUE', 'LEAK-INSERT'])
assert.equal(out.findings[1].fix, 'scope the INSERT policy\'s WITH CHECK to the tenant: with check (customer_id in (select id from public.customers where user_id = auth.uid())).')
})
test('access: a trigger that sets the tenant passes, with INSERT-REWRITTEN', async () => {
const out = await access(
plus(`create function public.force_customer() returns trigger language plpgsql as $$
begin
new.customer_id := (select id from public.customers where user_id = auth.uid() limit 1);
return new;
end $$;
create trigger force_customer before insert on public.invoices for each row execute function public.force_customer();`),
)
assert.equal(out.exit, 0, JSON.stringify(out.all))
assert.equal(find(out, 'INSERT-REWRITTEN').object, 'public.invoices as customer')
})
test('access: a domain check that fires is PROBE-ERROR naming the domain, never LEAK-INSERT', async () => {
// The seeder satisfies value >= 0; this domain refuses the API role, so it
// fires on P5 and never while seeding.
const out = await access(
sql(
GREEN_SQL.replace('create table public.invoices', "create domain public.amount as numeric check (value >= 0 and current_user <> 'authenticated');\ncreate table public.invoices")
.replace('total_amount numeric not null check (total_amount >= 0)', 'total_amount public.amount not null'),
),
)
assert.equal(out.exit, 2, JSON.stringify(out.all))
assert.ok(!out.all.includes('LEAK-INSERT'))
const error = out.untested.find((u) => u.code === 'PROBE-ERROR' && u.table === 'public.invoices')
assert.match(error.message, /^P5 failed: 23514 .*It reaches domain amount\.$/)
})
test('access: an audit trigger the role cannot write is PROBE-ERROR naming audit_log', async () => {
const out = await access(
plus(`create table public.audit_log (id bigint generated always as identity primary key, at timestamptz default now());
alter table public.audit_log enable row level security;
revoke all on public.audit_log from anon, authenticated;
create function public.audit() returns trigger language plpgsql as $$
begin
insert into public.audit_log default values;
return new;
end $$;
create trigger audit after insert or update on public.invoices for each row execute function public.audit();`),
)
assert.equal(out.exit, 2, JSON.stringify(out.all))
const error = out.untested.find((u) => u.code === 'PROBE-ERROR')
assert.equal(error.object, 'public.invoices')
assert.match(error.message, /^P3 failed: 42501 permission denied for table audit_log\. It reaches table audit_log\.$/)
assert.match(error.fix, /add 'public\.invoices as customer' to CONFIG\.untested/)
})
test('access: column grants are LEAK-READ, not DENIED-BY-GRANT; with id left out, PROBE-ERROR', async () => {
const grants = (columns) => `revoke select on public.invoices from authenticated;
grant select (${columns}) on public.invoices to authenticated;`
const out = await access(sql(`${splitInvoices('using (true)')[MIGRATION]}\n${grants('id, customer_id, total_amount')}`))
assert.deepEqual(out.all, ['SCOPED-READ-TRUE', 'LEAK-READ'])
assert.ok(!out.notes.some((n) => n.code === 'DENIED-BY-GRANT'))
const noId = await access(plus(grants('customer_id, total_amount')))
assert.equal(noId.exit, 2)
assert.deepEqual(noId.all, ['PROBE-ERROR'])
assert.match(noId.untested[0].message, /cannot read public\.invoices\.id, so the test cannot address a row/)
})
test('access: a user_id that defaults to auth.uid() is seeded as the principal', async () => {
const out = await access(sql(GREEN_SQL.replace('user_id uuid not null references auth.users(id)', 'user_id uuid not null default auth.uid() references auth.users')))
assert.equal(out.exit, 0, JSON.stringify(out.all))
})
const CONTACT = `create table public.contact_messages (
id uuid primary key default gen_random_uuid(),
user_id uuid not null default auth.uid() references auth.users,
body text not null
);
alter table public.contact_messages enable row level security;
`
const INSERT_ONLY = 'create policy "send" on public.contact_messages for insert to authenticated with check (user_id = (select auth.uid()));'
const withContact = (c) => {
c.entities.push({ id: 'contact_message', table: 'public.contact_messages', owner: 'user_id', fields: [{ name: 'user_id', type: 'id' }, 'body'] })
c.access.matrix.push({ actor: 'customer', entity: 'contact_message', can: ['create'], scope: 'own' })
return c
}
test('access: write-only skips P1 and P2 and passes; without its insert policy, OWN-INSERT-REFUSED', async () => {
const out = await access(plus(`${CONTACT}${INSERT_ONLY}`), {}, { edit: withContact })
assert.equal(out.exit, 0, JSON.stringify(out.all))
const test = out.deny.tests.find((t) => t.entity === 'contact_message')
assert.deepEqual(test.probes, { control: 'pass', P3: 'pass', P4: 'pass', P5: 'pass' })
const refused = await access(plus(CONTACT), {}, { edit: withContact })
assert.equal(refused.exit, 2)
assert.deepEqual(refused.all, ['OWN-INSERT-REFUSED'])
assert.match(refused.untested[0].fix, /CONFIG\.actors\.customer\.claims/)
})
test('access: with no column P3 may set, the narrow update runs and says so', async () => {
const out = await access(
sql(
GREEN_SQL.replace(
/create table public\.payments \([^;]+\);/,
`create table public.payments (
id uuid primary key default gen_random_uuid(),
customer_id uuid not null unique references public.customers(id),
invoice_id uuid not null unique references public.invoices(id),
amount numeric not null unique,
method text generated always as ('card') stored
);`,
),
),
)
assert.equal(out.exit, 0, JSON.stringify(out.all))
assert.equal(find(out, 'UPDATE-PROBE-NARROW').object, 'public.payments as customer')
})
test('access: read true under a RESTRICTIVE tenant policy is left to the deny test, which passes', async () => {
const out = await access(
sql(`${splitInvoices('using (true)')[MIGRATION]}
create policy "tenant only" on public.invoices as restrictive for select to authenticated using (${TENANT});`),
)
assert.equal(out.exit, 0, JSON.stringify(out.all))
})
test('access: policies that read each other are POLICY-RECURSES', async () => {
const out = await access(
sql(
GREEN_SQL.replace(
'using (user_id = (select auth.uid())) with check',
'using (user_id = (select auth.uid()) or id in (select customer_id from public.invoices)) with check',
),
),
)
assert.equal(out.exit, 1)
assert.ok(out.all.includes('POLICY-RECURSES'), JSON.stringify(out.all))
assert.match(find(out, 'POLICY-RECURSES').fix, /^The policies on public\.invoices reference each other in a loop\./)
})
test('access: a policy that reads a claim the test lacks is OWN-ROW-HIDDEN; with the claim, exit 0', async () => {
const files = withInvoices(`create policy "own invoices" on public.invoices for all to authenticated
using (${TENANT} and (auth.jwt() -> 'app_metadata' ->> 'role') = 'customer')
with check (${TENANT});
`)
const hidden = await access(files)
assert.deepEqual(hidden.all, ['OWN-ROW-HIDDEN'])
assert.equal(hidden.exit, 2)
assert.match(hidden.untested[0].fix, /^User A cannot read their own public\.invoices row\./)
const claimed = await access(files, { actors: { customer: { claims: { app_metadata: { role: 'customer' } } } } })
assert.equal(claimed.exit, 0, JSON.stringify(claimed.all))
})
test('access: a ref chain reaches the tenant; a true policy on it is LEAK-READ', async () => {
const chain = (policy) =>
sql(
GREEN_SQL.replace(' customer_id uuid not null references public.customers(id),\n invoice_id', ' invoice_id').replace(
/create policy "own payments"[^;]+;/,
policy,
),
)
const edit = (c) => {
c.entities[2].fields = c.entities[2].fields.filter((f) => f.name !== 'customer_id')
return c
}
const green = await access(
chain(`create policy "own payments" on public.payments for all to authenticated
using (invoice_id in (select id from public.invoices where ${TENANT}))
with check (invoice_id in (select id from public.invoices where ${TENANT}));`),
{},
{ edit },
)
assert.equal(green.exit, 0, JSON.stringify(green.all))
const red = await access(chain('create policy "own payments" on public.payments for all to authenticated using (true);'), {}, { edit })
const leak = red.findings.find((f) => f.code === 'LEAK-READ')
assert.equal(leak.object, 'public.payments')
assert.equal(
leak.fix,
'scope the policy to the tenant: using (invoice_id in (select id from public.invoices where customer_id in (select id from public.customers where user_id = auth.uid()))).',
)
})
const MEMBERSHIP_SQL = (projects) => `create table public.orgs (id uuid primary key default gen_random_uuid(), name text not null);
create table public.org_members (
user_id uuid not null references auth.users,
org_id uuid not null references public.orgs,
primary key (user_id, org_id)
);
create table public.projects (
id uuid primary key default gen_random_uuid(),
org_id uuid not null references public.orgs,
title text not null
);
alter table public.orgs enable row level security;
alter table public.org_members enable row level security;
alter table public.projects enable row level security;
create policy "my orgs" on public.orgs for select to authenticated
using (id in (select org_id from public.org_members where user_id = (select auth.uid())));
create policy "my memberships" on public.org_members for select to authenticated using (user_id = (select auth.uid()));
${projects}`
const MEMBERSHIP_CONTRACT = () => ({
actors: [{ id: 'member' }],
access: {
tenancy: { scope_entity: 'org', column: 'org_id', membership: { table: 'public.org_members', user_column: 'user_id', tenant_column: 'org_id' } },
matrix: [{ actor: 'member', entity: 'project', can: ['read', 'update'], scope: 'tenant' }],
},
entities: [
{ id: 'org', table: 'public.orgs', fields: ['name'] },
{ id: 'project', table: 'public.projects', fields: ['title', { name: 'org_id', type: 'id', ref: 'org' }] },
],
})
test('access: tenants through a membership table: green exit 0, a true policy LEAK-READ', async () => {
const scoped = 'org_id in (select org_id from public.org_members where user_id = (select auth.uid()))'
const green = await access(
sql(MEMBERSHIP_SQL(`create policy "org projects" on public.projects for all to authenticated using (${scoped}) with check (${scoped});`)),
{},
{ edit: MEMBERSHIP_CONTRACT },
)
assert.equal(green.exit, 0, JSON.stringify(green.all))
assert.equal(green.deny.tests.length, 1)
const red = await access(
sql(MEMBERSHIP_SQL('create policy "org projects" on public.projects for all to authenticated using (true) with check (true);')),
{},
{ edit: MEMBERSHIP_CONTRACT },
)
assert.ok(red.all.includes('LEAK-READ'), JSON.stringify(red.all))
assert.match(
red.findings.find((f) => f.code === 'LEAK-READ').message,
/^user A read 1 project row that belongs to another org\./,
)
})
test('access: a tenant a trigger made on signup is reused, not made twice', async () => {
// customers.user_id is unique here: a second row for a user would fail the seed.
const out = await access(
sql(`${GREEN_SQL.replace('user_id uuid not null references auth.users(id)', 'user_id uuid not null unique references auth.users(id)')}
create function public.handle_new_user() returns trigger language plpgsql security definer set search_path = '' as $$
begin
insert into public.customers (user_id, name, email) values (new.id, 'new', coalesce(new.email, ''));
return new;
end $$;
create trigger on_auth_user_created after insert on auth.users for each row execute function public.handle_new_user();`),
)
assert.equal(out.exit, 0, JSON.stringify([...out.findings, ...out.untested]))
})
test('access: the seeder reads an enum and an IN list', async () => {
const out = await access(
sql(
GREEN_SQL.replace('create table public.invoices (', "create type public.invoice_status as enum ('draft', 'issued');\ncreate table public.invoices (\n status public.invoice_status not null,")
.replace("method text check (method in ('card', 'bank_transfer'))", "method text not null check (method in ('card', 'bank_transfer'))"),
),
)
assert.equal(out.exit, 0, JSON.stringify([...out.findings, ...out.untested]))
})
const COMPOSITE = sql(
GREEN_SQL.replace('create table public.invoices (', 'create type public.price as (amount numeric, currency text);\ncreate table public.invoices (\n price public.price not null,'),
)
test('access: a column the seeder cannot fill is SEED-FAILED naming it; CONFIG.untested waives it; CONFIG.seed fixes it', async () => {
const failed = await access(COMPOSITE)
assert.equal(failed.exit, 2)
assert.deepEqual(failed.all, ['SEED-FAILED', 'SEED-FAILED'])
const invoices = failed.untested[0]
assert.equal(invoices.message, 'seeding public.invoices: price has type price, which the seeder cannot synthesise')
assert.equal(invoices.fix, "Give CONFIG.seed['public.invoices'] a value for price, or add 'public.invoices' to CONFIG.untested with a reason and an until date.")
assert.match(failed.untested[1].fix, /add 'public\.payments' to CONFIG\.untested/)
const waived = failed.report({
untested: {
'public.invoices as customer': { reason: 'price is set by the billing service', until: '2027-01-01' },
'public.payments': { reason: 'same', until: '2027-01-01' },
},
})
assert.equal(waived.exit, 0, JSON.stringify(waived.all))
assert.deepEqual(waived.waived.map((w) => [w.key, w.code]), [
['public.invoices as customer', 'SEED-FAILED'],
['public.payments', 'SEED-FAILED'],
])
const seeded = await access(COMPOSITE, { seed: { 'public.invoices': { price: '(1,GBP)' } } })
assert.equal(seeded.exit, 0, JSON.stringify([...seeded.findings, ...seeded.untested]))
})
test('access: an entity with no tenant column, ref or owner is NO-TENANT-PATH', async () => {
const out = await access(
plus(`create table public.notes (id uuid primary key default gen_random_uuid(), body text not null);
alter table public.notes enable row level security;
create policy "notes" on public.notes for all to authenticated using (true);`),
{},
{
edit: (c) => {
c.entities.push({ id: 'note', table: 'public.notes', fields: ['body'] })
c.access.matrix.push({ actor: 'customer', entity: 'note', can: ['read'], scope: 'own' })
return c
},
},
)
const path = out.untested.find((u) => u.code === 'NO-TENANT-PATH')
assert.equal(path.object, 'public.notes')
assert.match(path.fix, /^Nothing links public\.notes to a tenant or owner\./)
})
test('access: definer views, an exposed auth.users and user_metadata in a policy', async () => {
const out = await access(
plus(`create view public.v as select * from public.invoices;
create view public.u as select * from auth.users;
create policy "by metadata" on public.payments for select to authenticated using ((auth.jwt() -> 'user_metadata' ->> 'vip') = 'yes');`),
)
const definer = out.findings.find((f) => f.code === 'DEFINER-VIEW')
assert.equal(definer.object, 'public.v')
assert.equal(definer.fix, 'View public.v runs as its owner and skips the RLS on public.invoices. Add with (security_invoker = true).')
assert.equal(out.findings.find((f) => f.code === 'AUTH-USERS-EXPOSED').object, 'public.u')
assert.match(out.findings.find((f) => f.code === 'USER-METADATA-IN-POLICY').fix, /^Policy by metadata reads user_metadata/)
const invoker = await access(plus('create view public.v with (security_invoker = true) as select * from public.invoices;'))
assert.equal(invoker.exit, 0, JSON.stringify(invoker.all))
})
const POSTGRES_SQL = `create role app_user;
grant usage on schema public to app_user;
create table public.customers (id uuid primary key default gen_random_uuid(), user_id uuid not null, name text not null, email text not null);
create table public.invoices (
id uuid primary key default gen_random_uuid(),
customer_id uuid not null references public.customers(id),
total_amount numeric not null check (total_amount >= 0),
due_date date not null
);
create table public.payments (
id uuid primary key default gen_random_uuid(),
customer_id uuid not null references public.customers(id),
invoice_id uuid not null references public.invoices(id),
amount numeric not null
);
${['customers', 'invoices', 'payments']
.map(
(t) => `alter table public.${t} owner to app_user;
alter table public.${t} enable row level security;`,
)
.join('\n')}
alter table public.invoices force row level security;
create policy "own customer" on public.customers for all to app_user using (user_id = current_setting('app.user_id', true)::uuid);
create policy "own invoices" on public.invoices for all to app_user
using (customer_id in (select id from public.customers where user_id = current_setting('app.user_id', true)::uuid));
create policy "own payments" on public.payments for all to app_user
using (customer_id in (select id from public.customers where user_id = current_setting('app.user_id', true)::uuid));
`
test('access: postgres platform: an owning role skips RLS unless it is forced', async () => {
// app_user owns every table; only invoices forces RLS.
const out = await access(sql(POSTGRES_SQL), { platform: 'postgres' })
const notForced = out.findings.filter((f) => f.code === 'RLS-NOT-FORCED').map((f) => f.object)
assert.deepEqual(notForced, ['public.customers', 'public.payments'])
assert.match(out.findings[0].fix, /^app_user owns public\.customers, and owners skip RLS\. alter table public\.customers force row level security/)
const [invoices, payments] = out.deny.tests
assert.deepEqual(invoices.probes, { P1: 'pass', P2: 'pass', P3: 'pass', P4: 'pass', P5: 'pass' }, 'forced, the deny test runs and passes')
assert.equal(payments.probes.P2, 'leak', 'not forced, the deny test runs as the owner and finds the bypass')
})
test('access: publicTables waives, expires and goes stale; a framework table gets the revoke fix', async () => {
const out = await access(
plus(`create table public.countries (code text primary key);
create table public._prisma_migrations (id text primary key);
revoke all on public._prisma_migrations from anon, authenticated;
create table public.django_session (session_key text primary key);`),
)
assert.deepEqual(out.findings.map((f) => [f.code, f.object]), [
['RLS-OFF', 'public.countries'],
['RLS-OFF', 'public.django_session'],
], 'a framework table the API cannot reach is not a finding')
assert.match(out.findings[1].fix, /^public\.django_session is in CONFIG\.ignoreTables, which only quiets the schema gate\. Revoke what the API roles hold \(revoke all on public\.django_session from anon, authenticated\), or, if every row is public, add it to CONFIG\.publicTables/)
const entry = (until) => ({ 'public.countries': { reason: 'reference data, no user rows', until } })
const inForce = out.report({ publicTables: entry('2027-03-31') })
assert.deepEqual(codes(inForce.findings), ['RLS-OFF'])
assert.deepEqual(inForce.waived, [
{ list: 'publicTables', key: 'public.countries', code: 'RLS-OFF', until: '2027-03-31', reason: 'reference data, no user rows' },
])
const expired = out.report({ publicTables: entry('2026-09-01') })
assert.match(expired.findings[0].message, /\(waiver expired 2026-09-01\)$/)
const stale = out.report({ publicTables: { 'public.gone': { reason: 'r', until: '2027-03-31' } } })
assert.ok(stale.notes.some((n) => n.message === 'stale waiver: public.gone in CONFIG.publicTables. Delete it.'))
})
// PGlite fidelity: what the probes rely on, on the pinned version.
test('fidelity: rollback to savepoint returns the role and settings; set local role postgres works inside one', async () => {
const { db } = await build(G, {}, { keep: true })
try {
await db.exec('begin')
await db.query('insert into auth.users (id, email) values ($1, $2)', [USERS.b, 'b@ntent.test'])
await db.query("insert into public.customers (user_id, name, email) values ($1, 'b', 'b')", [USERS.b])
await db.exec('savepoint probe')
await db.exec('set local role authenticated')
await db.query("select set_config('request.jwt.claims', $1, true)", [JSON.stringify({ sub: USERS.a, role: 'authenticated' })])
assert.equal((await db.query('select count(*)::int as n from public.customers')).rows[0].n, 0, 'A sees none of B')
await db.exec('set local role postgres')
assert.equal((await db.query('select current_user as u')).rows[0].u, 'postgres')
assert.equal((await db.query('select count(*)::int as n from public.customers')).rows[0].n, 1, 'postgres sees B')
await db.exec('rollback to savepoint probe; release savepoint probe')
assert.equal((await db.query('select current_user as u')).rows[0].u, 'postgres')
assert.equal((await db.query("select coalesce(current_setting('request.jwt.claims', true), '') as c")).rows[0].c, '')
await db.exec('rollback')
} finally {
await db.close()
}
})
test('fidelity: the policy is checked before constraints (D22); a domain check fires before both', async () => {
const { db } = await build(
sql(`create domain public.positive as int check (value > 0);
create table public.t (id int primary key, owner uuid not null, n public.positive);
alter table public.t enable row level security;
create policy "mine" on public.t for insert to authenticated with check (owner = (select auth.uid()));`),
{},
{ keep: true },
)
const tryInsert = async (values) => {
await db.exec('begin; set local role authenticated')
await db.query("select set_config('request.jwt.claims', $1, true)", [JSON.stringify({ sub: USERS.a })])
try {
await db.query(`insert into public.t (id, owner, n) values ${values}`)
return null
} catch (e) {
return e
} finally {
await db.exec('rollback')
}
}
try {
// B's owner and a duplicate key in one statement: the policy speaks first.
const both = await tryInsert(`(1, '${USERS.b}', 1), (1, '${USERS.b}', 1)`)
assert.equal(both.code, '42501')
assert.match(both.message, /new row violates row-level security policy for table "t"/)
// A's own row with a duplicate key: past the policy, so the constraint.
const dup = await tryInsert(`(1, '${USERS.a}', 1), (1, '${USERS.a}', 1)`)
assert.deepEqual([dup.code, dup.table, dup.dataType], ['23505', 't', undefined])
// A domain check fires while the value is read, before the policy.
const domain = await tryInsert(`(1, '${USERS.b}', -1)`)
assert.deepEqual([domain.code, domain.dataType, domain.table], ['23514', 'positive', undefined])
} finally {
await db.close()
}
})
// ------------------------------------------------------------ allowlists ---
test('allowlists: an entry with no until refuses to run', async () => {
const dir = repo()
const r = await runIn(dir, ['--check', '--schema'], {
config: configWith({ publicTables: { 'public.countries': { reason: 'reference data, no user rows' } } }),
})
assert.equal(r.code, 2)
assert.match(r.out, /CONFIG-INVALID\s+publicTables\['public\.countries'\]/)
assert.match(r.out, /has no until date/)
})
test('allowlists: in force is a waiver line, expired brings the finding back, unmatched is stale', async () => {
const { snapshot, config } = await build({ [MIGRATION]: RED_SQL })
const waive = (until) => ({
...config,
schemaWaivers: {
'public.invoices.due_date': { reason: 'backfill lands next sprint', until },
'public.invoices.gone': { reason: 'nothing matches this', until: '2027-01-01' },
},
})
const gates = { schema: true, access: false }
const inForce = evaluate({ snapshot, contract: contract(), config: waive('2026-12-31'), gates, today: TODAY })
assert.deepEqual(codes(inForce.findings), ['TYPE-CONTRADICTS'])
assert.deepEqual(inForce.waived.map((w) => [w.key, w.code, w.until]), [['public.invoices.due_date', 'REQUIRED-BUT-NULLABLE', '2026-12-31']])
assert.ok(inForce.notes.some((n) => n.message === 'stale waiver: public.invoices.gone in CONFIG.schemaWaivers. Delete it.'))
const expired = evaluate({ snapshot, contract: contract(), config: waive('2026-09-01'), gates, today: TODAY })
const back = expired.findings.find((f) => f.code === 'REQUIRED-BUT-NULLABLE')
assert.match(back.message, /\(waiver expired 2026-09-01\)$/)
assert.deepEqual(expired.waived, [])
})
// ------------------------------------------------------------------- run ---
/** A temp repo with the green fixture and the contract, plus any overrides. */
function repo({ sql = GREEN_SQL, contractText = FIXTURE_CONTRACT } = {}) {
const dir = tmp('backend-')
if (sql !== null) {
mkdirSync(join(dir, 'supabase', 'migrations'), { recursive: true })
writeFileSync(join(dir, MIGRATION), sql)
}
if (contractText !== null) writeFileSync(join(dir, 'contract.json'), contractText)
return dir
}
// The environment a test hands run(): the real one, minus what would change a command source's behaviour.
const cleanEnv = (extra = {}) => {
const env = { ...process.env, ...extra }
for (const key of ['PRISMA_SCHEMA_ENGINE_BINARY', 'NTENT_REPO_ROOT', 'DATABASE_URL', 'DIRECT_URL']) if (!(key in extra)) delete env[key]
return env
}
async function runIn(dir, argv, { config = configWith(), today = TODAY, env = cleanEnv() } = {}) {
const lines = []
const errs = []
const code = await run({ argv, cwd: dir, config, today, env, log: (s) => lines.push(s), err: (s) => errs.push(s) })
return { code, out: lines.join('\n'), err: errs.join('\n') }
}
const json = (r) => JSON.parse(r.out)
test('cache: cold, then full with 2 deny tests, a journeys edit is still full, an access edit boots from the datadir', async () => {
const dir = repo()
const first = json(await runIn(dir, ['--json']))
assert.deepEqual([first.cached, first.booted], ['none', true])
const second = json(await runIn(dir, ['--json']))
assert.deepEqual([second.cached, second.booted], ['full', false])
const summary = await runIn(dir, ['--check'])
assert.equal(summary.code, 0, summary.out)
assert.equal(summary.out, 'backend: 1 migration, 3 tables, 2 deny tests, 0 findings (cached)')
const app = await runIn(dir, ['--check', '--access'], { config: configWith({ enforcement: 'app' }) })
assert.equal(app.code, 0, app.out)
const c = contract()
c.journeys[0].state = 'built'
writeFileSync(join(dir, 'contract.json'), JSON.stringify(c))
assert.equal(json(await runIn(dir, ['--json'])).cached, 'full', 'journeys do not touch the database')
c.access.matrix.push({ actor: 'customer', entity: 'invoice', can: ['read'], scope: 'own' })
writeFileSync(join(dir, 'contract.json'), JSON.stringify(c))
const fourth = json(await runIn(dir, ['--json']))
assert.deepEqual([fourth.cached, fourth.booted], ['datadir', true])
})
test('run(): green --schema exits 0 and is cached; nullable due_date and invoices without RLS exit 1', async () => {
const dir = repo()
const green = await runIn(dir, ['--check', '--schema'])
assert.equal(green.code, 0, green.out)
assert.match(green.out, /^backend: 1 migration, 3 tables, 0 findings$/)
const again = json(await runIn(dir, ['--check', '--schema', '--json']))
assert.deepEqual([again.exit, again.cached, again.booted], [0, 'full', false])
// One red repo for both gates: --schema reads only the schema, and the
// full run adds the access gate from the cached datadir.
const red = repo({ sql: GREEN_SQL.replace('due_date date not null', 'due_date date').replace('alter table public.invoices enable row level security;\n', '') })
const r = await runIn(red, ['--check', '--schema'])
assert.equal(r.code, 1)
assert.match(r.out, /REQUIRED-BUT-NULLABLE\s+public\.invoices\.due_date/)
assert.doesNotMatch(r.out, /RLS-OFF|LEAK-/)
const both = await runIn(red, ['--check'])
assert.equal(both.code, 1)
assert.match(both.out, /REQUIRED-BUT-NULLABLE\s+public\.invoices\.due_date/)
assert.match(both.out, /POLICY-BUT-RLS-OFF\s+public\.invoices/)
assert.match(both.out, /public\.invoices\s+LEAK-READ, LEAK-UPDATE, LEAK-DELETE, LEAK-INSERT as customer/, 'the probes sit under the finding that causes them')
})
test('a waiver edit never forces a boot', async () => {
const dir = repo()
await runIn(dir, ['--schema', '--json'])
const waived = configWith({ schemaWaivers: { 'public.invoices.gone': { reason: 'testing the key', until: '2027-01-01' } } })
const r = json(await runIn(dir, ['--schema', '--json'], { config: waived }))
assert.equal(r.cached, 'full')
})
test('a load failure through run() is exit 2, and nothing is cached', async () => {
const dir = repo({ sql: 'create table public.a (id int);\ncreat table public.b ();' })
const r = await runIn(dir, ['--check', '--schema'])
assert.equal(r.code, 2)
assert.match(r.out, new RegExp(`LOAD-FAILED\\s+${MIGRATION.replace(/\./g, '\\.')}:2`))
assert.equal(json(await runIn(dir, ['--schema', '--json'])).cached, 'none')
})
test('timeout: a migration that sleeps is exit 2 naming the phase, well inside the limit', async () => {
const dir = repo({ sql: 'select pg_sleep(30);' })
const started = Date.now()
const timeoutMs = 5000
const r = await runIn(dir, ['--check', '--schema', '--no-cache'], { config: configWith({ timeoutMs }) })
assert.equal(r.code, 2)
assert.match(r.out, /TIMEOUT\s+migrate/)
// The run includes booting PGlite, which is slow on a loaded machine, so the
// ceiling is the limit plus a wide margin for that boot, not a number of its
// own. It still has to sit under the 30 s sleep, or it proves nothing: a run
// that waited the sleep out is the failure this is looking for.
const ceiling = timeoutMs + 20000
assert.ok(Date.now() - started < ceiling, `took ${Date.now() - started} ms, ceiling ${ceiling} ms`)
})
test('empty: no migrations is exit 2 NO-INPUT; with --allow-empty, exit 0 and the note', async () => {
const dir = repo({ sql: null })
const r = await runIn(dir, ['--check'])
assert.equal(r.code, 2)
assert.match(r.out, /NO-INPUT/)
assert.match(r.out, /No migration files at supabase\/migrations\/\*\.sql/)
const allowed = await runIn(dir, ['--allow-empty', '--json'])
assert.equal(allowed.code, 0)
assert.deepEqual(codes(json(allowed).notes), ['NO-INPUT'])
})
test('contract shape: a table that is not a name is exit 2 before anything boots', async () => {
const c = contract()
c.entities[0].table = 42
const r = await runIn(repo({ contractText: JSON.stringify(c) }), ['--check', '--json'])
assert.equal(r.code, 2)
const out = json(r)
assert.equal(out.booted, false)
assert.deepEqual(out.untested.map((u) => [u.code, u.object]), [['CONTRACT-SHAPE', 'entities.invoice.table']])
assert.equal(out.untested[0].message, 'must be a table name, "table" or "schema.table"')
})
test('contract shape: a ref to no entity, and ref on a non-id field', async () => {
const c = contract()
c.entities[0].fields[2].ref = 'custmer'
c.entities[0].fields[1].ref = 'customer'
const out = json(await runIn(repo({ contractText: JSON.stringify(c) }), ['--json']))
assert.deepEqual(
out.untested.map((u) => [u.object, u.message]),
[
['entities.invoice.fields.total_amount.ref', 'ref is only valid on an id field'],
['entities.invoice.fields.customer_id.ref', '"custmer" is not an entity'],
],
)
})
test('an unknown argument refuses, so a typo of --check never runs as a report', async () => {
const r = await runIn(repo(), ['--chek'])
assert.equal(r.code, 2)
assert.match(r.out, /USAGE\s+--chek/)
})
// ------------------------------------------------------------------- CLI ---
test('print-config: the lines the hook reads, with no PGlite', () => {
const dir = repo()
const out = execFileSync(process.execPath, [CHECK, '--print-config'], { cwd: dir, encoding: 'utf8' })
const lines = out.trim().split('\n')
assert.match(lines[0], /^platform\t\w+$/)
assert.ok(lines.some((l) => /^watch\t\S+$/.test(l)), out)
assert.ok(lines.every((l) => /^(platform|platformWhy|clientAccess|clientAccessWhy|enforcement|enforcementHint|loadGate|watch|source|candidate|inputCount)\t.+$/.test(l)), out)
assert.match(lines.at(-1), /^inputCount\t\d+$/)
})
test('a snapshot longer than a pipe buffer arrives whole from the CLI', () => {
// process.exit straight after a long write cut the output at 64 KB on a pipe,
// which check-migrations read as a snapshot that is not JSON.
let sql = ''
for (let i = 0; i < 300; i++) sql += `create table t${i} (id integer primary key, a text not null, b integer, c real, d text);\n`
const dir = files({ 'db/schema.sql': sql })
mkdirSync(join(dir, 'scripts'))
let text = readFileSync(CHECK, 'utf8')
const re = /^ {2}sources: \[.*\],$/m
assert.match(text, re, 'CONFIG.sources is on one line, so a test can replace it')
text = text.replace(re, ` sources: ${JSON.stringify(sqliteSources)},`)
writeFileSync(join(dir, 'scripts', 'check-backend.mjs'), text)
const out = execFileSync(process.execPath, ['scripts/check-backend.mjs', '--print-snapshot', '--no-cache'], { cwd: dir, encoding: 'utf8', maxBuffer: 64 * 1024 * 1024 })
assert.ok(out.length > 65536, `the snapshot is ${out.length} bytes, so it is long enough to show the cut`)
assert.equal(Object.keys(JSON.parse(out).tables).length, 300)
})
test('PGLITE-MISSING: the checker copied where no node_modules can reach it', () => {
const dir = repo()
mkdirSync(join(dir, 'scripts'))
// A copy with the shipped platform and sources, so a customised CONFIG (a
// command source finds no input before it ever needs PGlite) changes nothing.
let text = readFileSync(CHECK, 'utf8')
for (const [key, re] of [['platform', /^ {2}platform: .*,$/m], ['sources', /^ {2}sources: \[.*\],$/m]]) {
assert.match(text, re, `CONFIG.${key} is on one line, so a test can replace it`)
text = text.replace(re, ` ${key}: ${JSON.stringify(DEFAULT_CONFIG[key])},`)
}
writeFileSync(join(dir, 'scripts', 'check-backend.mjs'), text)
let status = 0
let out = ''
try {
execFileSync(process.execPath, ['scripts/check-backend.mjs', '--check', '--schema'], { cwd: dir, encoding: 'utf8' })
} catch (e) {
status = e.status
out = e.stdout
}
assert.equal(status, 2)
assert.match(out, /PGLITE-MISSING/)
assert.match(out, /backend-deps/)
})
// ------------------------------------------------------ 1b: SQLite loader ---
/** A SQLite snapshot built in this process. The error a bad file raises is the test's to catch. */
async function buildSqlite(sql, overrides = {}) {
const { DatabaseSync } = await loadSqlite()
const config = configWith(overrides)
const db = new DatabaseSync(':memory:')
try {
const prepared = prepareSqlite([{ path: 'schema.sql', text: sql, sections: null }])
loadSqliteSchema(db, prepared, config)
const found = introspectSqlite(db)
return { snapshot: makeSnapshot({ found, prepared, config, inputsHash: 'test', pgliteVersion: 'test', dialect: 'sqlite' }), config }
} finally {
db.close()
}
}
const SQLITE_GREEN = `create table customers (id integer primary key, name text);
create table invoices (
id integer primary key autoincrement,
number text not null unique,
customer_id integer not null references customers,
total integer not null,
is_active boolean,
created datetime,
due text not null,
ext_id text,
email text,
loose,
gen integer generated always as (total * 2) stored
);
create unique index invoices_email on invoices(email);
create unique index invoices_lower_email on invoices(lower(email));
create view open_invoices as select * from invoices where is_active = 0;
`
const sqliteContract = (fields) => ({ entities: [{ id: 'invoice', table: 'invoices', fields }] })
test('sqlite: introspection reads columns, affinity, keys, foreign keys, unique sets and views', async () => {
const { snapshot } = await buildSqlite(SQLITE_GREEN)
assert.deepEqual(Object.keys(snapshot.tables), ['public.customers', 'public.invoices', 'public.open_invoices'])
assert.deepEqual([snapshot.loader.name, snapshot.source.platform, snapshot.loader.shim], ['node:sqlite', 'sqlite', null])
const t = snapshot.tables['public.invoices']
assert.deepEqual(t.primaryKey, ['id'])
assert.equal(t.columns.id.nullable, false, 'INTEGER PRIMARY KEY never says NOT NULL and is never null')
assert.deepEqual(
Object.fromEntries(Object.entries(t.columns).map(([k, c]) => [k, `${c.affinity}/${c.family}/${c.nullable ? 'null' : 'req'}`])),
{
id: 'INTEGER/num/req', number: 'TEXT/text/req', customer_id: 'INTEGER/num/req', total: 'INTEGER/num/req',
is_active: 'NUMERIC/num/null', created: 'NUMERIC/num/null', due: 'TEXT/text/req', ext_id: 'TEXT/text/null',
email: 'TEXT/text/null', loose: 'NONE/other/null', gen: 'INTEGER/num/null',
},
)
assert.equal(t.columns.gen.generated, 'generated')
assert.deepEqual(t.foreignKeys.map((f) => [f.columns, f.references, f.referencedColumns]), [[['customer_id'], 'public.customers', ['id']]])
assert.deepEqual(t.unique.map((u) => u.join()).sort(), ['email', 'number'], 'an expression index says nothing about a column')
assert.deepEqual([t.rls, t.policies, t.grants, t.kind], [null, [], {}, 'table'])
assert.deepEqual([snapshot.tables['public.open_invoices'].kind, snapshot.tables['public.open_invoices'].securityInvoker], ['view', false])
assert.equal(snapshot.tables['public.sqlite_sequence'], undefined)
})
test('sqlite: the schema gate, green and red, with the looser type table', async () => {
const green = await buildSqlite(SQLITE_GREEN)
const fields = [
{ name: 'number', type: 'text', required: true },
{ name: 'total', type: 'money', required: true },
{ name: 'is_active', type: 'boolean' }, // INTEGER-ish holds it; Postgres would call a num column a contradiction
{ name: 'created', type: 'datetime' },
{ name: 'due', type: 'date', required: true },
{ name: 'ext_id', type: 'id' },
{ name: 'loose', type: 'number' },
]
assert.deepEqual(schemaGate(green.snapshot, sqliteContract(fields), green.config).findings, [])
const pg = { ...green.snapshot, loader: { ...green.snapshot.loader, name: 'pglite' } }
assert.deepEqual(codes(schemaGate(pg, sqliteContract(fields), green.config).findings), ['TYPE-CONTRADICTS', 'TYPE-CONTRADICTS'], 'the same snapshot read the Postgres way')
const red = await buildSqlite(`create table invoices (id integer primary key, paid real, amount blob, due text, number text not null, ts blob);`)
const g = schemaGate(
red.snapshot,
sqliteContract([
{ name: 'paid', type: 'boolean' },
{ name: 'amount', type: 'money' },
{ name: 'due', type: 'date', required: true },
{ name: 'ts', type: 'datetime' },
]),
red.config,
)
assert.deepEqual(g.findings.map((f) => [f.code, f.object]), [
['TYPE-CONTRADICTS', 'public.invoices.paid'],
['TYPE-CONTRADICTS', 'public.invoices.amount'],
['REQUIRED-BUT-NULLABLE', 'public.invoices.due'],
])
assert.match(g.findings[0].message, /paid is boolean in the model; the column is REAL/)
})
test('sqlite: the access gate says it is not checked, and is not a finding', async () => {
const { snapshot, config } = await buildSqlite(SQLITE_GREEN)
const out = evaluate({ snapshot, contract: sqliteContract([{ name: 'number', type: 'text' }]), config, today: TODAY })
assert.deepEqual([out.findings, out.untested], [[], []])
const note = out.notes.find((n) => n.code === 'ACCESS-NOT-CHECKED')
assert.equal(note.message, 'not checked: SQLite has no row security. Access lives in app code (roadmap R1).')
})
test('sqlite: a file that does not load is LOAD-FAILED naming it; node older than 22.13 is SQLITE-UNAVAILABLE', async () => {
await assert.rejects(buildSqlite('create table a (id integer);\ncreat table b (id integer);'), (e) => {
assert.ok(e instanceof CannotCheck)
assert.deepEqual([e.code, e.object], ['LOAD-FAILED', 'schema.sql'])
assert.match(e.message, /syntax error/)
return true
})
for (const nodeVersion of ['22.12.0', '20.18.1']) {
await assert.rejects(loadSqlite({ nodeVersion }), (e) => e.code === 'SQLITE-UNAVAILABLE' && /22\.13 or later; this is/.test(e.fix))
}
const up = prepareSqlite([{ path: 'x.sql', text: '-- migrate:up\ncreate table a (id integer);\n-- migrate:down\ndrop table a;', sections: 'dbmate' }])
assert.doesNotMatch(up.files[0].text, /drop table/)
})
/** A temp repo of { path: text }. */
function files(map) {
const dir = tmp('backend-src-')
for (const [rel, text] of Object.entries(map)) {
mkdirSync(dirname(join(dir, rel)), { recursive: true })
writeFileSync(join(dir, rel), text)
}
return dir
}
const sqliteSources = [{ kind: 'sql', paths: ['db/*.sql'], dialect: 'sqlite' }]
test('sqlite through run(): SQL files load into node:sqlite; red is exit 1, green exit 0, and it is cached', async () => {
const contractText = JSON.stringify(sqliteContract([{ name: 'number', type: 'text', required: true }, { name: 'due', type: 'date', required: true }]))
const config = configWith({ sources: sqliteSources })
const green = files({ 'db/schema.sql': SQLITE_GREEN, 'contract.json': contractText })
const ok = await runIn(green, ['--check'], { config })
assert.equal(ok.code, 0, ok.out)
assert.equal(ok.out, 'backend: 1 migration, 3 tables, access NOT checked: SQLite has no row security, 0 findings')
const again = json(await runIn(green, ['--json'], { config }))
assert.deepEqual([again.exit, again.cached, again.booted, again.platform], [0, 'full', false, 'sqlite'])
assert.ok(again.notes.some((n) => n.code === 'ACCESS-NOT-CHECKED'))
const red = files({ 'db/schema.sql': SQLITE_GREEN.replace('due text not null', 'due text'), 'contract.json': contractText })
const bad = await runIn(red, ['--check'], { config })
assert.equal(bad.code, 1)
assert.match(bad.out, /REQUIRED-BUT-NULLABLE\s+invoices\.due/)
assert.doesNotMatch(bad.out, /public\./, 'SQLite has no schemas (R21)')
const broken = await runIn(files({ 'db/schema.sql': 'creat table a (id);' }), ['--check'], { config })
assert.equal(broken.code, 2)
assert.match(broken.out, /LOAD-FAILED\s+db\/schema\.sql/)
})
test('cache: a file another run deleted mid-prune does not stop the prune, and a bad cache never changes the result', async () => {
// A dangling link is what a file deleted between the listing and the stat
// looks like: readdir names it, statSync throws ENOENT.
const cache = tmp('backend-cache-')
for (let i = 0; i < 8; i++) {
const f = join(cache, `k${i}.snapshot.json`)
writeFileSync(f, '{}')
utimesSync(f, new Date(2026, 0, 1, 0, i), new Date(2026, 0, 1, 0, i))
}
symlinkSync(join(cache, 'gone'), join(cache, 'vanished.snapshot.json'))
assert.doesNotThrow(() => prune(cache))
assert.equal(existsSync(join(cache, 'k7.snapshot.json')), true, 'the newest are kept')
assert.equal(existsSync(join(cache, 'k0.snapshot.json')), false, 'the oldest are still pruned')
// Through run(): the same race beside a real cache write exits as it would without it.
const contractText = JSON.stringify(sqliteContract([{ name: 'number', type: 'text', required: true }, { name: 'due', type: 'date', required: true }]))
const config = configWith({ sources: sqliteSources })
const dir = files({ 'db/schema.sql': SQLITE_GREEN, 'contract.json': contractText })
const dangling = join(dir, config.cacheDir)
mkdirSync(dangling, { recursive: true })
symlinkSync(join(dangling, 'gone'), join(dangling, 'vanished.snapshot.json'))
const r = await runIn(dir, ['--check'], { config })
assert.equal(r.code, 0, r.out)
assert.equal(r.out, 'backend: 1 migration, 3 tables, access NOT checked: SQLite has no row security, 0 findings')
// A cache directory that cannot be written is a slower run, not a failed one.
const blocked = files({ 'db/schema.sql': SQLITE_GREEN, 'contract.json': contractText, 'node_modules/.cache': 'a file where a directory should be' })
const slow = await runIn(blocked, ['--check'], { config })
assert.equal(slow.code, 0, slow.out)
})
test('sources of two dialects are CONFIG-INVALID: one run builds one database', async () => {
const dir = files({ 'db/a.sql': 'create table a (id integer);', 'supabase/migrations/b.sql': 'create table public.b (id int);' })
const config = configWith({ sources: [sqliteSources[0], { kind: 'sql', paths: ['supabase/migrations/*.sql'] }] })
const r = await runIn(dir, ['--check'], { config })
assert.equal(r.code, 2)
assert.match(r.out, /CONFIG-INVALID\s+sources/)
assert.match(r.out, /mix (sqlite and postgres|postgres and sqlite)/)
})
// -------------------------------------------------- 1b: command sources ---
// What the real tools printed for the fixtures below: prisma 7.10.0 and
// drizzle-kit 0.31.11, captured once. The tests run the presets against stubs
// that print these, so nothing here needs the tools or a network.
const PRISMA_PG_SQL = `-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "public";
-- CreateTable
CREATE TABLE "Invoice" (
"id" UUID NOT NULL,
"number" TEXT NOT NULL,
"total" DECIMAL(65,30) NOT NULL,
"due" TIMESTAMP(3),
CONSTRAINT "Invoice_pkey" PRIMARY KEY ("id")
);
-- CreateIndex
CREATE UNIQUE INDEX "Invoice_number_key" ON "Invoice"("number");
`
const PRISMA_SQLITE_SQL = `-- CreateTable
CREATE TABLE "Invoice" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"number" TEXT NOT NULL,
"paid" BOOLEAN NOT NULL DEFAULT false,
"total" DECIMAL NOT NULL,
"due" DATETIME,
"customerId" INTEGER NOT NULL,
CONSTRAINT "Invoice_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "Customer" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);
-- CreateTable
CREATE TABLE "Customer" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL
);
-- CreateIndex
CREATE UNIQUE INDEX "Invoice_number_key" ON "Invoice"("number");
`
const DRIZZLE_SQLITE_SQL = [
'CREATE TABLE `invoices` (',
'\t`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,',
'\t`number` text NOT NULL,',
'\t`paid` integer',
');',
'',
].join('\n')
const PRISMA_SCHEMA = (provider) => `datasource db {
provider = "${provider}"
}
model Invoice {
id String @id
number String @unique
}
`
const PRISMA_CONFIG = `import { defineConfig } from 'prisma/config'
export default defineConfig({ schema: 'prisma/schema.prisma', datasource: { url: process.env.DATABASE_URL } })
`
const DRIZZLE_CONFIG = (dialect, schema = `'./src/db/schema.ts'`) => `import { defineConfig } from 'drizzle-kit'
export default defineConfig({ dialect: '${dialect}', schema: ${schema}, out: './drizzle' })
`
// Every stub is a symlink to one script, which reads its behaviour from a
// JSON file beside the link. A new executable costs a macOS security scan of
// about 200 ms the first time it runs; a link to one already run costs none.
const STUB_SOURCE = `#!/usr/bin/env node
const fs = require('node:fs')
const b = JSON.parse(fs.readFileSync(process.argv[1] + '.json', 'utf8'))
if (b.log) fs.appendFileSync(b.log, JSON.stringify(process.argv.slice(2)) + '\\n')
if (b.stderr) process.stderr.write(b.stderr)
if (b.stdout) process.stdout.write(b.stdout)
if (b.sleep) setTimeout(() => {}, b.sleep)
else process.exit(b.exit ?? 0)
`
let sharedStub = null
const sharedStubFile = () => {
if (!sharedStub) {
sharedStub = join(tmp('backend-stub-'), 'stub.cjs')
writeFileSync(sharedStub, STUB_SOURCE)
chmodSync(sharedStub, 0o755)
}
return sharedStub
}
/**
* An executable in a temp repo that does what `behaviour` says: { stdout,
* stderr, exit, sleep (ms), log (a file each argv is appended to) }. Calling it
* again with the same path changes what it does.
*/
function stub(dir, rel, behaviour = {}) {
const file = join(dir, rel)
mkdirSync(dirname(file), { recursive: true })
rmSync(file, { force: true })
symlinkSync(sharedStubFile(), file)
writeFileSync(`${file}.json`, JSON.stringify(behaviour))
return file
}
/** A stub that logs its argv as one JSON line per call, then prints `sql`. */
const printing = (dir, sql) => ({ log: join(dir, 'calls.log'), stdout: sql })
const calls = (dir) => (existsSync(join(dir, 'calls.log')) ? readFileSync(join(dir, 'calls.log'), 'utf8').trim().split('\n').map((l) => JSON.parse(l)) : [])
const genericSource = { kind: 'command', run: ['bin/print-sql'], dialect: 'sqlite', watch: ['db/'] }
const plan = (source, dir, extra = {}) => planCommand(source, 0, { root: dir, env: cleanEnv(), ...extra })
test('config: command sources name a preset or a run, and what a run needs', () => {
const problems = (source) => validateConfig(configWith({ sources: [source] })).map((p) => `${p.object}: ${p.message}`)
assert.deepEqual(problems({ kind: 'command', preset: 'prisma' }), [])
assert.deepEqual(problems({ kind: 'command', preset: 'alembic', dialect: 'postgres' }), [])
assert.deepEqual(problems(genericSource), [])
assert.deepEqual(problems({ kind: 'command' }), ['sources[0]: has neither preset nor run'])
assert.deepEqual(problems({ kind: 'command', preset: 'prisma', run: ['x'], dialect: 'sqlite', watch: ['a'] }), ['sources[0]: has both preset and run'])
assert.deepEqual(problems({ kind: 'command', preset: 'nope' }), ['sources[0].preset: is "nope"'])
assert.deepEqual(problems({ kind: 'command', preset: 'alembic' }), ['sources[0].dialect: is required for alembic'])
assert.deepEqual(problems({ kind: 'command', run: ['x'] }), ['sources[0].dialect: is required with run', 'sources[0].watch: is required with run'])
assert.deepEqual(problems({ kind: 'command', preset: 'prisma', cwd: 'here' }), ['sources[0].cwd: is "here"'])
assert.match(problems({ kind: 'sql', paths: ['a'], dialect: 'mysql' })[0], /"mysql", which this version does not load \(roadmap R3, MySQL\)/)
assert.deepEqual(problems({ kind: 'cmd' }), ['sources[0].kind: is "cmd"'])
assert.deepEqual(validateConfig(configWith({ commandTimeoutMs: 0 })).map((p) => p.object), ['commandTimeoutMs'])
})
test('prisma preset: the argv, the env, the dialect from the datasource, the watch list', () => {
const dir = files({
'prisma/schema.prisma': PRISMA_SCHEMA('postgresql'),
'prisma.config.ts': PRISMA_CONFIG,
'node_modules/prisma/package.json': '{ "version": "7.10.0" }',
})
const unit = plan({ kind: 'command', preset: 'prisma' }, dir)
assert.equal(unit.file, join(dir, 'node_modules/.bin/prisma'))
assert.deepEqual(unit.args, ['migrate', 'diff', '--from-empty', '--to-schema', 'prisma/schema.prisma', '--script'])
assert.deepEqual([unit.dialect, unit.label, unit.version, unit.mode], ['postgres', 'command:prisma', '7.10.0', 'snapshot'])
assert.equal(unit.env.DATABASE_URL, 'postgresql://ntent:ntent@127.0.0.1:1/ntent')
assert.equal(unit.env.DIRECT_URL, 'postgresql://ntent:ntent@127.0.0.1:1/ntent')
assert.deepEqual(unit.watch.slice(0, 2), ['prisma/schema.prisma', 'prisma.config.ts'])
// The caller's own variables win; Prisma 6 spells the flag differently.
const six = files({ 'prisma/schema.prisma': PRISMA_SCHEMA('postgresql'), 'node_modules/prisma/package.json': '{ "version": "6.4.1" }' })
const old = planCommand({ kind: 'command', preset: 'prisma' }, 0, { root: six, env: { DATABASE_URL: 'postgresql://mine/db' } })
assert.equal(old.args[3], '--to-schema-datamodel')
assert.deepEqual([old.env.DATABASE_URL, old.env.DIRECT_URL], ['postgresql://mine/db', 'postgresql://ntent:ntent@127.0.0.1:1/ntent'])
const lite = plan({ kind: 'command', preset: 'prisma' }, files({ 'prisma/schema.prisma': PRISMA_SCHEMA('sqlite') }))
assert.deepEqual([lite.dialect, lite.env.DATABASE_URL], ['sqlite', 'file:./ntent.db'])
// A schema folder is read whole, and watched as a folder.
const folder = plan({ kind: 'command', preset: 'prisma', schema: 'prisma/schema' }, files({ 'prisma/schema/base.prisma': PRISMA_SCHEMA('sqlite'), 'prisma/schema/models.prisma': 'model A {\n id Int @id\n}\n' }))
assert.deepEqual([folder.dialect, folder.watch[0]], ['sqlite', 'prisma/schema/'])
assert.equal(plan({ kind: 'command', preset: 'prisma' }, files({})), null, 'no schema is no input')
})
test('prisma preset: MySQL and MongoDB name their roadmap item, an unreadable provider says so, dialect overrides', () => {
const refuses = (provider) => {
try {
plan({ kind: 'command', preset: 'prisma' }, files({ 'prisma/schema.prisma': PRISMA_SCHEMA(provider) }))
} catch (e) {
return e.toJSON()
}
return assert.fail(`${provider} loaded`)
}
assert.deepEqual([refuses('mysql').code, refuses('mysql').message], ['DIALECT-UNSUPPORTED', 'the datasource provider in prisma/schema.prisma is mysql, which this version does not load (roadmap R3, MySQL).'])
assert.match(refuses('mongodb').message, /roadmap R4, MongoDB/)
assert.match(refuses('sqlserver').message, /no roadmap item yet/)
assert.equal(refuses('cockroachdb').code, 'DIALECT-UNSUPPORTED')
assert.equal(refuses('yugabyte').code, 'DIALECT-UNKNOWN')
assert.equal(plan({ kind: 'command', preset: 'prisma', dialect: 'postgres' }, files({ 'prisma/schema.prisma': PRISMA_SCHEMA('mysql') })).dialect, 'postgres')
})
test('drizzle preset: the argv, the dialect and schema paths read from the config with a regex', () => {
const dir = files({ 'drizzle.config.ts': DRIZZLE_CONFIG('postgresql'), 'src/db/schema.ts': '', 'node_modules/drizzle-kit/package.json': '{ "version": "0.31.11" }' })
const unit = plan({ kind: 'command', preset: 'drizzle' }, dir)
assert.equal(unit.file, join(dir, 'node_modules/.bin/drizzle-kit'))
assert.deepEqual(unit.args, ['export', '--config', 'drizzle.config.ts'])
assert.deepEqual([unit.dialect, unit.version, unit.watch], ['postgres', '0.31.11', ['drizzle.config.ts', 'src/db/schema.ts']])
const turso = plan({ kind: 'command', preset: 'drizzle' }, files({ 'drizzle.config.ts': DRIZZLE_CONFIG('turso', `['./src/a.ts', './src/b/']`), 'src/a.ts': '', 'src/b/x.ts': '' }))
assert.deepEqual([turso.dialect, turso.watch], ['sqlite', ['drizzle.config.ts', 'src/a.ts', 'src/b/']])
const mysql = () => plan({ kind: 'command', preset: 'drizzle' }, files({ 'drizzle.config.ts': DRIZZLE_CONFIG('mysql') }))
assert.throws(mysql, (e) => e.code === 'DIALECT-UNSUPPORTED' && /roadmap R3/.test(e.message))
// A schema path the regex cannot read makes CONFIG watch required.
const dynamic = { 'drizzle.config.ts': "import { defineConfig } from 'drizzle-kit'\nexport default defineConfig({ dialect: 'sqlite', schema: SCHEMA })\n", 'src/db/schema.ts': '' }
assert.throws(() => plan({ kind: 'command', preset: 'drizzle' }, files(dynamic)), (e) => e.code === 'CONFIG-INVALID' && e.object === 'sources[0].watch')
assert.deepEqual(plan({ kind: 'command', preset: 'drizzle', watch: ['src/db/'] }, files(dynamic)).watch, ['drizzle.config.ts', 'src/db/'])
assert.equal(plan({ kind: 'command', preset: 'drizzle' }, files({})), null)
})
test('alembic preset: runs from the repo root with .venv/bin first on PATH, and watches script_location', () => {
const repoRoot = files({
'alembic.ini': '[alembic]\nscript_location = %(here)s/db/migrations\n',
'db/migrations/versions/0001.py': '',
'.venv/bin/alembic': '',
})
const snapshotDir = files({})
const unit = planCommand({ kind: 'command', preset: 'alembic', dialect: 'postgres' }, 0, { root: snapshotDir, repoRoot, env: cleanEnv() })
assert.deepEqual([unit.base, unit.mode, unit.file, unit.args], [repoRoot, 'root', 'alembic', ['upgrade', 'head', '--sql']])
assert.ok(unit.env.PATH.startsWith(`${join(repoRoot, '.venv', 'bin')}:`), unit.env.PATH)
assert.deepEqual(unit.watch, ['db/migrations/', 'alembic.ini'])
assert.equal(planCommand({ kind: 'command', preset: 'alembic', dialect: 'postgres', cwd: 'snapshot' }, 0, { root: snapshotDir, repoRoot, env: cleanEnv() }), null, 'cwd snapshot reads the snapshot, which has no ini')
})
test('a command that prints SQL is read; one that prints nothing, only comments, fails, is missing or hangs is exit 2', () => {
const dir = files({ 'db/schema.ts': 'x' })
const unit = () => plan(genericSource, dir)
stub(dir, 'bin/print-sql', printing(dir, 'create table a (id integer);'))
assert.equal(runCommand(unit()), 'create table a (id integer);')
const fails = (body, opts) => {
stub(dir, 'bin/print-sql', body)
try {
runCommand(unit(), opts)
} catch (e) {
return e.toJSON()
}
return assert.fail('the command was accepted')
}
const empty = fails({})
assert.deepEqual([empty.code, empty.message], ['COMMAND-EMPTY', 'printed no SQL.'])
assert.match(empty.fix, /Run bin\/print-sql by hand/)
assert.equal(fails({ stdout: '-- This is an empty migration.\n' }).code, 'COMMAND-EMPTY', 'comments alone load nothing')
const failed = fails({ stderr: Array.from({ length: 30 }, (_, i) => `line ${i + 1}\n`).join(''), exit: 3 })
assert.equal(failed.code, 'COMMAND-FAILED')
assert.match(failed.message, /^exited 3\.\nline 11\n/)
assert.match(failed.message, /line 30$/, 'the last 20 lines of stderr')
assert.match(fails({ exit: 127 }).message, /^bin\/print-sql is not installed\.$/)
assert.match(fails({ sleep: 10000 }, { timeoutMs: 300 }).message, /^ran past 300 ms and was stopped\.$/)
const missing = plan({ ...genericSource, run: ['bin/not-there'] }, dir)
assert.throws(() => runCommand(missing), (e) => e.code === 'COMMAND-FAILED' && /bin\/not-there is not installed/.test(e.message))
})
test('alembic: found through .venv/bin, run offline with upgrade head --sql; op.get_bind() has its own fix', () => {
const dir = files({ 'alembic.ini': '[alembic]\nscript_location = alembic\n', 'alembic/env.py': '' })
stub(dir, '.venv/bin/alembic', printing(dir, 'create table alembic_version (v text);'))
const unit = planCommand({ kind: 'command', preset: 'alembic', dialect: 'postgres' }, 0, { root: dir, env: cleanEnv() })
assert.equal(runCommand(unit), 'create table alembic_version (v text);')
assert.deepEqual(calls(dir), [['upgrade', 'head', '--sql']])
stub(dir, '.venv/bin/alembic', { stderr: 'AttributeError: get_bind\n', exit: 1 })
assert.throws(() => runCommand(unit), (e) => e.code === 'COMMAND-FAILED' && /op\.get_bind\(\), which offline mode cannot run: execute\(\) returns None there\. Point sources at SQL files instead/.test(e.fix))
})
// The engine's whereabouts. pnpm keeps @prisma/engines beside prisma in its own
// store, not at the top of node_modules (verified with prisma 7.10.0 on pnpm).
const prismaRepo = (extra = {}) =>
files({ 'prisma/schema.prisma': PRISMA_SCHEMA('postgresql'), 'prisma.config.ts': PRISMA_CONFIG, 'node_modules/prisma/package.json': '{ "version": "7.10.0" }', ...extra })
test('prisma: no schema engine is PRISMA-ENGINE-MISSING; npm layout, pnpm layout and PRISMA_SCHEMA_ENGINE_BINARY each clear it', async () => {
const dir = prismaRepo()
stub(dir, 'node_modules/.bin/prisma', printing(dir, PRISMA_PG_SQL))
const config = configWith({ sources: [{ kind: 'command', preset: 'prisma' }] })
const r = await runIn(dir, ['--check', '--json'], { config })
assert.equal(r.code, 2)
const out = json(r)
assert.equal(out.booted, false)
assert.deepEqual(out.untested.map((u) => u.code), ['PRISMA-ENGINE-MISSING'])
assert.match(out.untested[0].fix, /^Allow the engine download, once, online\. pnpm 11: .*pnpm 10: .*npm: npm rebuild/, 'no lockfile says which manager, so it names each')
assert.deepEqual(calls(dir), [], 'the command never ran')
const text = await runIn(dir, ['--check'], { config })
assert.match(text.out, /PRISMA-ENGINE-MISSING/)
const unitOf = (d, env = cleanEnv()) => planCommand({ kind: 'command', preset: 'prisma' }, 0, { root: d, env })
const npm = prismaRepo({ 'node_modules/@prisma/engines/schema-engine-darwin-arm64': '' })
stub(npm, 'node_modules/.bin/prisma', printing(npm, PRISMA_PG_SQL))
assert.equal(runCommand(unitOf(npm)), PRISMA_PG_SQL)
const pnpm = files({
'prisma/schema.prisma': PRISMA_SCHEMA('postgresql'),
'node_modules/.pnpm/prisma@7.10.0/node_modules/prisma/package.json': '{ "version": "7.10.0" }',
'node_modules/.pnpm/prisma@7.10.0/node_modules/@prisma/engines/schema-engine-linux-x64': '',
})
symlinkSync('.pnpm/prisma@7.10.0/node_modules/prisma', join(pnpm, 'node_modules/prisma'))
stub(pnpm, 'node_modules/.bin/prisma', printing(pnpm, PRISMA_PG_SQL))
assert.equal(unitOf(pnpm).version, '7.10.0')
assert.equal(runCommand(unitOf(pnpm)), PRISMA_PG_SQL)
const binary = join(dir, 'my-schema-engine')
writeFileSync(binary, '')
assert.equal(runCommand(unitOf(dir, cleanEnv({ PRISMA_SCHEMA_ENGINE_BINARY: binary }))), PRISMA_PG_SQL)
assert.throws(() => runCommand(unitOf(dir, cleanEnv({ PRISMA_SCHEMA_ENGINE_BINARY: join(dir, 'gone') }))), (e) => e.code === 'PRISMA-ENGINE-MISSING')
const bare = prismaRepo()
assert.throws(() => runCommand(unitOf(bare)), (e) => e.code === 'COMMAND-FAILED' && e.message === 'prisma is not installed.', 'no prisma at all is not an engine problem')
})
test('prisma: no SQL printed is PRISMA-EMPTY with the datasource.url fix', () => {
const dir = prismaRepo({ 'node_modules/@prisma/engines/schema-engine-x': '' })
stub(dir, 'node_modules/.bin/prisma')
const unit = planCommand({ kind: 'command', preset: 'prisma' }, 0, { root: dir, env: cleanEnv() })
assert.throws(() => runCommand(unit), (e) => e.code === 'PRISMA-EMPTY' && /Prisma 7 needs datasource\.url in prisma\.config\.ts even to print SQL: set it to process\.env\.DATABASE_URL\.$/.test(e.fix))
})
test('prisma-shaped repo on SQLite: the preset runs the stub, the SQL loads, and the second run skips the command', async () => {
const dir = prismaRepo({ 'prisma/schema.prisma': PRISMA_SCHEMA('sqlite'), 'node_modules/@prisma/engines/schema-engine-x': '' })
stub(dir, 'node_modules/.bin/prisma', printing(dir, PRISMA_SQLITE_SQL))
writeFileSync(join(dir, 'contract.json'), JSON.stringify({ entities: [{ id: 'invoice', table: 'Invoice', fields: [
{ name: 'number', type: 'text', required: true }, { name: 'paid', type: 'boolean' }, { name: 'total', type: 'decimal', required: true }, { name: 'due', type: 'date' },
] }] }))
const config = configWith({ sources: [{ kind: 'command', preset: 'prisma' }] })
const first = await runIn(dir, ['--check'], { config })
assert.equal(first.code, 0, first.out)
assert.equal(first.out, 'backend: 1 migration, 2 tables, access NOT checked: SQLite has no row security, 0 findings')
assert.deepEqual(calls(dir), [['migrate', 'diff', '--from-empty', '--to-schema', 'prisma/schema.prisma', '--script']])
const second = json(await runIn(dir, ['--json'], { config }))
assert.deepEqual([second.cached, second.booted, calls(dir).length], ['full', false, 1])
writeFileSync(join(dir, 'prisma/schema.prisma'), `${PRISMA_SCHEMA('sqlite')}\n// edited\n`)
assert.equal(json(await runIn(dir, ['--json'], { config })).cached, 'none')
assert.equal(calls(dir).length, 2, 'a change to the watched schema runs it again')
})
test('drizzle-shaped repo on SQLite, and the generic run form: argv, snapshot provenance, cache keyed on the watched files', async () => {
const dir = files({ 'drizzle.config.ts': DRIZZLE_CONFIG('sqlite'), 'src/db/schema.ts': 'export {}', 'node_modules/drizzle-kit/package.json': '{ "version": "0.31.11" }' })
stub(dir, 'node_modules/.bin/drizzle-kit', printing(dir, DRIZZLE_SQLITE_SQL))
const config = configWith({ sources: [{ kind: 'command', preset: 'drizzle' }] })
const snap = async (c) => {
const r = await runIn(dir, ['--print-snapshot'], { config: c })
assert.equal(r.code, 0, r.out)
return JSON.parse(r.out)
}
const s = await snap(config)
assert.deepEqual(calls(dir), [['export', '--config', 'drizzle.config.ts']])
assert.deepEqual([s.source.kind, s.source.platform, s.source.inputs, s.loader.name, s.loader.via], ['command', 'sqlite', ['command:drizzle'], 'node:sqlite', ['command:drizzle']])
assert.deepEqual(Object.keys(s.tables), ['public.invoices'])
await snap(config)
assert.equal(calls(dir).length, 1)
writeFileSync(join(dir, 'src/db/schema.ts'), 'export const x = 1')
await snap(config)
assert.equal(calls(dir).length, 2)
// The generic form, watching a folder.
const generic = files({ 'db/schema.ts': 'v1' })
stub(generic, 'bin/print-sql', printing(generic, 'create table t (id integer primary key);'))
const gconfig = configWith({ sources: [genericSource] })
const a = json(await runIn(generic, ['--json'], { config: gconfig }))
assert.deepEqual([a.exit, a.cached, a.platform], [0, 'none', 'sqlite'])
assert.deepEqual(calls(generic), [[]])
assert.equal(json(await runIn(generic, ['--json'], { config: gconfig })).cached, 'full')
writeFileSync(join(generic, 'db/other.ts'), 'new file')
assert.equal(json(await runIn(generic, ['--json'], { config: gconfig })).cached, 'none', 'a new file under watch is a new key')
assert.equal(calls(generic).length, 2)
})
test('command failures through run(): empty, failed and not installed are exit 2 with nothing cached, and --print-snapshot says so', async () => {
const dir = files({ 'db/schema.ts': 'x' })
const config = configWith({ sources: [genericSource] })
stub(dir, 'bin/print-sql')
const empty = await runIn(dir, ['--check'], { config })
assert.equal(empty.code, 2)
assert.match(empty.out, /COMMAND-EMPTY\s+bin\/print-sql/)
stub(dir, 'bin/print-sql', { stderr: 'boom\n', exit: 1 })
const failed = await runIn(dir, ['--check'], { config })
assert.equal(failed.code, 2)
assert.match(failed.out, /COMMAND-FAILED\s+bin\/print-sql\n\s+exited 1\.\n\s+boom/)
const snap = await runIn(dir, ['--print-snapshot'], { config })
assert.deepEqual([snap.code, snap.out], [2, ''])
assert.match(snap.err, /COMMAND-FAILED/)
stub(dir, 'bin/print-sql', printing(dir, 'create table a (id integer);'))
assert.equal(json(await runIn(dir, ['--json'], { config })).cached, 'none', 'the failures cached nothing')
const slow = files({ 'db/schema.ts': 'x' })
stub(slow, 'bin/print-sql', { sleep: 10000 })
const timed = await runIn(slow, ['--check'], { config: configWith({ sources: [genericSource], commandTimeoutMs: 400 }) })
assert.equal(timed.code, 2)
assert.match(timed.out, /ran past 400 ms/)
})
test('no schema file yet is NO-INPUT naming it, and exit 0 with --allow-empty', async () => {
const dir = files({})
const config = configWith({ sources: [{ kind: 'command', preset: 'prisma' }] })
const r = await runIn(dir, ['--check'], { config })
assert.equal(r.code, 2)
assert.match(r.out, /NO-INPUT\s+prisma\/schema\.prisma/)
assert.equal((await runIn(dir, ['--allow-empty', '--check'], { config })).code, 0)
})
test('print-config: command sources add their watch paths even before the files exist, and count as inputs only when present', async () => {
const lines = async (dir, config, env) => (await runIn(dir, ['--print-config'], { config, env })).out.split('\n')
const prisma = configWith({ sources: [{ kind: 'command', preset: 'prisma' }] })
const none = await lines(files({}), prisma)
assert.ok(none.includes('watch\tprisma/schema.prisma') && none.includes('watch\tprisma.config.ts'), none.join('\n'))
assert.equal(none.at(-1), 'inputCount\t0')
const some = await lines(prismaRepo(), prisma)
assert.equal(some.at(-1), 'inputCount\t1')
assert.ok(some.every((l) => /^(platform|platformWhy|clientAccess|clientAccessWhy|enforcement|enforcementHint|loadGate|watch|source|candidate|inputCount)\t.+$/.test(l)), some.join('\n'))
const drizzle = await lines(files({ 'drizzle.config.ts': DRIZZLE_CONFIG('sqlite'), 'src/db/schema.ts': '' }), configWith({ sources: [{ kind: 'command', preset: 'drizzle' }] }))
assert.deepEqual(drizzle.filter((l) => l.startsWith('watch')).slice(0, 2), ['watch\tdrizzle.config.ts', 'watch\tsrc/db/schema.ts'])
assert.equal(drizzle[0], 'platform\tsqlite')
// The hook's cwd is the snapshot; a root-run source reads NTENT_REPO_ROOT.
const repoRoot = files({ 'alembic.ini': '[alembic]\nscript_location = alembic\n', 'alembic/env.py': '' })
const alembic = configWith({ sources: [{ kind: 'command', preset: 'alembic', dialect: 'postgres' }] })
const viaEnv = await lines(files({}), alembic, cleanEnv({ NTENT_REPO_ROOT: repoRoot }))
assert.ok(viaEnv.includes('watch\talembic/') && viaEnv.includes('watch\talembic.ini'), viaEnv.join('\n'))
assert.equal(viaEnv.at(-1), 'inputCount\t1')
assert.equal((await lines(files({}), alembic)).at(-1), 'inputCount\t0')
})
test('a root-run command reads the worktree, and says so when a watched file differs from the index', async () => {
const dir = files({ 'alembic.ini': '[alembic]\nscript_location = alembic\n', 'alembic/env.py': 'v1' })
stub(dir, '.venv/bin/alembic', printing(dir, 'create table a (id integer primary key);'))
const git = (...args) => execFileSync('git', ['-c', 'user.email=t@ntent.test', '-c', 'user.name=t', ...args], { cwd: dir, stdio: 'ignore' })
git('init', '-q')
writeFileSync(join(dir, '.gitignore'), '.venv\ncalls.log\n')
git('add', '.')
git('commit', '-q', '-m', 'init')
const config = configWith({ sources: [{ kind: 'command', preset: 'alembic', dialect: 'sqlite' }] })
const clean = await runIn(dir, ['--check'], { config })
assert.equal(clean.out, 'backend: 1 migration, 1 table, access NOT checked: SQLite has no row security, 0 findings')
writeFileSync(join(dir, 'alembic/env.py'), 'v2')
const dirty = await runIn(dir, ['--check'], { config })
assert.equal(dirty.code, 0, dirty.out)
assert.match(dirty.out, /note: alembic\/env\.py has unstaged changes; this check read the worktree, not the commit\./)
})
// ------------------------------------------------------- real tools (opt-in) ---
// With prisma 7.10.0 and drizzle-kit 0.31.11 installed (pnpm add -D in a temp
// dir, with @prisma/engines allowed to build), point NTENT_TEST_TOOLS at it.
// Without them these print why they are skipped.
const TOOLS = process.env.NTENT_TEST_TOOLS ?? process.cwd()
const toolSkip = (tool) => (existsSync(join(TOOLS, 'node_modules', '.bin', tool)) ? false : `${tool} is not installed in ${TOOLS}. Set NTENT_TEST_TOOLS to a directory that has it in node_modules.`)
/** A repo whose node_modules is the tools directory's. */
function withTools(map) {
const dir = files(map)
symlinkSync(join(TOOLS, 'node_modules'), join(dir, 'node_modules'))
return dir
}
test('real prisma on Postgres: migrate diff prints SQL that PGlite loads', { skip: toolSkip('prisma') }, async () => {
const dir = withTools({ 'prisma/schema.prisma': PRISMA_SCHEMA('postgresql'), 'prisma.config.ts': PRISMA_CONFIG })
const r = await runIn(dir, ['--print-snapshot'], { config: configWith({ sources: [{ kind: 'command', preset: 'prisma' }] }), env: cleanEnv() })
assert.equal(r.code, 0, r.err)
const snap = JSON.parse(r.out)
assert.deepEqual(Object.keys(snap.tables).filter((k) => !snap.tables[k].platform), ['public.Invoice'])
assert.deepEqual(snap.tables['public.Invoice'].unique, [['number']])
})
test('real prisma on SQLite and real drizzle-kit on SQLite load into node:sqlite', { skip: toolSkip('prisma') || toolSkip('drizzle-kit') }, async () => {
const p = withTools({ 'prisma/schema.prisma': PRISMA_SCHEMA('sqlite'), 'prisma.config.ts': PRISMA_CONFIG })
const pr = await runIn(p, ['--print-snapshot'], { config: configWith({ sources: [{ kind: 'command', preset: 'prisma' }] }) })
assert.equal(pr.code, 0, pr.err)
assert.deepEqual(Object.keys(JSON.parse(pr.out).tables), ['public.Invoice'])
const d = withTools({
'drizzle.config.ts': DRIZZLE_CONFIG('sqlite'),
'src/db/schema.ts': "import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core'\nexport const invoices = sqliteTable('invoices', { id: integer('id').primaryKey(), number: text('number').notNull() })\n",
})
const dr = await runIn(d, ['--print-snapshot'], { config: configWith({ sources: [{ kind: 'command', preset: 'drizzle' }] }) })
assert.equal(dr.code, 0, dr.err)
assert.deepEqual(Object.keys(JSON.parse(dr.out).tables), ['public.invoices'])
})
test('real drizzle-kit on Postgres: export prints SQL that PGlite loads', { skip: toolSkip('drizzle-kit') }, async () => {
const dir = withTools({
'drizzle.config.ts': DRIZZLE_CONFIG('postgresql'),
'src/db/schema.ts': "import { pgTable, uuid, text } from 'drizzle-orm/pg-core'\nexport const invoices = pgTable('invoices', { id: uuid('id').primaryKey().defaultRandom(), number: text('number').notNull() })\n",
})
const r = await runIn(dir, ['--print-snapshot'], { config: configWith({ sources: [{ kind: 'command', preset: 'drizzle' }] }) })
assert.equal(r.code, 0, r.err)
assert.deepEqual(Object.keys(JSON.parse(r.out).tables).filter((k) => k.startsWith('public.')), ['public.invoices'])
})
test('prisma-shaped repo on Postgres: the captured SQL loads into PGlite through the stub, and the snapshot says where it came from', async () => {
const dir = prismaRepo({ 'node_modules/@prisma/engines/schema-engine-x': '' })
stub(dir, 'node_modules/.bin/prisma', printing(dir, PRISMA_PG_SQL))
const r = await runIn(dir, ['--print-snapshot'], { config: configWith({ sources: [{ kind: 'command', preset: 'prisma' }] }) })
assert.equal(r.code, 0, r.err)
const snap = JSON.parse(r.out)
assert.deepEqual([snap.source.kind, snap.source.inputs, snap.loader.name, snap.loader.via], ['command', ['command:prisma'], 'pglite', ['command:prisma']])
assert.deepEqual(snap.tables['public.Invoice'].primaryKey, ['id'])
assert.deepEqual(calls(dir), [['migrate', 'diff', '--from-empty', '--to-schema', 'prisma/schema.prisma', '--script']])
})
// ------------------------------------------------------------ load errors ---
const LATER = 'supabase/migrations/20260102000000_later.sql'
test('load errors: every failure in one pass, classified, each with file:line', async () => {
const sql = [
'create table public.a (id int primary key);',
'create table public.b (select int);',
'alter table public.later add column x int;',
'alter table public.ghost add column y int;',
'create policy p on public.a for select using (true);',
'create policy p on public.a for select using (true);',
'create index on public.b (id);',
"alter table public.a set (timescaledb.compress);",
'',
].join('\n')
const e = await refusal({ [MIGRATION]: sql, [LATER]: 'create table public.later (id int);\n' })
assert.equal(e.code, 'LOAD-FAILED')
assert.equal(e.object, `${MIGRATION}:2`, 'the first failure still heads the problem')
const by = Object.fromEntries(e.problems.map((p) => [p.line, p]))
assert.deepEqual(
e.problems.map((p) => [p.object, p.class]),
[
[`${MIGRATION}:2`, 'real'],
[`${MIGRATION}:3`, 'ordering'],
[`${MIGRATION}:4`, 'outside'],
[`${MIGRATION}:6`, 'duplicate'],
[`${MIGRATION}:7`, 'cascade'],
[`${MIGRATION}:8`, 'engine'],
],
)
assert.match(by[3].fix, new RegExp(`created at ${LATER}:1`))
assert.match(by[4].fix, /first entry of CONFIG\.sources|FIRST entry of CONFIG\.sources/)
assert.match(by[7].fix, new RegExp(`${MIGRATION}:2 failed`))
assert.match(by[8].fix, /CONFIG\.skipStatements/)
assert.ok(e.problems.every((p) => p.code === 'LOAD-FAILED' && p.statement))
})
test('load errors: a file that runs its own transaction is still isolated statement by statement', async () => {
const e = await refusal({ [MIGRATION]: 'begin;\ncreate table public.a (id int);\ncreat table public.b (id int);\ncommit;\n' })
assert.deepEqual(e.problems.map((p) => [p.object, p.class]), [[`${MIGRATION}:3`, 'real']])
})
test('load errors: through run(), exit 2 lists every failure grouped by class, and --json keeps them all', async () => {
const dir = repo({ sql: 'creat table public.b (id int);\nalter table public.ghost add column y int;\n', contractText: null })
const r = await runIn(dir, ['--check', '--schema'])
assert.equal(r.code, 2)
assert.match(r.out, /LOAD-FAILED\s+a real Postgres error, 1 statement/)
assert.match(r.out, /LOAD-FAILED\s+created outside the migrations, 1 statement/)
assert.match(r.out, new RegExp(`${MIGRATION.replace(/\./g, '\\.')}:1`))
assert.match(r.out, new RegExp(`${MIGRATION.replace(/\./g, '\\.')}:2`))
const j = json(await runIn(dir, ['--check', '--schema', '--json']))
assert.equal(j.exit, 2)
assert.deepEqual(j.untested.map((u) => [u.code, u.class, u.line]), [['LOAD-FAILED', 'real', 1], ['LOAD-FAILED', 'outside', 2]])
})
test('a base schema file listed first in CONFIG.sources clears an outside-the-migrations failure', async () => {
const dir = repo({ sql: 'alter table public.ghost add column y int;\n', contractText: null })
mkdirSync(join(dir, 'db'), { recursive: true })
writeFileSync(join(dir, 'db', 'base.sql'), 'create table public.ghost (id int primary key);\n')
const sources = [
{ kind: 'sql', paths: ['db/base.sql'], order: 'name', sections: null },
{ kind: 'sql', paths: ['supabase/migrations/*.sql'], order: 'name', sections: null },
]
const r = await runIn(dir, ['--check', '--schema'], { config: configWith({ sources }) })
assert.equal(r.code, 0, r.out)
})
test('preamble: create extension loads the extension, before the migrations use it', async () => {
const sql = 'create table public.t (email citext primary key);'
const refused = await refusal({ [MIGRATION]: sql })
assert.equal(refused.problems[0].class, 'outside', 'the type is created nowhere')
const { snapshot } = await build({ [MIGRATION]: sql }, { preamble: [{ sql: 'create extension if not exists citext;', reason: 'made in the dashboard' }] })
assert.ok(snapshot.loader.extensions.includes('citext'))
assert.equal(snapshot.tables['public.t'].columns.email.family, 'text')
})
test('skipStatements: a statement the engine cannot run is skipped, printed as a waiver, and stale when nothing matches', async () => {
const sql = 'create table public.a (id int primary key);\nalter table public.a\n set (timescaledb.compress);\n'
const entry = { reason: 'storage parameter, no shape change', until: '2027-01-01' }
const skipStatements = { 'set (timescaledb.compress)': entry, 'never appears': entry }
const { snapshot, config } = await build({ [MIGRATION]: sql }, { skipStatements })
assert.deepEqual(snapshot.loader.rewritten, [
{ file: MIGRATION, line: 2, what: "statement skipped -> select 1 (CONFIG.skipStatements 'set (timescaledb.compress)')" },
])
const out = evaluate({ snapshot, contract: null, config, gates: { schema: true, access: false }, today: TODAY })
assert.deepEqual(out.waived, [{ list: 'skipStatements', key: 'set (timescaledb.compress)', code: 'LOAD-FAILED', until: '2027-01-01', reason: entry.reason }])
assert.ok(out.notes.some((n) => n.message === "stale waiver: 'never appears' in CONFIG.skipStatements. Delete it."))
assert.deepEqual(validateConfig(configWith({ skipStatements: { x: { reason: 'r' } } })).map((p) => p.object), ["skipStatements['x']"])
})
test('encoding: a UTF-8 BOM and UTF-16 files load, and the snapshot says they were decoded', async () => {
const sql = 'create table public.a (id int primary key);\n'
const dir = repo({ sql: null })
mkdirSync(join(dir, 'supabase', 'migrations'), { recursive: true })
writeFileSync(join(dir, MIGRATION), Buffer.concat([Buffer.from([0xff, 0xfe]), Buffer.from(sql, 'utf16le')]))
writeFileSync(join(dir, LATER), Buffer.concat([Buffer.from([0xef, 0xbb, 0xbf]), Buffer.from('create table public.b (id int);\n')]))
const r = await runIn(dir, ['--print-snapshot'])
assert.equal(r.code, 0, r.err)
const snap = JSON.parse(r.out)
assert.deepEqual(Object.keys(snap.tables).filter((k) => k.startsWith('public.')), ['public.a', 'public.b'])
assert.deepEqual(snap.loader.rewritten.map((x) => x.what), ['decoded utf-16le to text', 'removed a UTF-8 byte order mark'])
const { decodeSql } = await import('./check-backend.mjs')
assert.equal(decodeSql(Buffer.from(sql, 'utf16le')).text, sql, 'UTF-16LE with no BOM')
})
test('a comment-only or empty migration is a note, not silence', async () => {
const { snapshot, config } = await build({ [MIGRATION]: GREEN_SQL, [LATER]: '-- final lockdown\n/* todo */\n', 'supabase/migrations/20260103000000_empty.sql': '\n' })
const out = evaluate({ snapshot, contract: null, config, gates: { schema: true, access: false }, today: TODAY })
const empties = out.notes.filter((n) => n.code === 'EMPTY-MIGRATION')
assert.deepEqual(empties.map((n) => n.object), [LATER, 'supabase/migrations/20260103000000_empty.sql'])
assert.match(empties[0].message, /comments and no SQL/)
})
// ----------------------------------------------------------- grouped output ---
test('output: one finding per cause with the probes beneath it, and --json keeps every row', async () => {
const dir = repo({ sql: GREEN_SQL.replace(/alter table public\.\w+ enable row level security;\n/g, '') })
const r = await runIn(dir, ['--check'])
assert.equal(r.code, 1)
assert.match(r.out, /POLICY-BUT-RLS-OFF\s+3 objects, one cause/)
assert.match(r.out, /public\.invoices\s+LEAK-READ, LEAK-UPDATE, LEAK-DELETE, LEAK-INSERT as customer/)
assert.doesNotMatch(r.out, /^ {2}LEAK-/m, 'no LEAK row of its own')
const j = json(await runIn(dir, ['--json']))
const leaks = j.findings.filter((f) => f.code.startsWith('LEAK-'))
assert.equal(leaks.length, 8, 'every probe is still in the JSON')
assert.ok(leaks.every((l) => l.group === `POLICY-BUT-RLS-OFF:${j.findings.find((f) => f.code === 'POLICY-BUT-RLS-OFF').object}`))
assert.ok(leaks[0].message && leaks[0].fix && leaks[0].object, 'rows keep every field')
})
// ------------------------------------------------------------ probe fixes ---
const TEAMS_SQL = `create table public.teams (id uuid primary key default gen_random_uuid(), name text);
create table public.members (
team_id uuid not null references public.teams(id),
user_id uuid not null references auth.users(id),
primary key (team_id, user_id)
);
alter table public.teams enable row level security;
alter table public.members enable row level security;
create policy "create a team" on public.teams for insert to authenticated with check (true);
create policy "read my teams" on public.teams for select to authenticated
using (id in (select team_id from public.members where user_id = (select auth.uid())));
create policy "read my memberships" on public.members for select to authenticated using (user_id = (select auth.uid()));
`
const TEAMS_CONTRACT = () => ({
actors: [{ id: 'user' }],
access: {
tenancy: { scope_entity: 'team', membership: { table: 'public.members', user_column: 'user_id', tenant_column: 'team_id' } },
matrix: [
{ actor: 'user', entity: 'team', can: ['read', 'create'], scope: 'tenant' },
{ actor: 'user', entity: 'member', can: ['read'], scope: 'tenant' },
],
},
entities: [
{ id: 'team', table: 'public.teams', fields: ['name'] },
{ id: 'member', table: 'public.members', fields: [{ name: 'team_id', type: 'id', ref: 'team' }, { name: 'user_id', type: 'id' }] },
],
})
test('LEAK-INSERT: a primary key refusal after the policy passed is not a leak when no row lands', async () => {
// insert check (true) on teams, as the app creates a team. B's id collides
// with teams_pkey, and no other key could stay in B's team.
const r = await access({ [MIGRATION]: TEAMS_SQL }, {}, { edit: TEAMS_CONTRACT })
assert.ok(!r.all.includes('LEAK-INSERT'), r.all.join(','))
assert.equal(r.deny.tests.find((t) => t.table === 'public.teams').probes.P5, 'unproven')
const note = r.notes.find((n) => n.code === 'INSERT-UNPROVEN')
assert.equal(note.object, 'public.teams as user')
assert.match(note.message, /not proven/)
})
test('LEAK-INSERT: the retry with A as the user lands a row in B\'s team, and that is a leak', async () => {
// Any signed-in user may add a membership: a takeover. B's own membership
// row collides on the key, so the retry puts A in B's team.
const sql = `${TEAMS_SQL}create policy "anyone joins" on public.members for insert to authenticated with check (true);\n`
const r = await access({ [MIGRATION]: sql }, {}, { edit: TEAMS_CONTRACT })
const leak = r.findings.find((f) => f.code === 'LEAK-INSERT')
assert.equal(leak.table, 'public.members')
assert.equal(r.deny.tests.find((t) => t.table === 'public.members').probes.P5, 'leak')
})
test('PROBE-ERROR: a trigger that reads a missing column says so, and does not tell you to grant', async () => {
const sql = `create table public.notes (id uuid primary key default gen_random_uuid(), owner uuid not null references auth.users(id), body text);
alter table public.notes enable row level security;
create policy own on public.notes for all to authenticated using (owner = (select auth.uid())) with check (owner = (select auth.uid()));
create function public.touch() returns trigger language plpgsql as $$ begin new.updated_at = now(); return new; end $$;
create trigger notes_touch before update on public.notes for each row execute function public.touch();
`
const r = await access({ [MIGRATION]: sql }, {}, {
edit: () => ({
actors: [{ id: 'user' }],
access: { matrix: [{ actor: 'user', entity: 'note', can: ['read', 'update'], scope: 'own' }] },
entities: [{ id: 'note', table: 'public.notes', owner: 'owner', fields: [{ name: 'owner', type: 'id' }, 'body'] }],
}),
})
const p = r.untested.find((u) => u.code === 'PROBE-ERROR')
assert.match(p.message, /42703/)
assert.match(p.fix, /not a grant problem/)
assert.doesNotMatch(p.fix, /grant it|CONFIG\.untested/)
})
// ----------------------------------------------------------- auth shapes ---
const contractOf = ({ entities, matrix, tenancy }) => () => ({
actors: [{ id: 'user' }],
access: { ...(tenancy ? { tenancy } : {}), matrix },
entities,
})
const clean = (r) => assert.deepEqual([r.all, r.exit], [[], 0], JSON.stringify([...r.findings, ...r.untested].map((x) => [x.code, x.object, x.message])))
test('seeder: membership through the app\'s own users table, keyed by the auth id (no FK to auth.users)', async () => {
const sql = `create table public.users (id uuid primary key, email text);
create table public.orgs (id uuid primary key default gen_random_uuid(), name text not null);
create table public.org_members (
org_id uuid not null references public.orgs(id),
user_id uuid not null references public.users(id),
primary key (org_id, user_id)
);
create table public.leads (id uuid primary key default gen_random_uuid(), org_id uuid not null references public.orgs(id), name text);
alter table public.users enable row level security;
alter table public.orgs enable row level security;
alter table public.org_members enable row level security;
alter table public.leads enable row level security;
create policy mine on public.org_members for select to authenticated using (user_id = (select auth.uid()));
create policy mine on public.orgs for select to authenticated using (id in (select org_id from public.org_members where user_id = (select auth.uid())));
create policy mine on public.leads for all to authenticated
using (org_id in (select org_id from public.org_members where user_id = (select auth.uid())))
with check (org_id in (select org_id from public.org_members where user_id = (select auth.uid())));
`
const edit = contractOf({
tenancy: { scope_entity: 'org', column: 'org_id', membership: { table: 'public.org_members', user_column: 'user_id', tenant_column: 'org_id' } },
matrix: [{ actor: 'user', entity: 'lead', can: ['read'], scope: 'tenant' }],
entities: [
{ id: 'org', table: 'public.orgs', fields: ['name'] },
{ id: 'lead', table: 'public.leads', fields: [{ name: 'org_id', type: 'id', ref: 'org' }, 'name'] },
],
})
clean(await access({ [MIGRATION]: sql }, {}, { edit }))
})
test('seeder: membership through a profiles table with its own key and a column that references auth.users', async () => {
const sql = `create table public.profiles (id uuid primary key default gen_random_uuid(), user_id uuid not null unique references auth.users(id));
create table public.orgs (id uuid primary key default gen_random_uuid(), name text not null);
create table public.org_members (org_id uuid not null references public.orgs(id), profile_id uuid not null references public.profiles(id), primary key (org_id, profile_id));
create table public.leads (id uuid primary key default gen_random_uuid(), org_id uuid not null references public.orgs(id), name text);
alter table public.profiles enable row level security;
alter table public.orgs enable row level security;
alter table public.org_members enable row level security;
alter table public.leads enable row level security;
create function public.my_profile() returns uuid language sql stable security definer set search_path = public as $$ select id from public.profiles where user_id = auth.uid() $$;
create policy mine on public.org_members for select to authenticated using (profile_id = public.my_profile());
create policy mine on public.orgs for select to authenticated using (id in (select org_id from public.org_members where profile_id = public.my_profile()));
create policy mine on public.leads for all to authenticated
using (org_id in (select org_id from public.org_members where profile_id = public.my_profile()))
with check (org_id in (select org_id from public.org_members where profile_id = public.my_profile()));
`
const edit = contractOf({
tenancy: { scope_entity: 'org', column: 'org_id', membership: { table: 'public.org_members', user_column: 'profile_id', tenant_column: 'org_id' } },
matrix: [{ actor: 'user', entity: 'lead', can: ['read'], scope: 'tenant' }],
entities: [
{ id: 'org', table: 'public.orgs', fields: ['name'] },
{ id: 'lead', table: 'public.leads', fields: [{ name: 'org_id', type: 'id', ref: 'org' }, 'name'] },
],
})
clean(await access({ [MIGRATION]: sql }, {}, { edit }))
})
test('seeder: a row a trigger on auth.users already made is reused, not collided with', async () => {
const sql = `create table public.users (id uuid primary key references auth.users(id), org_id uuid);
create function public.on_signup() returns trigger language plpgsql security definer set search_path = public as $$ begin insert into public.users (id) values (new.id); return new; end $$;
create trigger on_signup after insert on auth.users for each row execute function public.on_signup();
alter table public.users enable row level security;
create policy own on public.users for all to authenticated using (id = (select auth.uid())) with check (id = (select auth.uid()));
`
// The tenancy column is on the row, so the seeder looks for a row with it, and the trigger's row has none.
const edit = contractOf({
tenancy: { column: 'org_id' },
matrix: [{ actor: 'user', entity: 'account', can: ['read', 'update'], scope: 'own' }],
entities: [{ id: 'account', table: 'public.users', owner: 'id', fields: [{ name: 'id', type: 'id' }, 'org_id'] }],
})
clean(await access({ [MIGRATION]: sql }, {}, { edit }))
})
const PROJECTS_SQL = (taskPolicy) => `create table public.projects (id uuid primary key default gen_random_uuid(), owner uuid not null references auth.users(id), name text);
create table public.tasks (id uuid primary key default gen_random_uuid(), project_id uuid not null references public.projects(id), title text);
alter table public.projects enable row level security;
alter table public.tasks enable row level security;
create policy own on public.projects for all to authenticated using (owner = (select auth.uid())) with check (owner = (select auth.uid()));
${taskPolicy}
`
const TASKS_CONTRACT = (extra = {}) =>
contractOf({
matrix: [{ actor: 'user', entity: 'task', can: ['read'], scope: 'own' }],
entities: [
{ id: 'project', table: 'public.projects', owner: 'owner', fields: [{ name: 'owner', type: 'id' }, 'name'] },
{ id: 'task', table: 'public.tasks', ...extra, fields: [{ name: 'project_id', type: 'id', ref: 'project' }, 'title'] },
],
})
test('contract: an owner on a parent row, reached by a ref, is tested', async () => {
const good = `create policy own on public.tasks for all to authenticated
using (project_id in (select id from public.projects where owner = (select auth.uid())))
with check (project_id in (select id from public.projects where owner = (select auth.uid())));`
clean(await access({ [MIGRATION]: PROJECTS_SQL(good) }, {}, { edit: TASKS_CONTRACT() }))
clean(await access({ [MIGRATION]: PROJECTS_SQL(good) }, {}, { edit: TASKS_CONTRACT({ owner_via: 'project_id' }) }))
const leaky = 'create policy open on public.tasks for all to authenticated using (true) with check (true);'
const r = await access({ [MIGRATION]: PROJECTS_SQL(leaky) }, {}, { edit: TASKS_CONTRACT() })
assert.ok(r.all.includes('LEAK-READ'), r.all.join(','))
const leak = r.findings.find((f) => f.code === 'LEAK-READ')
assert.match(leak.message, /belongs to another user/)
assert.match(leak.fix, /project_id in \(select id from public\.projects where owner = auth\.uid\(\)\)/)
})
test('contract: no owner anywhere up the refs is NO-TENANT-PATH, and the fix names owner_via', async () => {
const r = await access({ [MIGRATION]: PROJECTS_SQL('') }, {}, {
edit: contractOf({
matrix: [{ actor: 'user', entity: 'task', can: ['read'], scope: 'own' }],
entities: [
{ id: 'project', table: 'public.projects', fields: ['name'] },
{ id: 'task', table: 'public.tasks', fields: [{ name: 'project_id', type: 'id', ref: 'project' }, 'title'] },
],
}),
})
const p = r.untested.find((u) => u.code === 'NO-TENANT-PATH')
assert.match(p.fix, /owner_via/)
})
const OWN_SQL = (columns, checks) => `create table public.t (id uuid primary key default gen_random_uuid(), owner uuid not null references auth.users(id), ${columns}${checks ? `, ${checks}` : ''});
alter table public.t enable row level security;
create policy own on public.t for all to authenticated using (owner = (select auth.uid())) with check (owner = (select auth.uid()));
`
const OWN_CONTRACT = contractOf({
matrix: [{ actor: 'user', entity: 't', can: ['read'], scope: 'own' }],
entities: [{ id: 't', table: 'public.t', owner: 'owner', fields: [{ name: 'owner', type: 'id' }] }],
})
test('seeder: CHECKs it can read are met: a comparison across two columns, = any(array), in (...)', async () => {
const sql = OWN_SQL(
"starts date not null, ends date not null, opens timestamptz not null, closes timestamptz not null, lo int not null, hi int not null, kind text not null, tier text not null",
"check (ends > starts), check (closes >= opens), check (hi > lo), check (kind = any (array['a', 'b'])), check (tier in ('x', 'y'))",
)
clean(await access({ [MIGRATION]: sql }, {}, { edit: OWN_CONTRACT }))
})
test('seeder: a CHECK it cannot read says exactly what to put in CONFIG.seed, and that value then works', async () => {
const sql = OWN_SQL('code text not null', "check (code ~ '^[A-Z]{3}$')")
const r = await access({ [MIGRATION]: sql }, {}, { edit: OWN_CONTRACT })
const u = r.untested.find((x) => x.code === 'SEED-FAILED')
assert.match(u.fix, /t_code_check is CHECK/)
assert.match(u.fix, /CONFIG\.seed\['public\.t'\] = \{ code: '<a value that passes>' \}/)
clean(await access({ [MIGRATION]: sql }, { seed: { 'public.t': { code: 'ABC' } } }, { edit: OWN_CONTRACT }))
})
// ------------------------------------------------ finding the schema (R12) ---
const found = (dir) => discoverSources(dir)
const lineOf = (c) => c.line
test('discovery: supabase/migrations at any depth ranks first and prints the line to paste; node_modules, dist and build are not searched', () => {
const dir = files({
'packages/db/supabase/migrations/20260101_a.sql': 'create table a (id int);',
'packages/db/supabase/migrations/20260102_b.sql': 'create table b (id int);',
'node_modules/pkg/migrations/x.sql': 'create table nope (id int);',
'dist/schema.sql': 'create table nope (id int);',
'build/schema.sql': 'create table nope (id int);',
'vendor/schema.sql': 'create table nope (id int);',
'.git/hooks/x.sql': 'create table nope (id int);',
'.venv/lib/x.sql': 'create table nope (id int);',
'docs/seed/data.sql': 'insert into a values (1);',
})
const [first, ...rest] = found(dir)
assert.deepEqual([first.kind, first.path, first.n], ['supabase', 'packages/db/supabase/migrations', 1])
assert.equal(first.line, "sources: [{ kind: 'sql', paths: ['packages/db/supabase/migrations/*.sql'], order: 'name', sections: null }],")
assert.deepEqual(rest.map((c) => c.path), ['docs/seed'], 'nothing from the skipped folders, and a seed folder last')
assert.equal(rest[0].lowPriority, true)
})
test('discovery: prisma, drizzle and alembic at depth, each with its own CONFIG line', () => {
const dir = files({
'apps/api/prisma/schema.prisma': PRISMA_SCHEMA('postgresql'),
'apps/web/drizzle.config.ts': DRIZZLE_CONFIG('postgresql'),
'backend/alembic.ini': '[alembic]\nsqlalchemy.url = sqlite:///x.db\n',
'root/alembic.ini': '[alembic]\n',
})
const byKind = Object.fromEntries(found(dir).map((c) => [`${c.kind}:${c.path}`, c.line]))
assert.equal(byKind['prisma:apps/api/prisma/schema.prisma'], "sources: [{ kind: 'command', preset: 'prisma', schema: 'apps/api/prisma/schema.prisma' }],")
assert.equal(byKind['drizzle:apps/web/drizzle.config.ts'], "sources: [{ kind: 'command', preset: 'drizzle', config: 'apps/web/drizzle.config.ts' }],")
assert.equal(byKind['alembic:backend/alembic.ini'], "sources: [{ kind: 'command', preset: 'alembic', config: 'backend/alembic.ini', dialect: 'sqlite' }],")
assert.match(byKind['alembic:root/alembic.ini'], /dialect: 'postgres'/)
const top = discoverSources(files({ 'prisma/schema.prisma': PRISMA_SCHEMA('sqlite') }))[0]
assert.equal(top.line, "sources: [{ kind: 'command', preset: 'prisma', schema: 'prisma/schema.prisma' }],")
const multi = discoverSources(files({ 'prisma/schema/base.prisma': PRISMA_SCHEMA('sqlite'), 'prisma/schema/models.prisma': 'model A {\n id Int @id\n}\n' }))
assert.deepEqual(multi.map((c) => c.path), ['prisma/schema'], 'a folder of .prisma files is one source')
})
test('discovery: a base schema that sorts after its patches is one source, in order: listed', () => {
const dir = files({
'ga-backend/schema.sql': 'create table a (id int primary key);',
'ga-backend/migration_add_b.sql': 'alter table a add column b int;',
'ga-backend/migration_add_c.sql': 'alter table a add column c int;',
})
const [c] = found(dir)
assert.equal(c.line, "sources: [{ kind: 'sql', paths: ['ga-backend/schema.sql', 'ga-backend/migration_*.sql'], order: 'listed', sections: null }],")
// The line it prints loads schema.sql first. One glob would load the patches first and fail.
assert.deepEqual(resolveInputs(dir, c.sources).map((f) => f.path), ['ga-backend/schema.sql', 'ga-backend/migration_add_b.sql', 'ga-backend/migration_add_c.sql'])
assert.deepEqual(resolveInputs(dir, [{ kind: 'sql', paths: ['ga-backend/*.sql'] }]).map((f) => f.path)[0], 'ga-backend/migration_add_b.sql', 'by name, the patch is first')
assert.deepEqual(validateConfig(configWith({ sources: c.sources })), [])
assert.equal(validateConfig(configWith({ sources: [{ kind: 'sql', paths: ['a'], order: 'sorted' }] }))[0].fix, "Set 'name', 'natural' or 'listed'.")
})
test('discovery: down files are left out, dbmate and goose sections and unpadded numbers are read from the files', () => {
const pairs = files({ 'db/001_a.up.sql': 'create table a (id int);', 'db/001_a.down.sql': 'drop table a;', 'db/002_b.up.sql': 'create table b (id int);', 'db/002_b.down.sql': 'drop table b;' })
assert.deepEqual(found(pairs)[0].sources, [{ kind: 'sql', paths: ['db/*.up.sql'], order: 'name', sections: null }])
const dbmate = files({ 'db/migrations/1_a.sql': '-- migrate:up\ncreate table a (id int);\n-- migrate:down\ndrop table a;\n' })
assert.equal(found(dbmate)[0].sources[0].sections, 'dbmate')
const goose = files({ 'migrations/1_a.sql': '-- +goose Up\ncreate table a (id int);\n-- +goose Down\ndrop table a;\n' })
assert.equal(found(goose)[0].sources[0].sections, 'goose')
const flyway = files({ 'sql/V2__b.sql': 'create table b (id int);', 'sql/V10__c.sql': 'create table c (id int);' })
assert.equal(found(flyway)[0].sources[0].order, 'natural')
const lite = files({ 'src-tauri/db/migrations/1.sql': 'create table a (id integer primary key autoincrement);' })
assert.equal(found(lite)[0].sources[0].dialect, 'sqlite')
})
test('discovery: prisma migration folders, and an archive of old schemas ranks last', () => {
const dir = files({
'prisma/migrations/20260101_init/migration.sql': 'create table a (id int);',
'prisma/migrations/20260102_two/migration.sql': 'create table b (id int);',
'prisma/schema.prisma': PRISMA_SCHEMA('postgresql'),
'sql/_archive/old_schema.sql': 'create table old (id int);',
'server/migrations/001_init.sql': 'create table s (id int);',
})
const list = found(dir)
assert.equal(list[0].kind, 'prisma', 'the declared source of truth first')
assert.equal(list.at(-1).path, 'sql/_archive')
assert.equal(list.at(-1).lowPriority, true)
const folders = list.find((c) => c.path === 'prisma/migrations')
assert.equal(folders.line, "sources: [{ kind: 'sql', paths: ['prisma/migrations/*/migration.sql'], order: 'name', sections: null }],")
assert.ok(list.findIndex((c) => c.path === 'server/migrations') < list.findIndex((c) => c.path === 'sql/_archive'))
})
test('NO-INPUT lists what it found with the line to paste, picks nothing, and pasting the line clears it', async () => {
const dir = files({ 'server/migrations/001_init.sql': 'create table widgets (id int primary key);' })
const config = configWith({ platform: 'postgres' })
const r = await runIn(dir, ['--check'], { config })
assert.equal(r.code, 2, 'one candidate is still not used: the source decides what the gate checks')
assert.match(r.out, /NO-INPUT/)
assert.match(r.out, /Found in this repo, best first/)
assert.match(r.out, /1\. server\/migrations/)
assert.match(r.out, /sources: \[\{ kind: 'sql', paths: \['server\/migrations\/\*\.sql'\], order: 'name', sections: null \}\],/)
assert.match(r.out, /fix: No migration files at supabase\/migrations\/\*\.sql\. Paste one of the sources lines above into CONFIG/)
const paste = await runIn(dir, ['--check'], { config: configWith({ platform: 'postgres', sources: discoverSources(dir)[0].sources }) })
assert.equal(paste.code, 0, paste.out)
const none = await runIn(files({ 'README.md': 'x' }), ['--check'], { config })
assert.equal(none.code, 2)
assert.match(none.out, /and none found anywhere in the repo/)
assert.doesNotMatch(none.out, /Found in this repo/)
})
test('print-config: with no input it lists candidates before inputCount; with input it does not walk the repo', async () => {
const dir = files({ 'db/schema.sql': 'create table a (id int);', 'apps/api/prisma/schema.prisma': PRISMA_SCHEMA('postgresql') })
const none = (await runIn(dir, ['--print-config'], { config: configWith({ platform: 'postgres' }) })).out.split('\n')
const cands = none.filter((l) => l.startsWith('candidate\t'))
assert.equal(cands.length, 2)
assert.match(cands[0], /^candidate\t1\tapps\/api\/prisma\/schema\.prisma\tPrisma schema\tsources: \[\{ kind: 'command', preset: 'prisma', schema: 'apps\/api\/prisma\/schema\.prisma' \}\],$/)
assert.equal(none.at(-1), 'inputCount\t0')
const some = (await runIn(repo(), ['--print-config'])).out.split('\n')
assert.deepEqual(some.filter((l) => l.startsWith('candidate')), [])
})
// ---------------------------------------------------- honest platform defaults ---
test('platform signals: each one says Supabase; none says plain Postgres, and why', () => {
const detect = (map, sql = []) => {
const dir = files(map)
return detectPlatform(dir, scanRepo(dir), sql)
}
assert.deepEqual(detect({ 'supabase/config.toml': 'project_id = "x"' }).reasons, ['supabase/config.toml'])
assert.match(detect({ 'apps/web/package.json': '{"dependencies":{"@supabase/supabase-js":"2"}}' }).reasons[0], /a @supabase dependency in apps\/web\/package\.json/)
assert.match(detect({ 'package.json': '{"dependencies":{"@supabase/ssr":"0.5"}}' }).reasons[0], /@supabase dependency/)
// An env example is a hint and never decides alone (R36): it is named, not counted.
const envOnly = detect({ '.env.example': 'NEXT_PUBLIC_SUPABASE_URL=https://abc.supabase.co' })
assert.equal(envOnly.platform, 'postgres')
assert.match(envOnly.reasons[0], /Seen and not counted: a Supabase URL or key in \.env\.example \(an env example lists options, it does not choose\)/)
assert.match(detect({ 'requirements.txt': 'fastapi\nsupabase==2.4\n' }).reasons[0], /the supabase package in requirements\.txt/)
assert.match(detect({}, [['db/a.sql', 'create policy p on t using (auth.uid() = user_id);']]).reasons[0], /auth\.uid\(\) in db\/a\.sql/)
assert.equal(detect({}, [['db/a.sql', 'create policy p on t to authenticated using (true);']]).platform, 'supabase')
const plain = detect({ 'package.json': '{"dependencies":{"pg":"8","prisma":"7"}}', '.env.example': 'DATABASE_URL=postgres://localhost/app', 'README.md': 'Deploy on Supabase if you like' }, [['a.sql', 'create table a (id int);']])
assert.equal(plain.platform, 'postgres')
assert.match(plain.reasons[0], /^no supabase\/config\.toml, no @supabase dependency/)
})
test("platform 'auto': plain Postgres is resolved, said in the header, and RLS-OFF is a note; clientAccess direct makes it a finding", async () => {
const dir = files({
'db/schema.sql': 'create table public.widgets (id int primary key, name text);',
'package.json': '{"dependencies":{"pg":"8"}}',
})
const sources = [{ kind: 'sql', paths: ['db/*.sql'], order: 'name', sections: null }]
// enforcement 'rls' pinned: with 'auto', a schema with no RLS at all is app-code access and none of this runs.
const auto = configWith({ platform: 'auto', sources, enforcement: 'rls' })
const r = await runIn(dir, [], { config: auto })
assert.equal(r.code, 0, r.out)
assert.match(r.out, /^check-backend: postgres \(auto: no supabase\/config\.toml, no @supabase dependency.*\), 1 migration, 1 table/)
assert.match(r.out, /note\s+RLS-OFF\s+1 table: row level security is off on public\.widgets\. Only your server connects \(CONFIG\.clientAccess is server-only\), so this is a note, not a finding\./)
assert.match(r.out, /set clientAccess: 'direct' in CONFIG, and these become findings/)
// Many tables are one note, not one each.
const many = files({ 'db/schema.sql': Array.from({ length: 20 }, (_, i) => `create table public.t${i} (id int primary key);`).join('\n') })
const noisy = await runIn(many, [], { config: auto })
assert.equal(noisy.out.match(/note\s+RLS-OFF/g).length, 1)
assert.match(noisy.out, /public\.t0, .*public\.t19 and 8 more\./)
assert.equal(json(await runIn(dir, ['--json'], { config: auto })).platform, 'postgres')
const direct = await runIn(dir, ['--check'], { config: configWith({ platform: 'auto', sources, clientAccess: 'direct', enforcement: 'rls' }) })
assert.equal(direct.code, 1, direct.out)
assert.match(direct.out, /RLS-OFF\s+public\.widgets/)
// Explicit values still work: platform supabase on the same repo reads it as Supabase.
const supabase = await runIn(dir, ['--check'], { config: configWith({ platform: 'supabase', sources }) })
assert.equal(supabase.code, 1)
assert.match(supabase.out, /anon and authenticated can reach it|authenticated and anon can reach it|anon, authenticated|RLS-OFF/)
const pinned = await runIn(dir, [], { config: configWith({ platform: 'postgres', sources, clientAccess: 'server-only', enforcement: 'rls' }) })
assert.match(pinned.out, /^check-backend: postgres, 1 migration/, 'a pinned platform needs no reason in the header')
})
test("platform 'auto': a Supabase signal gives the shim and the findings it always did", async () => {
const dir = files({
'supabase/config.toml': 'project_id = "x"',
'supabase/migrations/20260101_a.sql': 'create table public.widgets (id int primary key);',
})
const r = await runIn(dir, [], { config: configWith({ platform: 'auto' }) })
assert.equal(r.code, 1, r.out)
assert.match(r.out, /^check-backend: supabase \(auto: found supabase\/config\.toml\), 1 migration/)
assert.match(r.out, /RLS-OFF\s+public\.widgets/)
const cfg = (await runIn(dir, ['--print-config'], { config: configWith({ platform: 'auto' }) })).out.split('\n')
assert.deepEqual(cfg.slice(0, 5), [
'platform\tsupabase',
'platformWhy\tauto: found supabase/config.toml',
'clientAccess\tdirect',
"clientAccessWhy\tauto: Supabase's Data API serves the database to clients",
'enforcement\trls',
])
const pg = (await runIn(files({ 'supabase/migrations/a.sql': 'create table t (id int);' }), ['--print-config'], { config: configWith({ platform: 'auto' }) })).out.split('\n')
assert.deepEqual(pg.slice(0, 3), ['platform\tpostgres', 'platformWhy\tauto: no supabase/config.toml, no @supabase dependency, no Supabase URL in an env example, no auth.uid() or authenticated role in the migrations', 'clientAccess\tserver-only'])
})
test('config: platform and clientAccess take their values and refuse others', () => {
assert.deepEqual(validateConfig(configWith({ platform: 'auto', clientAccess: 'server-only' })), [])
assert.equal(validateConfig(configWith({ platform: 'neon' }))[0].fix, "Set it to 'auto', 'supabase' or 'postgres'.")
const bad = validateConfig(configWith({ clientAccess: 'some' }))
assert.deepEqual([bad[0].object, bad[0].fix], ['clientAccess', "Set 'auto', 'direct' (a client reaches the database) or 'server-only' (only your server does)."])
})
test("enforcement 'app': says what is left unchecked, in the report and in the one-line summary", async () => {
const dir = repo()
const full = await runIn(dir, [], { config: configWith({ enforcement: 'app' }) })
assert.equal(full.code, 0, full.out)
assert.match(full.out, /not checked: CONFIG\.enforcement is 'app'/)
assert.match(full.out, /Left unchecked: every RLS lint .* and the cross-tenant deny test\./)
assert.match(full.out, /A query that forgets its tenant or owner filter \(an IDOR\), and a table that holds another tenant's rows, are not caught/)
assert.match(full.out, /Still checked: the schema gate/)
const check = await runIn(dir, ['--check'], { config: configWith({ enforcement: 'app' }) })
assert.equal(check.out, 'backend: 1 migration, 3 tables, access NOT checked: enforced in app code, 0 findings (cached)')
assert.match(full.out, /Access NOT checked: enforced in app code\.$/, 'the last line of the full report says it too')
assert.match(full.out, /\(an IDOR\).*out of scope here \(roadmap R1\)/)
})
test("enforcement 'rls' pinned on a plain Postgres repo with no RLS anywhere is told it may mean enforcement 'app'", async () => {
const dir = files({ 'db/schema.sql': 'create table public.widgets (id int primary key);' })
const sources = [{ kind: 'sql', paths: ['db/*.sql'] }]
const r = await runIn(dir, [], { config: configWith({ platform: 'postgres', sources, enforcement: 'rls' }) })
assert.match(r.out, /note\s+NO-RLS-FOUND/)
assert.match(r.out, /no migration enables row level security or creates a policy\. If access is enforced in app code, set enforcement: 'app'/)
const cfg = (await runIn(dir, ['--print-config'], { config: configWith({ platform: 'postgres', sources, enforcement: 'rls' }) })).out
assert.match(cfg, /enforcementHint\tno migration enables row level security/)
const quiet = await runIn(dir, ['--print-config'], { config: configWith({ platform: 'postgres', sources, enforcement: 'app' }) })
assert.doesNotMatch(quiet.out, /enforcementHint/)
assert.doesNotMatch((await runIn(repo(), ['--print-config'], { config: configWith({ enforcement: 'rls' }) })).out, /enforcementHint/, 'a repo that enables RLS is not told')
})
// ------------------------------------------------------ lint finding text ---
test('lints: POLICY-ALWAYS-TRUE names the role, and a policy only service_role can use is not flagged', async () => {
const table = `create table public.audit (id int primary key);
alter table public.audit enable row level security;
create policy "svc" on public.audit for all to service_role using (true) with check (true);
create policy "anon writes" on public.audit for insert to anon with check (true);
create policy "everyone updates" on public.audit for update using (true);
`
const out = await access(plus(table))
const notes = out.notes.filter((n) => n.code === 'POLICY-ALWAYS-TRUE')
assert.deepEqual(notes.map((n) => n.message), [
'policy "anon writes" on public.audit is true for INSERT, for anon.',
'policy "everyone updates" on public.audit is true for UPDATE, for every role.',
], 'service_role has full access is not a finding')
assert.match(notes[0].fix, /^Policy anon writes on public\.audit allows INSERT on every row for anon\. Scope it: with check \(.*\)\. If only the service role should use it, add to service_role\. If anyone may create a row here on purpose \(a sign-up\), tie it to the caller: with check \(\(select auth\.uid\(\)\) is not null\)\.$/)
assert.match(notes[1].fix, /allows UPDATE on every row for every role\. Scope it: using \(/)
// On a table the contract scopes, the same service_role policy used to be a finding.
const scoped = await access(plus('create policy "svc" on public.invoices for all to service_role using (true) with check (true);'))
assert.deepEqual(scoped.all, [])
const open = await access(plus('create policy "anon all" on public.invoices for update to anon using (true);'))
assert.equal(open.findings[0].code, 'POLICY-ALWAYS-TRUE')
assert.match(open.findings[0].message, /is true for UPDATE, for anon\.$/)
})
test('lints: an ignoreTables table with grants still says RLS-OFF, and the fix says how to clear it', async () => {
const out = await access(plus('create table public.alembic_version (version_num text primary key);'))
const f = out.findings.find((x) => x.object === 'public.alembic_version')
assert.equal(f.code, 'RLS-OFF')
assert.match(f.fix, /^public\.alembic_version is in CONFIG\.ignoreTables, which only quiets the schema gate\./)
assert.match(f.fix, /revoke all on public\.alembic_version from anon, authenticated\), or, if every row is public, add it to CONFIG\.publicTables/)
const revoked = await access(plus('create table public.alembic_version (version_num text primary key);\nrevoke all on public.alembic_version from anon, authenticated;'))
assert.deepEqual(revoked.all, [], 'the fix clears it')
})
// ------------------------------------------------------- monorepo tools ---
const PRISMA_NESTED = (extra = {}) =>
files({ 'apps/api/prisma/schema.prisma': PRISMA_SCHEMA('postgresql'), 'apps/api/node_modules/prisma/package.json': '{ "version": "6.4.1" }', ...extra })
test('monorepo: prisma in the schema package is found there, its major is read there, and it runs from that folder', () => {
const dir = PRISMA_NESTED({ 'apps/api/node_modules/@prisma/engines/schema-engine-x': '' })
stub(dir, 'apps/api/node_modules/.bin/prisma', printing(dir, PRISMA_PG_SQL))
const unit = plan({ kind: 'command', preset: 'prisma', schema: 'apps/api/prisma/schema.prisma' }, dir)
assert.equal(unit.file, join(dir, 'apps/api/node_modules/.bin/prisma'))
assert.equal(unit.cwd, join(dir, 'apps/api'))
assert.deepEqual(unit.args, ['migrate', 'diff', '--from-empty', '--to-schema-datamodel', 'prisma/schema.prisma', '--script'], 'prisma 6 from the nested package, relative to where it runs')
assert.equal(unit.version, '6.4.1')
assert.ok(unit.watch.includes('apps/api/prisma.config.ts'), unit.watch.join())
assert.equal(runCommand(unit), PRISMA_PG_SQL, 'the engine is looked for next to the nested prisma')
assert.deepEqual(calls(dir), [['migrate', 'diff', '--from-empty', '--to-schema-datamodel', 'prisma/schema.prisma', '--script']])
// Hoisted to the root: found by walking up.
const hoisted = files({ 'apps/api/prisma/schema.prisma': PRISMA_SCHEMA('postgresql'), 'node_modules/prisma/package.json': '{ "version": "7.10.0" }' })
stub(hoisted, 'node_modules/.bin/prisma', printing(hoisted, PRISMA_PG_SQL))
const up = plan({ kind: 'command', preset: 'prisma', schema: 'apps/api/prisma/schema.prisma' }, hoisted)
assert.deepEqual([up.file, up.version, up.args[3]], [join(hoisted, 'node_modules/.bin/prisma'), '7.10.0', '--to-schema'])
})
test('monorepo: a missing tool names every folder it looked in, and the one that should have it', () => {
const dir = PRISMA_NESTED()
const unit = plan({ kind: 'command', preset: 'prisma', schema: 'apps/api/prisma/schema.prisma' }, dir)
assert.throws(() => runCommand(unit), (e) => {
const x = e.toJSON()
return x.code === 'COMMAND-FAILED' && x.message === 'prisma is not installed.' &&
x.fix === 'Looked for node_modules/.bin/prisma in apps/api/node_modules/.bin, apps/node_modules/.bin, ./node_modules/.bin. Add prisma as a devDependency of the package that owns the schema (apps/api) and install.'
})
const drizzle = plan({ kind: 'command', preset: 'drizzle', config: 'backend/drizzle.config.ts' }, files({ 'backend/drizzle.config.ts': DRIZZLE_CONFIG('postgresql'), 'backend/src/db/schema.ts': '' }))
assert.throws(() => runCommand(drizzle), (e) => /^Looked for node_modules\/\.bin\/drizzle-kit in backend\/node_modules\/\.bin, \.\/node_modules\/\.bin\. Add drizzle-kit as a devDependency of the package that owns the schema \(backend\)/.test(e.fix))
const alembic = files({ 'backend/alembic.ini': '[alembic]\n', 'backend/alembic/env.py': '' })
const au = planCommand({ kind: 'command', preset: 'alembic', dialect: 'postgres', config: 'backend/alembic.ini' }, 0, { root: alembic, env: { PATH: '/nonexistent' } })
assert.throws(() => runCommand(au), (e) => /^Looked for a \.venv or venv with alembic in backend\/\{\.venv,venv\}\/bin, \.\/\{\.venv,venv\}\/bin, then on PATH\. Install it in the folder that owns alembic\.ini \(backend\)/.test(e.fix))
})
test('monorepo: drizzle runs from the config folder, with the schema paths read from there', () => {
const dir = files({
'backend/drizzle.config.ts': DRIZZLE_CONFIG('postgresql', `'./src/db/schema/*.ts'`),
'backend/src/db/schema/a.ts': '',
'backend/node_modules/drizzle-kit/package.json': '{ "version": "0.31.11" }',
})
stub(dir, 'backend/node_modules/.bin/drizzle-kit', printing(dir, 'create table a (id integer);'))
const unit = plan({ kind: 'command', preset: 'drizzle', config: 'backend/drizzle.config.ts' }, dir)
assert.deepEqual([unit.file, unit.cwd, unit.args, unit.version], [join(dir, 'backend/node_modules/.bin/drizzle-kit'), join(dir, 'backend'), ['export', '--config', 'drizzle.config.ts'], '0.31.11'])
assert.deepEqual(unit.watch, ['backend/drizzle.config.ts', 'backend/src/db/schema/'])
assert.equal(runCommand(unit), 'create table a (id integer);')
})
test('monorepo: alembic reads its ini where it is, runs from there, and finds a .venv in that folder or above', () => {
const dir = files({ 'backend/alembic.ini': '[alembic]\nscript_location = %(here)s/migrations\n', 'backend/migrations/env.py': '' })
stub(dir, '.venv/bin/alembic', printing(dir, 'create table a (id integer);'))
const unit = planCommand({ kind: 'command', preset: 'alembic', dialect: 'postgres', config: 'backend/alembic.ini' }, 0, { root: dir, env: cleanEnv() })
assert.deepEqual([unit.cwd, unit.args, unit.watch], [join(dir, 'backend'), ['upgrade', 'head', '--sql'], ['backend/migrations/', 'backend/alembic.ini']])
assert.ok(unit.env.PATH.startsWith(`${join(dir, '.venv', 'bin')}:`), 'the .venv one folder up')
stub(dir, 'backend/venv/bin/alembic', printing(dir, 'create table a (id integer);'))
const near = planCommand({ kind: 'command', preset: 'alembic', dialect: 'postgres', config: 'backend/alembic.ini' }, 0, { root: dir, env: cleanEnv() })
assert.ok(near.env.PATH.startsWith(`${join(dir, 'backend', 'venv', 'bin')}:`), 'the nearer venv wins')
const named = files({ 'alembic.prod.ini': '[alembic]\n', 'alembic/env.py': '' })
assert.deepEqual(planCommand({ kind: 'command', preset: 'alembic', dialect: 'postgres', config: 'alembic.prod.ini' }, 0, { root: named, env: cleanEnv() }).args, ['-c', 'alembic.prod.ini', 'upgrade', 'head', '--sql'])
})
// -------------------------------------------------------- drizzle env ---
test('drizzle: DATABASE_URL and the variables the config reads are set to stand-ins, replacing any real value', () => {
const config = `import { defineConfig } from 'drizzle-kit'
if (!process.env.DATABASE_URL) throw new Error('DATABASE_URL is required')
export default defineConfig({
dialect: 'postgresql',
schema: './src/db/schema.ts',
dbCredentials: { host: process.env.DB_HOST!, port: Number(process.env['DB_PORT']), user: env.DB_USER, password: process.env.DB_PASSWORD!, url: process.env.DATABASE_URL },
other: process.env.SOME_FLAG,
})
`
const dir = files({ 'drizzle.config.ts': config, 'src/db/schema.ts': '' })
const real = 'postgresql://prod:secret@db.example.com/app'
const unit = planCommand({ kind: 'command', preset: 'drizzle' }, 0, { root: dir, env: cleanEnv({ DATABASE_URL: real, SOME_FLAG: 'keep' }) })
const stand = 'postgresql://ntent:ntent@127.0.0.1:1/ntent'
assert.deepEqual(
[unit.env.DATABASE_URL, unit.env.DB_HOST, unit.env.DB_PORT, unit.env.DB_USER, unit.env.DB_PASSWORD, unit.env.SOME_FLAG],
[stand, '127.0.0.1', '5432', 'ntent', 'ntent', 'keep'],
)
assert.ok(!Object.values(unit.env).includes(real), 'a real value never reaches the tool')
const lite = planCommand({ kind: 'command', preset: 'drizzle' }, 0, { root: files({ 'drizzle.config.ts': DRIZZLE_CONFIG('sqlite'), 'src/db/schema.ts': '' }), env: cleanEnv() })
assert.equal(lite.env.DATABASE_URL, 'file:./ntent.db')
assert.equal(placeholderFor('TURSO_DATABASE_URL', 'sqlite'), 'file:./ntent.db')
assert.equal(placeholderFor('TURSO_AUTH_TOKEN', 'sqlite'), 'ntent')
assert.equal(placeholderFor('NEXT_PUBLIC_APP_URL', 'postgres'), null, 'not a database variable')
assert.equal(placeholderFor('STRIPE_SECRET_KEY', 'postgres'), null)
})
// ----------------------------------------------------- alembic offline ---
const ALEMBIC_BINDS = `from alembic import op
import sqlalchemy as sa
def upgrade():
conn = op.get_bind()
conn.execute(
sa.text("INSERT INTO plans (code) VALUES (:code)"),
{"code": "free"},
)
# op.get_bind().execute(sa.text("x"), {"a": 1})
`
const alembicRepo = (versions, stdout) => {
const dir = files({ 'alembic.ini': '[alembic]\nscript_location = alembic\n', 'alembic/env.py': '', ...Object.fromEntries(Object.entries(versions).map(([k, v]) => [`alembic/versions/${k}`, v])) })
stub(dir, '.venv/bin/alembic', printing(dir, stdout))
return planCommand({ kind: 'command', preset: 'alembic', dialect: 'postgres' }, 0, { root: dir, env: cleanEnv() })
}
test('alembic offline: a migration that binds a parameter prints NULL, and the finding names the file, the line and the fix', () => {
const unit = alembicRepo({ '0001_plans.py': ALEMBIC_BINDS }, 'CREATE TABLE plans (code text NOT NULL);\nINSERT INTO plans (code) VALUES (NULL);\n')
assert.throws(() => runCommand(unit), (e) => {
const x = e.toJSON()
return x.code === 'COMMAND-FAILED' &&
/^alembic printed NULL where a migration binds a parameter \(alembic\/versions\/0001_plans\.py:6\)\./.test(x.message) &&
/bindparams\(code='x'\) with context\.configure\(literal_binds=True\)/.test(x.fix) && /run: \['\.venv\/bin\/python'/.test(x.fix) && /watch: \['alembic\/'\]/.test(x.fix)
})
// Green: the same migration, with nothing printed as NULL, loads; and so does a repo with no binding.
assert.match(runCommand(alembicRepo({ '0001_plans.py': ALEMBIC_BINDS }, "INSERT INTO plans (code) VALUES ('free');\n")), /'free'/)
assert.match(runCommand(alembicRepo({ '0001.py': 'def upgrade():\n pass\n' }, 'INSERT INTO t (a) VALUES (NULL);\n')), /NULL/, 'no binding in the migrations: NULL is the SQL\'s own')
const uses = alembicBindUses(join(unit.base, 'alembic'))
assert.deepEqual(uses.map((u) => [u.line, u.binds]), [[6, true]], 'a commented-out call is not a use')
})
test("alembic offline: execute() returns None is the get_bind hint, found by the AttributeError and not only the word", () => {
const unit = alembicRepo({ '0002_data.py': ALEMBIC_BINDS }, '')
stub(unit.base, '.venv/bin/alembic', { stderr: "Traceback (most recent call last):\n File \"alembic/versions/0002_data.py\", line 9, in upgrade\nAttributeError: 'NoneType' object has no attribute 'fetchone'\n", exit: 1 })
assert.throws(() => runCommand(unit), (e) => e.code === 'COMMAND-FAILED' && /execute\(\) returns None there\. Found in alembic\/versions\/0002_data\.py:6\. Point sources at SQL files instead/.test(e.fix))
stub(unit.base, '.venv/bin/alembic', { stderr: 'ValueError: something else\n', exit: 1 })
assert.throws(() => runCommand(unit), (e) => /Run the command by hand/.test(e.fix), 'any other failure keeps the generic fix')
})
test('alembic offline: app logging on stdout never reaches the loader', () => {
const banner = "2026-10-01 12:00:00 [info ] Settings initialized env=dev\n{\"event\": \"db ready\", \"level\": \"info\"}\nBEGIN;\n\nCREATE TABLE alembic_version (v varchar(32) NOT NULL);\n\n[info] migrating 0001\n-- Running upgrade -> 0001\n\nCREATE TABLE a (id integer);\nINFO:app:done\nCOMMIT;\n"
const sql = runCommand(alembicRepo({ '0001.py': 'def upgrade():\n pass\n' }, banner))
assert.equal(sql, 'BEGIN;\n\nCREATE TABLE alembic_version (v varchar(32) NOT NULL);\n\n-- Running upgrade -> 0001\n\nCREATE TABLE a (id integer);\nCOMMIT;\n')
// A plain banner with no timestamp is dropped before the first statement.
assert.equal(stripNonSql('Settings initialized\nBEGIN;\nCREATE TABLE a (id int);\n').sql, 'BEGIN;\nCREATE TABLE a (id int);\n')
assert.equal(stripNonSql('Settings initialized\nBEGIN;\n').dropped, 1)
// SQL that merely looks like a log line stays: a function body, and a multi-line statement.
const body = "CREATE FUNCTION f() RETURNS text AS $$\nBEGIN\n[info] not a log, a body\n2026-01-01 00:00 is a string here\nRETURN 'x';\nEND;\n$$ LANGUAGE plpgsql;\n"
assert.equal(stripNonSql(body).sql, body)
assert.equal(stripNonSql(body).dropped, 0)
assert.equal(stripNonSql('-- only a comment\n').sql, '-- only a comment\n')
})
// -------------------------------------------------------- engine fix ---
test('PRISMA-ENGINE-MISSING: the fix is the one this repo\'s package manager reads', () => {
const fix = (map, from) => engineFix(files(map), from)
const p11 = fix({ 'package.json': '{"packageManager":"pnpm@11.0.2"}', 'pnpm-lock.yaml': '' })
assert.match(p11, /^This repo uses pnpm 11\. In pnpm-workspace\.yaml add allowBuilds: \{ '@prisma\/engines': true, prisma: true \}, then pnpm install\. If pnpm says 'Already up to date', run pnpm rebuild @prisma\/engines prisma, or delete node_modules/)
assert.doesNotMatch(p11, /onlyBuiltDependencies/, 'pnpm 11 ignores it')
const p10 = fix({ 'package.json': '{"packageManager":"pnpm@10.4.0"}' })
assert.match(p10, /^This repo uses pnpm 10\. Add @prisma\/engines and prisma to pnpm\.onlyBuiltDependencies in package\.json, then pnpm install\./)
assert.match(fix({ 'package.json': '{"pnpm":{"onlyBuiltDependencies":["esbuild"]}}', 'pnpm-lock.yaml': '' }), /^This repo uses pnpm 10\./, 'the settings say 10 when nothing else does')
assert.match(fix({ 'package.json': '{}', 'pnpm-lock.yaml': '', 'pnpm-workspace.yaml': "packages:\n - 'apps/*'\nallowBuilds:\n esbuild: true\n" }), /^This repo uses pnpm 11\./)
const npm = fix({ 'package.json': '{}', 'package-lock.json': '{}' })
assert.match(npm, /^This repo uses npm\. The download runs at install, so it was skipped: run npm rebuild @prisma\/engines prisma, and remove ignore-scripts=true from \.npmrc/)
assert.doesNotMatch(npm, /pnpm/)
assert.match(fix({ 'package.json': '{}', 'yarn.lock': '' }), /^This repo uses yarn\./)
assert.match(fix({ 'package.json': '{}', 'bun.lock': '' }), /^This repo uses bun\. Run bun pm trust/)
// A workspace package: the root's settings and lockfile are found by walking up.
assert.match(fix({ 'package.json': '{"packageManager":"pnpm@11.0.0"}', 'apps/api/package.json': '{}' }, 'apps/api'), /^This repo uses pnpm 11\./)
})
test('PRISMA-ENGINE-MISSING through run(): an npm repo is told npm, and the engine is looked for beside the nested prisma', async () => {
const dir = PRISMA_NESTED({ 'package-lock.json': '{}' })
stub(dir, 'apps/api/node_modules/.bin/prisma', printing(dir, PRISMA_PG_SQL))
const r = await runIn(dir, ['--check', '--json'], { config: configWith({ sources: [{ kind: 'command', preset: 'prisma', schema: 'apps/api/prisma/schema.prisma' }] }) })
assert.equal(r.code, 2)
const u = json(r).untested[0]
assert.deepEqual([u.code, u.object], ['PRISMA-ENGINE-MISSING', 'apps/api/node_modules/@prisma/engines'])
assert.match(u.fix, /^This repo uses npm\./)
assert.deepEqual(calls(dir), [])
})
test('run source through sh: a missing command is named, not sh', () => {
const dir = files({ 'db/schema.sql': 'select 1;' })
const fails = (cmd) => {
try {
runCommand(plan({ kind: 'command', run: ['sh', '-c', cmd], dialect: 'sqlite', watch: ['db/'] }, dir))
} catch (e) {
return e.toJSON()
}
return assert.fail('the command was accepted')
}
const named = fails('ntent-no-such-tool --print')
assert.equal(named.code, 'COMMAND-FAILED')
assert.match(named.message, /^ntent-no-such-tool is not installed\.$/)
assert.doesNotMatch(named.message, /^sh /)
assert.match(fails('echo hi >&2; exit 127').message, /^the command exited 127 \(command not found\)\.\nhi/, 'no name in stderr: say what is known and quote it')
})
test('install root: a tool hoisted above a package install is found, and only when the package has no node_modules', () => {
const top = files({ '.git/HEAD': '', 'node_modules/prisma/package.json': '{ "version": "6.4.1" }', 'pkg/prisma/schema.prisma': PRISMA_SCHEMA('postgresql') })
stub(top, 'node_modules/.bin/prisma', printing(top, PRISMA_PG_SQL))
const root = join(top, 'pkg')
const unit = planCommand({ kind: 'command', preset: 'prisma', schema: 'prisma/schema.prisma' }, 0, { root, env: cleanEnv() })
assert.equal(unit.file, join(top, 'node_modules/.bin/prisma'))
assert.equal(unit.version, '6.4.1')
// The package has its own node_modules: that is the install, and the walk stops there.
mkdirSync(join(root, 'node_modules'))
const own = planCommand({ kind: 'command', preset: 'prisma', schema: 'prisma/schema.prisma' }, 0, { root, env: cleanEnv() })
assert.equal(own.file, join(root, 'node_modules/.bin/prisma'))
})
// ------------------------------------------------------------ anon access ---
// R34: a SELECT policy with an anon branch. The seeder leaves session_id null,
// so only a probe that fills what the policy reads can see the hole.
const SESSION_CHECKS = (using) => `create table public.checks (
id uuid primary key default gen_random_uuid(),
user_id uuid references auth.users(id),
session_id text,
body text not null
);
alter table public.checks enable row level security;
create policy "checks read" on public.checks for select ${using};
create policy "checks write" on public.checks for all to authenticated using (user_id = (select auth.uid())) with check (user_id = (select auth.uid()));
`
const withChecks = (using) => (c) => {
c.entities.push({ id: 'check', table: 'public.checks', owner: 'user_id', fields: [{ name: 'user_id', type: 'id' }, 'session_id', 'body'] })
c.access.matrix.push({ actor: 'customer', entity: 'check', can: ['read'], scope: 'own' })
return c
}
test('anon probe: a policy branch on auth.uid() is null lets anon read rows, and names the policy', async () => {
const out = await access(plus(SESSION_CHECKS('using (auth.uid() = user_id or (auth.uid() is null and session_id is not null))')), {}, { edit: withChecks() })
assert.deepEqual(out.all, ['LEAK-ANON-READ'], JSON.stringify(out.findings))
const leak = out.findings[0]
assert.equal(leak.object, 'public.checks')
assert.match(leak.message, /^anon, with no signed-in user, read 1 row of public\.checks\.\npolicies for SELECT that reach anon: "checks read" using /)
assert.match(leak.message, /session_id IS NOT NULL/)
assert.match(leak.fix, /^Policy "checks read" on public\.checks lets a caller with no user read rows\./)
assert.equal(out.exit, 1)
})
test('anon probe: the same table with the policy limited to the owner is clean', async () => {
const out = await access(plus(SESSION_CHECKS('to authenticated using (auth.uid() = user_id)')), {}, { edit: withChecks() })
assert.deepEqual(out.all, [], JSON.stringify(out.findings))
})
test('anon probe: CONFIG.publicTables waives it, and a stale entry is still named', async () => {
const out = await access(plus(SESSION_CHECKS('using (auth.uid() = user_id or (auth.uid() is null and session_id is not null))')), {}, { edit: withChecks() })
const entry = { 'public.checks': { reason: 'guest quiz results', until: '2027-03-31' } }
const waived = out.report({ publicTables: entry })
assert.deepEqual(waived.all, [])
assert.deepEqual(waived.waived.map((w) => [w.key, w.code]), [['public.checks', 'LEAK-ANON-READ']])
assert.equal(out.report({ publicTables: { 'public.checks': { ...entry['public.checks'], until: '2026-09-01' } } }).all[0], 'LEAK-ANON-READ')
})
test('anon probe: not run when only the server connects', async () => {
const out = await access(plus(SESSION_CHECKS('using (auth.uid() = user_id or (auth.uid() is null and session_id is not null))')), { clientAccess: 'server-only' }, { edit: withChecks() })
assert.ok(!out.all.includes('LEAK-ANON-READ'), JSON.stringify(out.all))
})
// R26: a table nothing maps, readable by anon through an always-true policy.
const AUDIT_LOGS = (policy) => `create table public.audit_logs (id uuid primary key default gen_random_uuid(), action text not null);
alter table public.audit_logs enable row level security;
${policy}`
test('anon lint: an unmapped table anon reads through using (true) is ANON-READ-OPEN', async () => {
const out = await access(plus(AUDIT_LOGS('create policy "Public can read audit logs" on public.audit_logs for select using (true);')))
assert.deepEqual(out.all, ['ANON-READ-OPEN'])
assert.equal(out.findings[0].object, 'public.audit_logs')
assert.match(out.findings[0].message, /^policy "Public can read audit logs" on public\.audit_logs is using \(true\) for every role, and anon can select from it/)
// The policy is named public and is using (true): the contract change first, publicTables as another way to say it, then the leak reading (fix4).
assert.match(out.findings[0].fix, /^This policy looks public on purpose: it is using \(true\)/)
assert.match(out.findings[0].fix, /give a visitor actor .* scope 'all' on public\.audit_logs for read.*or add public\.audit_logs to CONFIG\.publicTables/)
assert.match(out.findings[0].fix, /If it is not, the policy is the leak: scope policy Public can read audit logs/)
})
test('anon lint: to authenticated, publicTables, the contract, or a restrictive policy clear it', async () => {
const none = await access(plus(AUDIT_LOGS('create policy "read" on public.audit_logs for select to authenticated using (true);')))
assert.deepEqual(none.all, [])
const open = await access(plus(AUDIT_LOGS('create policy "Public can read audit logs" on public.audit_logs for select using (true);')))
const waived = open.report({ publicTables: { 'public.audit_logs': { reason: 'public changelog', until: '2027-03-31' } } })
assert.deepEqual(waived.all, [])
assert.equal(waived.waived.length, 1)
const marked = await access(plus(AUDIT_LOGS('create policy "Public can read audit logs" on public.audit_logs for select using (true);')), {}, {
edit: (c) => {
c.actors.push({ id: 'visitor', description: 'Anyone, not signed in.' })
c.entities.push({ id: 'audit_log', table: 'public.audit_logs', fields: ['action'] })
c.access.matrix.push({ actor: 'visitor', entity: 'audit_log', can: ['read'], scope: 'all' })
return c
},
})
assert.deepEqual(marked.all, [])
const restricted = await access(
plus(AUDIT_LOGS(`create policy "read" on public.audit_logs for select using (true);
create policy "no anon" on public.audit_logs as restrictive for select using (auth.uid() is not null);`)),
)
assert.deepEqual(restricted.all, [])
})
test('anon lint: a table with RLS off is still RLS-OFF, not said twice', async () => {
const out = await access(plus('create table public.logs (id int primary key, action text);'))
assert.deepEqual(out.all, ['RLS-OFF'])
})
test('definer view: over tables everyone can read, it is not flagged; over a private one it still is', async () => {
const open = `create table public.hotels (id int primary key, name text);
alter table public.hotels enable row level security;
create policy "read" on public.hotels for select to anon, authenticated using (true);
create view public.hotel_names as select name from public.hotels;`
const quiet = await access(plus(open))
assert.deepEqual(quiet.all, ['ANON-READ-OPEN'], 'the view adds nothing; the table is what is open')
assert.deepEqual(quiet.report({ publicTables: { 'public.hotels': { reason: 'listing data', until: '2027-03-31' } } }).all, [])
const private_ = await access(plus(open.replace('to anon, authenticated using (true)', 'to authenticated using (id > 0)')))
assert.deepEqual(private_.all, ['DEFINER-VIEW'])
const waived = await access(plus(open.replace('to anon, authenticated using (true)', 'to authenticated using (id > 0)')))
assert.deepEqual(
waived.report({ publicTables: { 'public.hotels': { reason: 'listing data', until: '2027-03-31' } } }).all,
[],
'a table marked public makes the view over it public',
)
})
// R20: a definer function that hands back what RLS would hide.
const SECRET_FN = (body, returns = 'text') => `create schema private;
create table private.server_keys (id uuid primary key default gen_random_uuid(), api_key text not null);
create function public.get_server_key(key_id uuid) returns ${returns} language plpgsql security definer as $$
${body}
$$;`
test('definer function: returns a secret from a private table with no caller check is DEFINER-FUNCTION-LEAKS', async () => {
const out = await access(plus(SECRET_FN('begin return (select api_key from private.server_keys where id = key_id); end')))
assert.deepEqual(out.all, ['DEFINER-FUNCTION-LEAKS'])
const f = out.findings[0]
assert.equal(f.object, 'public.get_server_key(key_id uuid)')
assert.match(f.message, /anon, authenticated can call it over RPC\. It returns text read from private\.server_keys \(api_key\), and nothing in its body checks who is calling/)
assert.match(f.fix, /Compare auth\.uid\(\) to the row's owner or tenant inside it, or revoke execute/)
})
test('definer function: a caller check, a boolean result, or no sensitive table stays a note, and says why', async () => {
const note = (out) => out.notes.find((n) => n.code === 'DEFINER-FUNCTION-EXECUTABLE')
const checked = await access(plus(SECRET_FN('begin return (select api_key from private.server_keys where id = key_id and auth.uid() is not null); end')))
assert.deepEqual(checked.all, [])
assert.match(note(checked).message, /Kept as a note: it reads private\.server_keys \(api_key\) but its body checks the caller/)
const bool = await access(plus(SECRET_FN('begin return exists (select 1 from private.server_keys where id = key_id); end', 'boolean')))
assert.deepEqual(bool.all, [])
assert.match(note(bool).message, /Kept as a note: it returns boolean, so it hands back no rows/)
const plain = await access(
plus(`create function public.add_one(n int) returns int language sql security definer as $$ select n + 1 $$;`),
)
assert.deepEqual(plain.all, [])
assert.match(note(plain).message, /Kept as a note: its body names no tenant table, no secret-looking column/)
})
test('definer function: one that reads a tenant table the contract scopes, with no check, is a finding', async () => {
const out = await access(
plus(`create function public.invoice_total(inv uuid) returns numeric language sql security definer as $$ select total_amount from public.invoices where id = inv $$;`),
)
assert.deepEqual(out.all, ['DEFINER-FUNCTION-LEAKS'])
assert.match(out.findings[0].message, /read from public\.invoices, and nothing/)
})
// ------------------------------------------------- round 2: extensions ---
test('preamble: a create extension with no trailing semicolon loads it (pg_trgm, and a quoted uuid-ossp)', async () => {
const sql = 'create table public.a (n text, id uuid default uuid_generate_v4());\ncreate index on public.a using gin (n gin_trgm_ops);'
const { snapshot } = await build(
{ [MIGRATION]: sql },
{
platform: 'postgres',
preamble: [
{ sql: 'create extension if not exists pg_trgm', reason: 'made in the dashboard' },
{ sql: 'create extension if not exists "uuid-ossp"', reason: 'made in the dashboard' },
],
},
)
assert.ok(snapshot.tables['public.a'])
assert.deepEqual(snapshot.loader.extensions, ['pg_trgm', 'uuid-ossp'])
})
test('stubExtensions: vector can be stubbed when pglite-pgvector is not installed', async () => {
// The pgvector package may or may not be installed here; the stub only applies when it is not.
if ((await import('@electric-sql/pglite-pgvector').catch(() => null)) !== null) return
const sql = 'create extension if not exists vector;\ncreate table public.t (id int primary key);'
const refused = await refusal({ [MIGRATION]: sql })
assert.equal(refused.code, 'EXTENSION-UNAVAILABLE')
const entry = { reason: 'no table the checks read has an embedding', until: '2027-01-01' }
const { snapshot } = await build({ [MIGRATION]: sql }, { stubExtensions: { vector: entry } })
assert.deepEqual(snapshot.loader.stubbed, [{ name: 'vector', reason: entry.reason, by: 'config' }])
})
test('extensions: every missing extension is reported in one pass, one problem each', async () => {
const e = await refusal({
[MIGRATION]: 'create extension timescaledb;',
'supabase/migrations/20260102000000_b.sql': 'create extension if not exists timescaledb_toolkit;\ncreate extension postgis;\ncreate extension postgis;',
})
assert.equal(e.code, 'EXTENSION-UNAVAILABLE')
assert.deepEqual(e.problems.map((p) => p.object), ['timescaledb', 'timescaledb_toolkit', 'postgis'])
assert.match(e.problems[1].message, /20260102000000_b\.sql:1 creates extension timescaledb_toolkit/)
assert.ok(e.problems.every((p) => /CONFIG\.stubExtensions/.test(p.fix)))
// The one that was stubbed stays out of the list.
const some = await refusal({ [MIGRATION]: 'create extension timescaledb;\ncreate extension postgis;' }, { stubExtensions: { timescaledb: { reason: 'r', until: '2027-01-01' } } })
assert.equal(some.object, 'postgis')
assert.equal(some.problems, undefined)
})
// ------------------------------------------------------ round 2: loader ---
const problemsOf = (e) => e.problems ?? [e]
test('load errors: the cascade of a function with several arguments is a cascade', async () => {
const e = await refusal({
[MIGRATION]: `create table public.profiles (id uuid primary key);
create function public.f(a uuid, b text) returns table (x text) language sql as $$ select p.nope from public.profiles p $$;
grant execute on function public.f(uuid, text) to authenticated;
grant execute on function public.f(uuid) to authenticated;`,
})
const grants = problemsOf(e).filter((p) => p.statement.startsWith('grant'))
assert.equal(grants.length, 2)
for (const g of grants) {
assert.equal(g.class, 'cascade', g.message)
assert.equal(g.by, `${MIGRATION}:2`)
}
})
test('load errors: a function of a stubbed extension is a PGlite gap, with the skipStatements fix', async () => {
const sql = `create extension if not exists timescaledb;
create table public.m (id int primary key, ts timestamptz not null);
select create_hypertable('m', 'ts');
create table public.n (id int primary key, v nope_type);`
const stub = { timescaledb: { reason: 'hypertables are never read', until: '2027-01-01' } }
const e = await refusal({ [MIGRATION]: sql }, { platform: 'postgres', stubExtensions: stub })
const [fn, type] = problemsOf(e)
assert.equal(fn.class, 'engine')
assert.match(fn.fix, /timescaledb is stubbed/)
assert.match(fn.fix, /CONFIG\.skipStatements/)
assert.equal(type.class, 'engine', 'a type is the same: nothing says it is not the extension')
const { snapshot } = await build({ [MIGRATION]: sql.replace(/\ncreate table public\.n.*$/, '') }, {
platform: 'postgres',
stubExtensions: stub,
skipStatements: { create_hypertable: { reason: 'hypertables are never read', until: '2027-01-01' } },
})
assert.ok(snapshot.tables['public.m'])
})
test('load errors: uuid_generate_v4 with no extension says which one, and where it goes', async () => {
const e = await refusal({ [MIGRATION]: 'create table public.a (id uuid default uuid_generate_v4());' }, { platform: 'postgres' })
assert.equal(e.class, 'outside')
assert.match(e.fix, /uuid-ossp extension/)
assert.match(e.fix, /CONFIG\.preamble/)
})
test('load errors: a duplicate stays a duplicate, also when its table came later', async () => {
const dump = 'supabase/migrations/20260102000000_dump.sql'
const e = await refusal({
[MIGRATION]: 'alter table public.t add constraint t_n_check check (n > 0);',
[dump]: 'create table public.t (id int primary key, n int, constraint t_n_check check (n > 0));',
})
const [dup] = problemsOf(e)
assert.equal(dup.class, 'duplicate')
assert.equal(dup.sqlstate, '42710')
assert.doesNotMatch(dup.fix, /created later/)
})
test('load errors: create or replace that changes the return type is a real error, not an ordering one', async () => {
const e = await refusal({
[MIGRATION]: `create function public.f(a int) returns int language sql as $$ select a $$;
create or replace function public.f(a int) returns table (x int) language sql as $$ select a $$;`,
'supabase/migrations/20260102000000_c.sql': `drop function if exists public.f(int);
create or replace function public.f(a int) returns table (x int) language sql as $$ select a $$;`,
})
assert.equal(e.class, 'real')
assert.equal(e.sqlstate, '42P13')
assert.equal(e.line, 2)
assert.match(e.fix, /drop function if exists public\.f/)
})
test('load errors: a multi-line insert names the line of the row Postgres refused', async () => {
const e = await refusal({
[MIGRATION]: `create table public.t (id int primary key, n int not null);
-- seed
insert into public.t (id, n) values
(1, 1),
(2, 2),
(3, null),
(4, 4);`,
})
assert.equal(e.line, 7)
assert.equal(e.object, `${MIGRATION}:7`)
// A key that already exists is found the same way, and a row that cannot be told apart keeps the statement's line.
const dup = await refusal({ [MIGRATION]: 'create table public.t (id int primary key);\ninsert into public.t (id) values\n (1),\n (2),\n (2);' })
assert.equal(dup.line, 2, 'two rows say (2), so the statement\'s own line stands')
const fk = await refusal({
[MIGRATION]: 'create table public.p (id int primary key);\ncreate table public.c (id int primary key, p int references public.p(id));\ninsert into public.p values (1);\ninsert into public.c values\n (1, 1),\n (2, 9);',
})
assert.equal(fk.line, 6)
})
test('load errors: a statement that cannot fix itself is not retried, so it is never ordering', async () => {
const e = await refusal({ [MIGRATION]: 'create table public.a (id int);\ncreat table public.b (id int);\ncreate table public.c (id int);' })
assert.deepEqual(problemsOf(e).map((p) => p.class), ['real'])
})
test('load errors: thousands of failures do not end in a flood of 54001', async () => {
const config = configWith({ platform: 'postgres' })
const failing = Array.from({ length: 2600 }, (_, i) => `insert into public.nope_${i} values (1);`).join('\n')
const sql = `${failing}\ncreate table public.ok (id int primary key);\ncreate table public.x (id int references public.missing(id));\n`
const prepared = await prepareInputs([{ path: MIGRATION, text: sql, sections: null }], config)
const db = await openDatabase({ config })
let thrown = null
try {
await loadSchema(db, prepared, config)
} catch (e) {
thrown = e
}
assert.ok(thrown instanceof CannotCheck)
const { problems } = thrown.toJSON()
assert.equal(problems.length, 2601)
assert.equal(problems.filter((p) => p.sqlstate === '54001').length, 0)
assert.equal(problems.at(-1).statement.startsWith('create table public.x'), true)
assert.equal(problems.at(-1).class, 'outside')
// And a load that only needs the restart comes back with the tables it made.
const clean = Array.from({ length: 1300 }, (_, i) => `create table public.t${i} (id int primary key);`).join('\n')
const one = await prepareInputs([{ path: MIGRATION, text: `${failing.split('\n').slice(0, 1300).join('\n')}\n${clean}`, sections: null }], config)
const db2 = await openDatabase({ config })
let again = null
try {
await loadSchema(db2, one, config)
} catch (e) {
again = e
}
assert.equal(again.toJSON().problems.length, 1300)
})
test('load errors: PGlite stack depth exhausted mid-load restarts on a fresh database and loads', async () => {
const config = configWith({ platform: 'postgres' })
const db = await openDatabase({ config })
// Poison it the way a long failing load does.
for (let i = 0; i < 2600; i++) await db.exec(`insert into nope_${i} values (1)`).catch(() => {})
const poisoned = await db.exec('create table public.probe (id int primary key)').then(() => false, (e) => e.code === '54001')
assert.equal(poisoned, true, 'the fixture has to poison PGlite, or this test proves nothing')
const prepared = await prepareInputs([{ path: MIGRATION, text: 'create table public.a (id int primary key);\ncreate index a_id on public.a (id);', sections: null }], config)
const loaded = await loadSchema(db, prepared, config)
try {
assert.notEqual(loaded, db, 'a fresh database came back')
assert.equal((await loaded.query("select to_regclass('public.a')::text as t")).rows[0].t, 'a')
} finally {
await loaded.close()
}
})
test('load errors: a 54001 that survives a fresh database is one engine problem, not a list', () => {
const file = { path: MIGRATION }
const failure = (line) => ({
file,
fileIndex: 0,
st: { text: `select deep(${line});`, line },
e: Object.assign(new Error('stack depth limit exceeded'), { code: '54001' }),
})
const { problems } = loadFailed([1, 2, 3, 4].map(failure), [file]).toJSON()
assert.equal(problems.length, 1)
assert.equal(problems[0].class, 'engine')
assert.match(problems[0].message, /3 more statements failed the same way/)
assert.match(problems[0].fix, /CONFIG\.skipStatements/)
})
// ------------------------------------------------------ round 2: seeder ---
test('seeder: an integer tenant key with no membership is a whole number, not the user\'s uuid (R02)', async () => {
const sql = `create table public.merchants (id serial primary key, name text);
create table public.customers (id serial primary key, merchant_id int not null references public.merchants(id));
alter table public.merchants enable row level security;
alter table public.customers enable row level security;
create policy mine on public.customers for all to authenticated
using (merchant_id = (auth.jwt() ->> 'merchant')::int) with check (merchant_id = (auth.jwt() ->> 'merchant')::int);
create policy mine on public.merchants for select to authenticated using (id = (auth.jwt() ->> 'merchant')::int);`
const edit = contractOf({
tenancy: { scope_entity: 'merchant', column: 'merchant_id' },
matrix: [{ actor: 'user', entity: 'customer', can: ['read'], scope: 'tenant' }],
entities: [
{ id: 'merchant', table: 'public.merchants', fields: [{ name: 'id', type: 'id' }] },
{ id: 'customer', table: 'public.customers', fields: [{ name: 'merchant_id', type: 'id', ref: 'merchant' }] },
],
})
clean(await access({ [MIGRATION]: sql }, { actors: { user: { claims: { merchant: '{tenant}' } } } }, { edit }))
})
test('seeder: an integer owner key is a whole number, and a true policy on it is still a leak (R09)', async () => {
const sql = `create table public.users (id serial primary key, name text);
create table public.notes (id serial primary key, user_id int not null references public.users(id), body text);
alter table public.notes enable row level security;
create policy all_read on public.notes for select to authenticated using (true);`
const edit = contractOf({
matrix: [{ actor: 'user', entity: 'note', can: ['read'], scope: 'own' }],
entities: [{ id: 'note', table: 'public.notes', owner: 'user_id', fields: [{ name: 'user_id', type: 'id' }] }],
})
const r = await access({ [MIGRATION]: sql }, {}, { edit })
assert.deepEqual(r.untested, [])
assert.ok(r.all.includes('LEAK-READ'), r.all.join())
})
test('seeder: check (a <> b) on two foreign keys to one table gets two different parents (R15)', async () => {
const sql = `create table public.profiles (id uuid primary key references auth.users(id));
create table public.friend_requests (
id uuid primary key default gen_random_uuid(),
requester_id uuid not null references public.profiles(id),
addressee_id uuid not null references public.profiles(id),
check (requester_id <> addressee_id)
);
alter table public.profiles enable row level security;
alter table public.friend_requests enable row level security;
create policy see on public.profiles for select to authenticated using (true);
create policy mine on public.friend_requests for all to authenticated
using (requester_id = (select auth.uid()) or addressee_id = (select auth.uid()))
with check (requester_id = (select auth.uid()));`
const edit = contractOf({
matrix: [{ actor: 'user', entity: 'friend_request', can: ['read'], scope: 'own' }],
entities: [
{
id: 'friend_request',
table: 'public.friend_requests',
owner: 'requester_id',
fields: [{ name: 'requester_id', type: 'id' }, { name: 'addressee_id', type: 'id' }],
},
],
})
clean(await access({ [MIGRATION]: sql }, {}, { edit }))
})
test('seeder: = any(array[...]::text[]) over varchar literals is read as an IN list (R36)', async () => {
const sql = OWN_SQL(
'kind varchar(20) not null',
"constraint kind_ck check (kind::text = any (array['reserve'::character varying, 'finalize'::character varying]::text[]))",
)
clean(await access({ [MIGRATION]: sql }, {}, { edit: OWN_CONTRACT }))
})
test('seeder: a database error while looking up a seeded row is SEED-FAILED, not a crash that loses the lints (R02)', async () => {
// The owner is a jsonb key, so the lookup of the user's row refuses the id the seeder gives it.
const sql = `create table public.t (owner jsonb primary key, body text);
alter table public.t enable row level security;
create policy own on public.t for all to authenticated using (true);`
const edit = contractOf({
matrix: [{ actor: 'user', entity: 't', can: ['read'], scope: 'own' }],
entities: [{ id: 't', table: 'public.t', owner: 'owner', fields: [{ name: 'owner', type: 'id' }] }],
})
const r = await access({ [MIGRATION]: sql }, {}, { edit })
const failed = r.untested.find((u) => u.code === 'SEED-FAILED')
assert.match(failed.message, /^seeding public\.t: 22P02 /)
assert.match(failed.fix, /CONFIG\.seed\['public\.t'\]/)
})
// ------------------------------------------------ round 2: probe internals ---
test('LEAK-INSERT: an owner column that is itself unique is tried with a third user, and a true check is a leak (R31)', async () => {
const table = (check) => `create table public.resumes (id uuid primary key default gen_random_uuid(), user_id text not null unique, body text);
alter table public.resumes enable row level security;
create policy open on public.resumes for all to authenticated using (user_id = (select auth.uid())::text) with check (${check});`
const edit = contractOf({
matrix: [{ actor: 'user', entity: 'resume', can: ['read'], scope: 'own' }],
entities: [{ id: 'resume', table: 'public.resumes', owner: 'user_id', fields: [{ name: 'user_id', type: 'id' }] }],
})
const leaky = await access({ [MIGRATION]: table('true') }, {}, { edit })
const leak = leaky.findings.find((f) => f.code === 'LEAK-INSERT')
assert.match(leak.message, /third user/)
assert.equal(leaky.notes.some((n) => n.code === 'INSERT-UNPROVEN'), false)
const sound = await access({ [MIGRATION]: table("user_id = (select auth.uid())::text") }, {}, { edit })
clean(sound)
assert.equal(sound.deny.tests[0].probes.P5, 'pass')
})
test('PROBE-ERROR: a trigger that reads several missing columns names them all in one run (R27)', async () => {
const sql = `create table public.profiles (id uuid primary key default gen_random_uuid(), owner uuid not null references auth.users(id), name text);
alter table public.profiles enable row level security;
create policy own on public.profiles for all to authenticated using (owner = (select auth.uid())) with check (owner = (select auth.uid()));
create function public.guard() returns trigger language plpgsql as $$
begin
if old.subscription_status is distinct from new.subscription_status then raise exception 'locked'; end if;
new."next_renewal_date" = old.next_renewal_date;
new.name = old.name;
return new;
end $$;
create trigger profiles_guard before update on public.profiles for each row execute function public.guard();
`
const edit = () => ({
actors: [{ id: 'user' }],
access: { matrix: [{ actor: 'user', entity: 'profile', can: ['read', 'update'], scope: 'own' }] },
entities: [{ id: 'profile', table: 'public.profiles', owner: 'owner', fields: [{ name: 'owner', type: 'id' }, 'name'] }],
})
const r = await access({ [MIGRATION]: sql }, {}, { edit })
const probes = r.untested.filter((u) => u.code === 'PROBE-ERROR')
assert.equal(probes.length, 1)
assert.match(probes[0].message, /The triggers on public\.profiles read next_renewal_date, subscription_status, which the table does not have\./)
assert.match(probes[0].fix, /columns \(next_renewal_date, subscription_status\)/)
assert.match(probes[0].fix, /late: true/)
// A late file that adds them clears it: nothing needs a new concept.
const second = 'supabase/migrations/99999999999999_after.sql'
const fixed = await access(
{ [MIGRATION]: sql, [second]: { late: true, text: 'alter table public.profiles add column if not exists subscription_status text, add column if not exists next_renewal_date date;' } },
{},
{ edit },
)
clean(fixed)
})
test('load errors: a column missing from a table the migrations create says to add a late file, and that file clears it (R27)', async () => {
const sql = 'create table public.profiles (id int primary key);\ncreate view public.pv as select id, views from public.profiles;\n'
const e = await refusal({ [MIGRATION]: sql })
assert.equal(e.class, 'outside')
assert.match(e.fix, /late: true/)
assert.match(e.fix, new RegExp(`${MIGRATION}:1`))
const after = 'supabase/migrations/99999999999999_after.sql'
const { snapshot } = await build({ [MIGRATION]: sql, [after]: { late: true, text: 'alter table public.profiles add column if not exists views int;' } })
assert.ok(snapshot.tables['public.pv'])
// Without the flag the same file still runs last in name order, and the view stays a finding.
const plain = await refusal({ [MIGRATION]: sql, [after]: 'alter table public.profiles add column if not exists views int;' })
assert.equal(plain.class, 'ordering')
})
test('config: sources late must be a boolean', () => {
const problems = validateConfig(configWith({ sources: [{ kind: 'sql', paths: ['a.sql'], late: 'yes' }] }))
assert.ok(problems.some((p) => /late/.test(p.object)))
assert.deepEqual(validateConfig(configWith({ sources: [{ kind: 'sql', paths: ['a.sql'], late: true }] })), [])
})
test('load errors: the outside fix says how an ORM that creates the tables at startup prints its DDL', async () => {
const e = await refusal({ [MIGRATION]: 'alter table public.users add column x int;' })
assert.equal(e.class, 'outside')
assert.match(e.fix, /SQLAlchemy create_all, Prisma db push/)
assert.match(e.fix, /kind: 'command', run: \['python', 'ntent\/base_schema\.py'\]/)
})
test('--json: groups sit beside the rows, and every row keeps its fields and its group tag (bug 13)', async () => {
const load = (n, cls) => ({ code: 'LOAD-FAILED', class: cls, object: `file${n}.sql:${n}`, message: 'boom', fix: 'x' })
const rls = (t) => ({ code: 'RLS-OFF', object: t, message: `${t} has RLS off`, fix: 'enable it' })
const leak = (t) => ({ code: 'LEAK-READ', object: t, table: t, actor: 'b', message: 'read', fix: 'x' })
const findings = [rls('public.a'), rls('public.b'), leak('public.a'), { code: 'FK-UNINDEXED', object: 'public.c', message: 'one', fix: 'f' }]
const untested = [load(1, 'ordering'), load(2, 'ordering'), load(3, 'real')]
const groups = jsonGroups({ findings, untested })
const rlsGroup = groups.find((g) => g.id === 'RLS-OFF:public.a')
assert.deepEqual([rlsGroup.section, rlsGroup.count], ['findings', 3])
assert.deepEqual(rlsGroup.objects.sort(), ['public.a', 'public.b'])
const ordering = groups.find((g) => g.id === 'LOAD-FAILED:ordering')
assert.deepEqual([ordering.section, ordering.count, ordering.objects], ['untested', 2, ['file1.sql:1', 'file2.sql:2']])
assert.ok(groups.some((g) => g.id === 'LOAD-FAILED:real' && g.count === 1))
assert.ok(!groups.some((g) => g.code === 'FK-UNINDEXED'), 'a lone finding is not a group')
})
test('--json: the report carries groups and keeps the old top-level keys', async () => {
const dir = repo()
const r = json(await runIn(dir, ['--json']))
for (const k of ['exit', 'booted', 'cached', 'platform', 'findings', 'untested', 'notes', 'waived']) assert.ok(k in r, k)
assert.ok(Array.isArray(r.groups))
})
// ------------------------------------------------ round 2: candidates ---
test('candidates: only .sql files are listed and counted, with a README, a script and a Dockerfile beside them (R04, R16, R22, R28, R31, R35)', () => {
const small = files({ 'd/schema.sql': 'create table t (id int);', 'd/a.py': 'x', 'd/b.py': 'x', 'd/c.md': 'x' })
const [one] = found(small)
assert.equal(one.note, '1 SQL file')
assert.deepEqual(one.sources, [{ kind: 'sql', paths: ['d/schema.sql'], order: 'name', sections: null }])
// A base schema sorting after its patches: the listed paths hold no other file.
const mixed = files({
'src/db/schema.sql': 'create table a (id int primary key);',
'src/db/client.ts': 'export const db = 1',
'src/db/migration_add_b.sql': 'alter table a add column b int;',
'src/db/migration_add_c.sql': 'alter table a add column c int;',
'src/db/README.md': 'x',
})
const [m] = found(mixed)
assert.deepEqual(m.sources[0].paths, ['src/db/schema.sql', 'src/db/migration_*.sql'])
assert.match(m.note, /^3 SQL files, a base schema first/)
const scripts = files({ 'scripts/001_a.sql': 'create table a (id int);', 'scripts/002_b.sql': 'create table b (id int);', 'scripts/check-backend.mjs': '', 'scripts/check-seo.mjs': '', 'scripts/x.json': '{}' })
assert.equal(found(scripts)[0].note, '2 SQL files')
assert.equal(found(scripts)[0].sources[0].paths[0], 'scripts/*.sql')
const many = files({ 'backend/migrations/001_a.sql': 'create table a (id int);', 'backend/migrations/002_b.sql': 'create table b (id int);', 'backend/migrations/seed.js': '', 'backend/migrations/notes.pdf': '' })
assert.equal(found(many)[0].note, '2 SQL files')
})
test('candidates: a base schema above a patch folder is one listed source with both (R04, R22)', () => {
const dir = files({
'schema.sql': 'create table a (id int primary key);',
'postgres_functions.sql': 'create function f() returns int language sql as $$ select 1 $$;',
'setup.py': 'x',
'Dockerfile': 'x',
'migrations/001_add_b.sql': 'alter table a add column b int;',
'migrations/002_add_c.sql': 'alter table a add column c int;',
})
const list = found(dir)
const both = list.find((c) => c.sources[0].paths.includes('migrations/*.sql') && c.sources[0].paths.includes('schema.sql'))
assert.ok(both, list.map((c) => c.line).join('\n'))
assert.deepEqual(both.sources[0], { kind: 'sql', paths: ['schema.sql', 'migrations/*.sql'], order: 'listed', sections: null })
assert.ok(list.indexOf(both) < list.findIndex((c) => c.line === "sources: [{ kind: 'sql', paths: ['migrations/*.sql'], order: 'name', sections: null }],"), 'above the patches alone')
for (const c of list) for (const p of c.sources[0].paths) assert.match(p, /\.sql$/, 'no path that is not SQL')
// A dump named for the database, not only 'schema'.
const dump = found(files({ 'clento-new-database.sql': 'create table a (id int);', 'migrations/0114_x.sql': 'alter table a add column b int;' }))
assert.deepEqual(dump[0].sources[0].paths, ['clento-new-database.sql', 'migrations/*.sql'])
})
test("candidates: SQLite and D1 SQL carries dialect: 'sqlite' (R01, R19, R21)", () => {
const d1 = found(files({ 'migrations/0001_init.sql': "create table users (id integer primary key, created_at integer default (unixepoch()));\ninsert or ignore into users (id) values (1);" }))
assert.equal(d1[0].sources[0].dialect, 'sqlite')
assert.match(d1[0].line, /dialect: 'sqlite'/)
const drizzle = found(files({ 'apps/server/drizzle/0000_x.sql': 'CREATE TABLE `users` (\n\t`id` text PRIMARY KEY NOT NULL\n);\n--> statement-breakpoint\nCREATE TABLE `s` (`id` text);' }))
assert.equal(drizzle[0].sources[0].dialect, 'sqlite')
const tauri = found(files({ 'src-tauri/migrations/V001__a.sql': 'CREATE TABLE a (id INTEGER PRIMARY KEY AUTOINCREMENT, b BLOB);' }))
assert.equal(tauri[0].sources[0].dialect, 'sqlite')
// Postgres SQL, even with a column called uuid, is left alone.
const pg = found(files({ 'migrations/001.sql': 'create table a (uuid uuid primary key default gen_random_uuid(), at timestamptz);' }))
assert.equal(pg[0].sources[0].dialect, undefined)
// A pasted line loads.
const dir = files({ 'migrations/0001_init.sql': 'create table users (id integer primary key autoincrement, name text);' })
assert.deepEqual(validateConfig(configWith({ sources: found(dir)[0].sources })), [])
})
test('candidates: the server schema ranks above the desktop app database (R01)', () => {
const dir = files({
'src-tauri/migrations/V001__a.sql': 'CREATE TABLE a (id INTEGER PRIMARY KEY AUTOINCREMENT);\n'.repeat(1),
'src-tauri/migrations/V002__b.sql': 'CREATE TABLE b (id INTEGER PRIMARY KEY AUTOINCREMENT);',
'src-tauri/migrations/V003__c.sql': 'CREATE TABLE c (id INTEGER PRIMARY KEY AUTOINCREMENT);',
'src-server/migrations/001_a.sql': 'create table s (id uuid primary key default gen_random_uuid());',
})
const list = found(dir)
assert.equal(list[0].path, 'src-server/migrations')
assert.equal(list[1].path, 'src-tauri/migrations')
assert.match(list[1].note, /desktop or mobile/)
})
test('candidates: after a failed load, the other places SQL lives are named, in --check too (R04, R22)', async () => {
const dir = files({
'schema.sql': 'create table a (id int primary key);',
'migrations/001_add_b.sql': 'alter table a add column b int;',
})
const config = configWith({ platform: 'postgres', sources: [{ kind: 'sql', paths: ['migrations/*.sql'], order: 'name', sections: null }] })
for (const argv of [[], ['--check']]) {
const r = await runIn(dir, argv, { config })
assert.equal(r.code, 2, r.out)
assert.match(r.out, /LOAD-FAILED/)
assert.match(r.out, /did not load\. This repo keeps SQL in other places too/)
assert.match(r.out, /sources: \[\{ kind: 'sql', paths: \['schema\.sql', 'migrations\/\*\.sql'\], order: 'listed', sections: null \}\],/)
assert.doesNotMatch(r.out, /1\. migrations\n/, 'the sources it already read are not offered again')
}
const fix = await runIn(dir, ['--check'], { config: { ...config, sources: found(dir).find((c) => c.sources[0].paths.length === 2).sources } })
assert.equal(fix.code, 0, fix.out)
// A load that works says nothing about candidates.
const ok = await runIn(dir, [], { config: { ...config, sources: found(dir).find((c) => c.sources[0].paths.length === 2).sources } })
assert.doesNotMatch(ok.out, /did not load/)
})
// ------------------------------------------- round 2: platform auto ---
test('platform signals: templates, examples, docs and fixtures are not read, and a weak signal never decides (R18, R36)', () => {
const detect = (map, sql = []) => {
const dir = files(map)
return detectPlatform(dir, scanRepo(dir), sql)
}
const dep = '{"dependencies":{"@supabase/supabase-js":"2"}}'
for (const folder of ['templates/saas-skeleton', 'examples/demo', 'docs', 'test/fixtures/app', 'sample']) {
assert.equal(detect({ [`${folder}/package.json`]: dep, [`${folder}/.env.example`]: 'SUPABASE_URL=x' }).platform, 'postgres', folder)
}
assert.equal(detect({ 'templates/a/supabase/config.toml': 'x' }).platform, 'postgres')
assert.equal(detect({}, [['templates/a/001.sql', 'create policy p on t using (auth.uid() = id);']]).platform, 'postgres', 'SQL in a template')
assert.equal(detect({ 'apps/web/package.json': dep }).platform, 'supabase', 'the live app still counts')
// Peer and optional dependencies.
const peer = detect({ 'package.json': '{"peerDependencies":{"@supabase/supabase-js":"2"},"optionalDependencies":{"@supabase/ssr":"1"}}' })
assert.equal(peer.platform, 'postgres')
assert.match(peer.reasons[0], /Seen and not counted: a @supabase dependency in package\.json \(optional\)/)
assert.equal(detect({ 'package.json': '{"devDependencies":{"@supabase/supabase-js":"2"}}' }).platform, 'supabase')
// Python: an optional extra is weak, a dependency is not.
const extra = detect({ 'packages/backend/pyproject.toml': '[project]\nname = "x"\ndependencies = [\n "asyncpg",\n]\n\n[project.optional-dependencies]\nstorage = [\n "supabase>=2",\n]\n' })
assert.equal(extra.platform, 'postgres')
assert.match(extra.reasons[0], /Seen and not counted: the supabase package in packages\/backend\/pyproject\.toml \(optional\)/)
assert.equal(detect({ 'pyproject.toml': '[project]\ndependencies = [\n "supabase>=2",\n]\n' }).platform, 'supabase')
assert.equal(detect({ 'requirements-dev.txt': 'supabase==2\n' }).platform, 'postgres')
assert.equal(detect({ 'requirements.txt': 'supabase==2\n' }).platform, 'supabase')
})
test('print-config: with no input the platform is read from the best candidate, so pasting it does not flip the answer (R34)', async () => {
const dir = files({
'scripts/001_create_tables.sql': 'create table public.profiles (id uuid primary key references auth.users(id));\ncreate policy p on public.profiles to authenticated using (auth.uid() = id);',
'package.json': '{"dependencies":{"pg":"8"}}',
})
const config = configWith({ platform: 'auto' })
const first = (await runIn(dir, ['--print-config'], { config })).out.split('\n')
assert.equal(first[0], 'platform\tsupabase')
assert.match(first[1], /^platformWhy\tauto: found .* in scripts\/001_create_tables\.sql \(read from candidate 1, scripts\)$/)
const line = first.find((l) => l.startsWith('candidate\t1\t')).split('\t')[4]
assert.match(line, /^sources: /)
const pasted = (await runIn(dir, ['--print-config'], { config: { ...config, sources: discoverSources(dir)[0].sources } })).out.split('\n')
assert.equal(pasted[0], first[0], 'the same answer after the paste')
// A plain repo says plain, both times.
const plain = files({ 'db/schema.sql': 'create table t (id int);' })
const a = (await runIn(plain, ['--print-config'], { config })).out.split('\n')[0]
const b = (await runIn(plain, ['--print-config'], { config: { ...config, sources: discoverSources(plain)[0].sources } })).out.split('\n')[0]
assert.deepEqual([a, b], ['platform\tpostgres', 'platform\tpostgres'])
// A SQLite candidate reads as SQLite before it is pasted.
const lite = files({ 'migrations/0001.sql': 'create table t (id integer primary key autoincrement);' })
assert.equal((await runIn(lite, ['--print-config'], { config })).out.split('\n')[0], 'platform\tsqlite')
})
// ----------------------------------------- round 2: enforcement 'auto' ---
// The green fixture without its RLS or its Supabase parts: plain tables, as an app that checks access in code would have.
const PLAIN_SQL = GREEN_SQL.split('alter table public.customers enable')[0].replace(' references auth.users(id)', '')
const PLAIN_SOURCES = [{ kind: 'sql', paths: ['db/*.sql'], order: 'name', sections: null }]
const plainRepo = (sql = PLAIN_SQL) => files({ 'db/schema.sql': sql, 'contract.json': FIXTURE_CONTRACT })
const plainConfig = (extra = {}) => configWith({ platform: 'postgres', sources: PLAIN_SOURCES, enforcement: 'auto', ...extra })
const AUTO_NOTE = "access is enforced in app code; deny tests skipped; set enforcement: 'app' to confirm."
test("enforcement 'auto': plain Postgres with no RLS is app-code access: exit 0, one note, no deny test, no dead end (R13, R24, R28, R35, R36)", async () => {
const dir = plainRepo()
// No app_user role exists, and no deny test needs one.
const full = await runIn(dir, [], { config: plainConfig() })
assert.equal(full.code, 0, full.out)
assert.ok(full.out.includes(`ACCESS-NOT-CHECKED ${AUTO_NOTE}`), full.out)
assert.doesNotMatch(full.out, /CONFIG-INVALID|LEAK-|NOT-DENY-TESTED/)
assert.match(full.out, /Access NOT checked: enforced in app code\.$/)
assert.match(full.out, /\(an IDOR\).*out of scope here \(roadmap R1\)/)
const check = await runIn(dir, ['--check'], { config: plainConfig() })
assert.equal(check.out, 'backend: 1 migration, 3 tables, access NOT checked: enforced in app code, 0 findings (cached)')
const j = json(await runIn(dir, ['--json'], { config: plainConfig() }))
assert.deepEqual([j.exit, j.findings, j.untested], [0, [], []])
// Confirmed: the same run, with the first sentence gone, and the line still says it.
const sure = await runIn(dir, ['--check'], { config: plainConfig({ enforcement: 'app' }) })
assert.equal(sure.out, 'backend: 1 migration, 3 tables, access NOT checked: enforced in app code, 0 findings (cached)')
assert.doesNotMatch((await runIn(dir, [], { config: plainConfig({ enforcement: 'app' }) })).out, /set enforcement: 'app' to confirm/)
// The schema gate still runs: a field with no column is still a note, a type clash still a finding.
const bad = await runIn(plainRepo(PLAIN_SQL.replace('due_date date not null', 'due_date date')), ['--check'], { config: plainConfig() })
assert.equal(bad.code, 1, bad.out)
})
test("enforcement 'auto': every source type, SQL, a run command, prisma and SQLite", async () => {
const note = (r) => {
assert.equal(r.code, 0, r.out)
assert.match(r.out, /access NOT checked: enforced in app code|Access NOT checked: enforced in app code/)
}
const run = files({ 'db/schema.ts': 'x', 'contract.json': FIXTURE_CONTRACT })
stub(run, 'bin/print-sql', printing(run, PLAIN_SQL))
const viaRun = await runIn(run, ['--check'], { config: plainConfig({ sources: [{ kind: 'command', run: ['bin/print-sql'], dialect: 'postgres', watch: ['db/'] }] }) })
note(viaRun)
assert.match(viaRun.out, /^backend: 1 migration, 3 tables, access NOT checked: enforced in app code, 0 findings$/)
assert.equal(json(await runIn(run, ['--json'], { config: plainConfig({ sources: [{ kind: 'command', run: ['bin/print-sql'], dialect: 'postgres', watch: ['db/'] }] }) })).cached, 'full', 'and the second run reads it from the cache')
const prisma = prismaRepo({ 'node_modules/@prisma/engines/schema-engine-x': '', 'contract.json': FIXTURE_CONTRACT })
stub(prisma, 'node_modules/.bin/prisma', printing(prisma, PLAIN_SQL))
const viaPrisma = await runIn(prisma, [], { config: plainConfig({ sources: [{ kind: 'command', preset: 'prisma' }] }) })
note(viaPrisma)
assert.ok(viaPrisma.out.includes(AUTO_NOTE), viaPrisma.out)
const lite = files({ 'db/schema.sql': SQLITE_GREEN, 'contract.json': JSON.stringify(sqliteContract([{ name: 'number', type: 'text', required: true }, { name: 'due', type: 'date', required: true }])) })
const viaSqlite = await runIn(lite, ['--check'], { config: plainConfig({ sources: sqliteSources }) })
assert.equal(viaSqlite.out, 'backend: 1 migration, 3 tables, access NOT checked: SQLite has no row security, 0 findings')
})
test("enforcement 'auto': RLS anywhere in the schema keeps the deny tests, and Supabase is never read as app code (decision 1)", async () => {
// A table with RLS on: 'auto' is 'rls', so the missing role is still CONFIG-INVALID, and says 'app' is another way out.
const rls = plainRepo(`${PLAIN_SQL}alter table public.customers enable row level security;\ncreate policy own on public.customers for all using (user_id::text = current_setting('app.user_id', true));\n`)
const r = await runIn(rls, ['--check'], { config: plainConfig() })
assert.equal(r.code, 2, r.out)
assert.match(r.out, /CONFIG-INVALID\s+actAs\.role/)
assert.match(r.out, /If this app has no row level security and checks access in app code, set enforcement: 'app'/)
assert.doesNotMatch(r.out, /ACCESS-NOT-CHECKED|access NOT checked: enforced/)
// Supabase with no RLS on an exposed table is the hole: RLS-OFF is a finding, whatever 'auto' says.
const sb = files({ 'supabase/config.toml': 'x', 'supabase/migrations/20260101_a.sql': PLAIN_SQL, 'contract.json': FIXTURE_CONTRACT })
const s = await runIn(sb, ['--check'], { config: configWith({ platform: 'auto', enforcement: 'auto' }) })
assert.equal(s.code, 1, s.out)
assert.match(s.out, /RLS-OFF/)
assert.doesNotMatch(s.out, /enforced in app code/)
const pinned = await runIn(sb, ['--check'], { config: configWith({ platform: 'supabase', enforcement: 'auto' }) })
assert.equal(pinned.code, 1, pinned.out)
// Explicit 'rls' on plain Postgres with no RLS keeps today's behaviour, with the way out named.
const explicit = await runIn(plainRepo(), ['--check'], { config: plainConfig({ enforcement: 'rls' }) })
assert.equal(explicit.code, 2, explicit.out)
assert.match(explicit.out, /CONFIG-INVALID\s+actAs\.role/)
assert.match(explicit.out, /set enforcement: 'app' in CONFIG instead/)
})
test('no hollow pass: a role CONFIG.preamble made up, with no grants, is not green and not "checked"', async () => {
const preamble = [{ sql: 'create role app_user', reason: 'the installer needed a role to act as' }]
// Pinned to 'rls': the user said access is row security.
const r = await runIn(plainRepo(), ['--check'], { config: plainConfig({ enforcement: 'rls', preamble }) })
assert.equal(r.code, 2, r.out)
assert.match(r.out, /ACCESS-NOT-EXERCISED\s+deny tests/)
assert.match(r.out, /none exercised a policy: every probe was denied by a missing grant/)
assert.match(r.out, /set enforcement: 'app' in CONFIG/)
assert.match(r.out, /app_user is created in CONFIG\.preamble, not in your migrations/)
assert.match(r.out, /Access NOT checked: no deny test exercised a policy\.$/)
// 'auto' with RLS on one table and the same invented role: the same.
const some = `${PLAIN_SQL}alter table public.customers enable row level security;\ncreate policy own on public.customers for all using (user_id::text = current_setting('app.user_id', true));\n`
const a = await runIn(plainRepo(some), ['--check'], { config: plainConfig({ preamble }) })
assert.equal(a.code, 2, a.out)
assert.match(a.out, /ACCESS-NOT-EXERCISED/)
// With the grants a real app has, the probes run, and the unscoped tables leak.
const granted = `${some}grant select, insert, update, delete on all tables in schema public to app_user;\n`
const g = await runIn(plainRepo(granted), ['--check'], { config: plainConfig({ preamble }) })
assert.equal(g.code, 1, g.out)
assert.doesNotMatch(g.out, /ACCESS-NOT-EXERCISED/)
// And a real pass says how many probes ran, with no "not checked" tail.
const ok = await runIn(repo(), ['--check'], { config: configWith() })
assert.equal(ok.out, 'backend: 1 migration, 3 tables, 2 deny tests, 0 findings')
})
test('closed by grant is a result when the role is real: the migrations made it, or it is a Supabase role', async () => {
const line = 'access checked: every scoped table is closed to client roles by grants'
// A role the repo's own migration creates, with no grants and RLS on: denied by grant, and meant.
const own = `${PLAIN_SQL}create role app_user;\nalter table public.customers enable row level security;\nalter table public.invoices enable row level security;\nalter table public.payments enable row level security;\n`
const r = await runIn(plainRepo(own), ['--check'], { config: plainConfig() })
assert.equal(r.code, 0, r.out)
assert.equal(r.out, `backend: 1 migration, 3 tables, 2 deny tests, ${line}, 0 findings`)
assert.match((await runIn(plainRepo(own), [], { config: plainConfig() })).out, /Access checked: every scoped table is closed to client roles by grants\.$/)
// Supabase: authenticated has no grants, RLS is on everywhere.
const sb = await runIn(repo(), ['--check'], { config: configWith({ supabase: { defaultGrants: false } }) })
assert.equal(sb.code, 0, sb.out)
assert.ok(sb.out.includes(line), sb.out)
// The same repo with a role made up in the preamble is the hollow case.
const invented = await runIn(plainRepo(own.replace('create role app_user;\n', '')), ['--check'], { config: plainConfig({ preamble: [{ sql: 'create role app_user', reason: 'x' }] }) })
assert.equal(invented.code, 2, invented.out)
assert.match(invented.out, /ACCESS-NOT-EXERCISED/)
})
test("print-config: enforcement says what 'auto' will do, for each source and platform", async () => {
const lines = async (dir, config) => Object.fromEntries((await runIn(dir, ['--print-config'], { config })).out.split('\n').map((l) => l.split('\t')).filter((p) => p.length === 2))
const sql = await lines(plainRepo(), plainConfig())
assert.equal(sql.enforcement, 'app')
assert.match(sql.enforcementHint, /^auto: plain Postgres, and no migration enables row level security .* Set enforcement: 'app' in CONFIG to confirm\.$/)
const rls = await lines(repo(), configWith({ platform: 'postgres', enforcement: 'auto' }))
assert.equal(rls.enforcement, 'rls')
assert.equal(rls.enforcementHint, undefined)
const sb = await lines(files({ 'supabase/config.toml': 'x', 'supabase/migrations/1.sql': PLAIN_SQL }), configWith({ platform: 'auto', enforcement: 'auto' }))
assert.equal(sb.enforcement, 'rls', 'Supabase is never app')
assert.equal(sb.enforcementHint, undefined)
const cmd = files({ 'db/schema.ts': 'x' })
stub(cmd, 'bin/print-sql', printing(cmd, PLAIN_SQL))
const viaCmd = await lines(cmd, plainConfig({ sources: [{ kind: 'command', run: ['bin/print-sql'], dialect: 'postgres', watch: ['db/'] }] }))
assert.equal(viaCmd.enforcement, 'auto')
assert.match(viaCmd.enforcementHint, /decided when the schema loads/)
const lite = await lines(files({ 'db/schema.sql': SQLITE_GREEN }), plainConfig({ sources: sqliteSources }))
assert.equal(lite.enforcement, 'app')
const first = await lines(files({ 'db/schema.sql': PLAIN_SQL }), configWith({ platform: 'postgres', enforcement: 'auto' }))
assert.equal(first.enforcement, 'app', 'before any source is pasted, the best candidate is read')
assert.deepEqual(validateConfig(configWith({ enforcement: 'auto' })), [])
assert.equal(validateConfig(configWith({ enforcement: 'maybe' }))[0].fix, "Set 'auto', 'rls' or 'app'.")
})
// ------------------------------------------ round 2: unlisted migration ---
test('per-file sources: a migration beside them that is not listed is named, loud, in --check too (R33)', async () => {
const dir = files({
'supabase/migrations/20250101_a.sql': 'create table public.a (id int primary key);',
'supabase/migrations/20250102_b.sql': 'create table public.b (id int primary key);',
'supabase/migrations/20250103_c.sql': 'create table public.c (id int primary key);',
'supabase/migrations/20250103_c.down.sql': 'drop table public.c;',
'supabase/migrations/helper-setup-agent-licenses.sql': "select 'AGENT_EMAIL_HERE';",
})
const listed = [{ kind: 'sql', paths: ['supabase/migrations/20250101_a.sql', 'supabase/migrations/20250102_b.sql'], order: 'listed', sections: null }]
for (const argv of [[], ['--check']]) {
const r = await runIn(dir, argv, { config: configWith({ sources: listed }) })
assert.match(r.out, /note: supabase\/migrations\/20250103_c\.sql sits beside/, r.out)
assert.match(r.out, /supabase\/migrations\/20250103_c\.sql sits beside migrations that CONFIG\.sources lists one file at a time, and is not loaded\./)
assert.doesNotMatch(r.out, /down\.sql|helper-setup/, 'a down file and a helper are not migrations')
}
// A glob, a folder, or every file listed: nothing to say.
for (const sources of [
[{ kind: 'sql', paths: ['supabase/migrations/*.sql'], order: 'name', sections: null }],
[{ kind: 'sql', paths: [...listed[0].paths, 'supabase/migrations/20250103_c.sql'], order: 'listed', sections: null }],
]) assert.doesNotMatch((await runIn(dir, ['--check'], { config: configWith({ sources }) })).out, /sits beside/)
})
// ------------------------------------------------- round 3: lints and fixes ---
test('definer function: a helper that checks the caller, one or two levels down, keeps it a note (R15)', async () => {
const note = (out) => out.notes.find((n) => n.code === 'DEFINER-FUNCTION-EXECUTABLE')
const keys = `create schema private;
create table private.server_keys (id uuid primary key default gen_random_uuid(), api_key text not null);
create function public.reads_key(k uuid) returns text language plpgsql security definer as $$
begin
if not public.is_group_member(k) then raise exception 'no'; end if;
return (select api_key from private.server_keys where id = k);
end $$;`
const direct = `create function public.is_group_member(g uuid) returns boolean language sql security definer as $$ select g = auth.uid() $$;`
const viaOther = `create function public.who() returns uuid language sql as $$ select auth.uid() $$;
create function public.is_group_member(g uuid) returns boolean language sql security definer as $$ select g = public.who() $$;`
const careless = `create function public.is_group_member(g uuid) returns boolean language sql security definer as $$ select true $$;`
for (const helper of [direct, viaOther]) {
const out = await access(plus(`${keys}\n${helper}`))
assert.ok(!out.all.includes('DEFINER-FUNCTION-LEAKS'), out.all.join(','))
const n = out.notes.find((x) => x.object.startsWith('public.reads_key'))
assert.match(n.message, /its body calls is_group_member\(\), which checks the caller/)
}
const bad = await access(plus(`${keys}\n${careless}`))
assert.deepEqual(bad.findings.map((f) => [f.code, f.object]), [['DEFINER-FUNCTION-LEAKS', 'public.reads_key(k uuid)']], 'a helper that checks nobody is no check')
assert.ok(note(bad))
})
test('definer function: one that returns an integer or a count is a note, and one that returns rows is still a finding (R15)', async () => {
const fn = (returns, body) => `create schema private;
create table private.jobs (id uuid primary key default gen_random_uuid(), api_key text);
create function public.run_jobs() returns ${returns} language plpgsql security definer as $$ begin ${body} end $$;`
const count = await access(plus(fn('integer', 'return (select count(*) from private.jobs);')))
assert.ok(!count.all.includes('DEFINER-FUNCTION-LEAKS'), count.all.join(','))
assert.match(count.notes.find((n) => n.object.startsWith('public.run_jobs')).message, /it returns integer, so it hands back no rows/)
const rows = await access(plus(fn('text', 'return (select api_key from private.jobs limit 1);')))
assert.deepEqual(rows.all, ['DEFINER-FUNCTION-LEAKS'])
})
const OWN_TEXT = (policy) => `create table public.resumes (id uuid primary key default gen_random_uuid(), user_id text not null, body text);
alter table public.resumes enable row level security;
${policy}`
const OWN_TEXT_EDIT = contractOf({
matrix: [{ actor: 'user', entity: 'resume', can: ['read', 'create'], scope: 'own' }],
entities: [{ id: 'resume', table: 'public.resumes', owner: 'user_id', fields: [{ name: 'user_id', type: 'id' }] }],
})
test('fix text: a text owner column (a Clerk id) is compared with auth.jwt()->>sub, never auth.uid() (R31)', async () => {
const out = await access({ [MIGRATION]: OWN_TEXT('create policy open on public.resumes for all to authenticated using (true) with check (true);') }, {}, { edit: OWN_TEXT_EDIT })
const fixes = out.findings.filter((f) => ['LEAK-READ', 'LEAK-UPDATE', 'LEAK-DELETE', 'LEAK-INSERT', 'SCOPED-READ-TRUE', 'POLICY-ALWAYS-TRUE'].includes(f.code)).map((f) => f.fix)
assert.ok(fixes.length >= 4, out.all.join(','))
for (const fix of fixes) assert.doesNotMatch(fix, /auth\.uid\(\)/, fix)
assert.match(out.findings.find((f) => f.code === 'LEAK-READ').fix, /user_id = \(select auth\.jwt\(\)->>'sub'\)/)
})
test('fix text: a membership LEAK-INSERT says to check the team, not only the user (R20)', async () => {
const sql = `${TEAMS_SQL}create policy "anyone joins" on public.members for insert to authenticated with check (true);\n`
const r = await access({ [MIGRATION]: sql }, {}, { edit: TEAMS_CONTRACT })
const leak = r.findings.find((f) => f.code === 'LEAK-INSERT')
assert.match(leak.fix, /with check \(team_id in \(select team_id from public\.members where user_id = auth\.uid\(\)\)\)/)
assert.match(leak.fix, /lets anyone join any team, in any role/)
assert.doesNotMatch(leak.fix, /^scope the INSERT/)
})
test('LEAK-INSERT: a check on the user alone lets A into B\'s team, and a membership check does not (R20, R15)', async () => {
const own = `create policy "join" on public.members for insert to authenticated with check (user_id = (select auth.uid()));\n`
const leaky = await access({ [MIGRATION]: TEAMS_SQL + own }, {}, { edit: TEAMS_CONTRACT })
const leak = leaky.findings.find((f) => f.code === 'LEAK-INSERT')
assert.equal(leak?.table, 'public.members', leaky.all.join(','))
assert.match(leak.message, /with their own user as its owner/)
const admin = `create policy "join" on public.members for insert to authenticated
with check (team_id in (select team_id from public.members m where m.user_id = (select auth.uid())));\n`
const sound = await access({ [MIGRATION]: TEAMS_SQL + admin }, {}, { edit: TEAMS_CONTRACT })
assert.ok(!sound.all.includes('LEAK-INSERT'), sound.all.join(','))
})
test('LEAK-INSERT: a child row whose policy checks only its author, into another team\'s parent, is caught (R15)', async () => {
const sql = (check) => `${TEAMS_SQL}create table public.posts (
id uuid primary key default gen_random_uuid(),
team_id uuid not null references public.teams(id),
author uuid not null references auth.users(id),
body text
);
alter table public.posts enable row level security;
create policy "read mine" on public.posts for select to authenticated
using (team_id in (select team_id from public.members where user_id = (select auth.uid())));
create policy "write" on public.posts for insert to authenticated with check (${check});
`
const edit = () => {
const c = TEAMS_CONTRACT()
c.access.tenancy.column = 'team_id'
c.access.matrix.push({ actor: 'user', entity: 'post', can: ['read', 'create'], scope: 'tenant' })
c.entities.push({ id: 'post', table: 'public.posts', fields: [{ name: 'team_id', type: 'id', ref: 'team' }, 'body'] })
return c
}
const leaky = await access({ [MIGRATION]: sql('author = (select auth.uid())') }, {}, { edit })
assert.ok(leaky.findings.some((f) => f.code === 'LEAK-INSERT' && f.table === 'public.posts'), leaky.all.join(','))
const sound = await access({ [MIGRATION]: sql('author = (select auth.uid()) and team_id in (select team_id from public.members where user_id = (select auth.uid()))') }, {}, { edit })
assert.ok(!sound.findings.some((f) => f.code === 'LEAK-INSERT' && f.table === 'public.posts'), sound.all.join(','))
})
test('OWN-ROW-HIDDEN: with no SELECT policy for the role it says so, and does not blame claims (R02)', async () => {
const sql = `create table public.orders (id uuid primary key default gen_random_uuid(), user_id uuid not null references auth.users(id));
alter table public.orders enable row level security;
create policy srv on public.orders for all to service_role using (true) with check (true);
grant select on public.orders to authenticated;`
const edit = contractOf({
matrix: [{ actor: 'user', entity: 'order', can: ['read'], scope: 'own' }],
entities: [{ id: 'order', table: 'public.orders', owner: 'user_id', fields: [{ name: 'user_id', type: 'id' }] }],
})
const r = await access({ [MIGRATION]: sql }, {}, { edit })
const hidden = r.untested.find((u) => u.code === 'OWN-ROW-HIDDEN')
assert.match(hidden.fix, /No SELECT policy on public\.orders applies to authenticated/)
assert.match(hidden.fix, /every policy is for another role or another command/)
assert.doesNotMatch(hidden.fix, /reads a claim/)
// With a policy that reaches the role, the claims advice stays.
const claims = await access({ [MIGRATION]: sql.replace('to service_role', 'to authenticated').replace('using (true) with check (true)', "using ((auth.jwt()->>'tenant') = 'x')") }, {}, { edit })
assert.match(claims.untested.find((u) => u.code === 'OWN-ROW-HIDDEN').fix, /reads a claim the test does not set/)
})
test('all-roles using (true) on a scoped table: the finding says anyone, and the anon probe reports what it read (R31)', async () => {
const policy = 'create policy "Service role has full access" on public.resumes using (true) with check (true);'
const out = await access({ [MIGRATION]: OWN_TEXT(policy) }, {}, { edit: OWN_TEXT_EDIT })
const lint = out.findings.find((f) => f.code === 'SCOPED-READ-TRUE')
assert.match(lint.message, /for every role, and anon can select from it, so anyone with the public key reads every row, signed in or not/)
assert.match(lint.fix, /lets anyone, signed in or not, read every row/)
assert.ok(out.findings.some((f) => f.code === 'LEAK-ANON-READ' && f.object === 'public.resumes'), out.all.join(','))
// To authenticated only: still the signed-in wording, no anon finding.
const authed = await access({ [MIGRATION]: OWN_TEXT(policy.replace('on public.resumes', 'on public.resumes to authenticated')) }, {}, { edit: OWN_TEXT_EDIT })
assert.match(authed.findings.find((f) => f.code === 'SCOPED-READ-TRUE').fix, /lets any signed-in user read every row/)
assert.ok(!authed.all.includes('LEAK-ANON-READ'))
})
test('every deny test waived: the summary says no deny test ran, not "N deny tests, 0 findings" (R02)', async () => {
const sql = `create table public.orders (id uuid primary key default gen_random_uuid(), user_id uuid not null references auth.users(id));
alter table public.orders enable row level security;
create policy srv on public.orders for all to service_role using (true) with check (true);
grant select on public.orders to authenticated;`
const edit = contractOf({
matrix: [{ actor: 'user', entity: 'order', can: ['read'], scope: 'own' }],
entities: [{ id: 'order', table: 'public.orders', owner: 'user_id', fields: [{ name: 'user_id', type: 'id' }] }],
})
const untested = { 'public.orders as user': { reason: 'only the server reads it', until: '2027-01-01' } }
const base = await access({ [MIGRATION]: sql }, {}, { edit })
const waived = base.report({ untested })
assert.equal(waived.exit, 0, waived.all.join(','))
assert.equal(waived.waived.length, 1)
assert.equal(waived.accessNote, 'access NOT checked: every deny test was waived in CONFIG.untested, so no policy was exercised')
// The summary keys on this note, so the two must stay in step.
assert.equal(denyTestsPart({ denyTests: 1, accessNote: waived.accessNote }), '0 deny tests ran')
// Not waived, it is exit 2 and the summary is the exit-2 one.
assert.equal(base.accessNote, null)
})
// ------------------------------------------------------------------ fix3 ---
test('late retry: a drop ... if exists between two creates runs again, so the second create does not find the first (R27, R32)', async () => {
const one = 'supabase/migrations/001_a.sql'
const two = 'supabase/migrations/002_b.sql'
const late = 'supabase/migrations/999_late.sql'
const { snapshot } = await build({
[one]: 'create table public.t(a int);\ncreate view public.v as select b from public.t;\n',
[two]: 'drop view if exists public.v;\ncreate view public.v as select b, a from public.t;\n',
[late]: { late: true, text: 'alter table public.t add column b int;' },
})
const cols = Object.keys(snapshot.tables['public.v']?.columns ?? {})
assert.deepEqual(cols.sort(), ['a', 'b'], 'the view is the second definition')
})
test('load errors: a duplicate the file already drops first does not say to drop it first', () => {
const file = { path: 'a.sql', text: 'drop view if exists v;\ncreate view v as select 1;\n' }
const st = { text: 'create view v as select 1;', line: 2 }
const e = loadFailed([{ file, fileIndex: 0, st, e: { code: '42P07', message: 'relation "v" already exists' } }], [file]).toJSON()
assert.equal(e.class, 'duplicate')
assert.match(e.fix, /already drops the object first/)
assert.doesNotMatch(e.fix, /make it idempotent/)
})
// ------------------------------------------------------------ load ratchet ---
const BAD_LOAD = 'create table public.t (a int);\ninsert into public.t select * from public.missing_table;\n'
test('load ratchet: warn mode with a failing load is exit 0 with the whole failure and the state line', async () => {
const dir = repo({ sql: BAD_LOAD, contractText: null })
const r = await runIn(dir, ['--check'], { config: configWith({ loadGate: 'warn' }) })
assert.equal(r.code, 0, r.out)
assert.match(r.out, /LOAD-FAILED/)
assert.match(r.out, /missing_table/)
assert.match(r.out, /backend: database NOT checked: the migrations do not load yet \(warn mode\)\. Fix them or add escapes; the first clean load turns this into a gate\./)
assert.doesNotMatch(r.out, /backend: ok/)
const j = json(await runIn(dir, ['--json'], { config: configWith({ loadGate: 'warn' }) }))
assert.equal(j.exit, 0)
assert.match(j.loadWarn, /database NOT checked/)
})
test('load ratchet: gate mode, and a config with no loadGate, block a failing load with exit 2', async () => {
const dir = repo({ sql: BAD_LOAD, contractText: null })
const gate = await runIn(dir, ['--check'], { config: configWith({ loadGate: 'gate' }) })
assert.equal(gate.code, 2, gate.out)
assert.match(gate.out, /LOAD-FAILED/)
const config = configWith()
delete config.loadGate
const missing = await runIn(dir, ['--check'], { config })
assert.equal(missing.code, 2, 'no key fails closed')
})
test('load ratchet: warn mode that loads cleanly is exit 1 with the switch finding, and gate mode is clean', async () => {
const dir = repo({ sql: 'create table public.t (a int);\n', contractText: null })
const warn = await runIn(dir, ['--check'], { config: configWith({ loadGate: 'warn', enforcement: 'app' }) })
assert.equal(warn.code, 1, warn.out)
assert.match(warn.out, /LOAD-GATE-READY/)
assert.match(warn.out, /Set CONFIG\.loadGate to 'gate'/)
const gate = await runIn(dir, ['--check'], { config: configWith({ loadGate: 'gate', enforcement: 'app' }) })
assert.equal(gate.code, 0, gate.out)
})
test('load ratchet: only load failures are softened, so a bad config or contract still exits 2 in warn mode', async () => {
const dir = repo({ sql: BAD_LOAD, contractText: null })
const badConfig = await runIn(dir, ['--check'], { config: configWith({ loadGate: 'warn', exposedSchemas: 'public' }) })
assert.equal(badConfig.code, 2, badConfig.out)
assert.match(badConfig.out, /CONFIG-INVALID/)
writeFileSync(join(dir, 'contract.json'), '{ not json')
const badContract = await runIn(dir, ['--check'], { config: configWith({ loadGate: 'warn' }) })
assert.equal(badContract.code, 2, badContract.out)
assert.match(badContract.out, /CONTRACT-SHAPE/)
})
test('load ratchet: loadGate is validated and --print-config prints it', async () => {
const problems = validateConfig(configWith({ loadGate: 'off' }))
assert.ok(problems.some((p) => p.object === 'loadGate'))
assert.deepEqual(validateConfig(configWith({ loadGate: 'warn' })), [])
const dir = repo({ sql: BAD_LOAD, contractText: null })
assert.match((await runIn(dir, ['--print-config'], { config: configWith({ loadGate: 'warn' }) })).out, /^loadGate\twarn$/m)
assert.match((await runIn(dir, ['--print-config'])).out, /^loadGate\tgate$/m)
})
test('load ratchet: an extension that cannot load is a load failure too, so warn mode does not block it', async () => {
const dir = repo({ sql: 'create extension if not exists postgis;\ncreate table public.t (a int);\n', contractText: null })
const warn = await runIn(dir, ['--check'], { config: configWith({ loadGate: 'warn' }) })
assert.equal(warn.code, 0, warn.out)
assert.match(warn.out, /EXTENSION-UNAVAILABLE/)
assert.match(warn.out, /database NOT checked/)
assert.equal((await runIn(dir, ['--check'], { config: configWith({ loadGate: 'gate' }) })).code, 2)
})
test("candidates: drizzle's statement-breakpoint is not a SQLite sign, and meta/_journal.json names the dialect (R13)", () => {
const sql = 'CREATE TABLE "users" (\n\t"id" serial PRIMARY KEY\n);\n--> statement-breakpoint\nCREATE TABLE "orgs" (\n\t"id" integer PRIMARY KEY\n);\n'
const noJournal = found(files({ 'drizzle/0000_x.sql': sql }))
assert.equal(noJournal[0].sources[0].dialect, undefined, 'a breakpoint alone is not SQLite')
const pg = found(files({ 'drizzle/0000_x.sql': sql, 'drizzle/meta/_journal.json': '{"version":"7","dialect":"postgresql","entries":[]}' }))
assert.equal(pg[0].sources[0].dialect, undefined)
const lite = found(files({ 'drizzle/0000_x.sql': sql, 'drizzle/meta/_journal.json': '{"version":"7","dialect":"sqlite","entries":[]}' }))
assert.equal(lite[0].sources[0].dialect, 'sqlite')
// A Postgres journal beats a backtick.
const tick = found(files({ 'drizzle/0000_x.sql': 'CREATE TABLE `users` (id int);', 'drizzle/meta/_journal.json': '{"dialect":"postgresql"}' }))
assert.equal(tick[0].sources[0].dialect, undefined)
})
test('candidates: a drizzle config with a non-default name is offered, one candidate each (R21)', () => {
const list = found(files({ 'apps/server/drizzle.db.config.ts': 'export default {}', 'apps/server/drizzle.do.config.ts': 'export default {}', 'apps/server/drizzle.config.ts': 'export default {}' }))
const drizzle = list.filter((c) => c.kind === 'drizzle')
assert.deepEqual(drizzle.map((c) => c.path), ['apps/server/drizzle.config.ts', 'apps/server/drizzle.db.config.ts', 'apps/server/drizzle.do.config.ts'])
assert.deepEqual(drizzle[1].sources, [{ kind: 'command', preset: 'drizzle', config: 'apps/server/drizzle.db.config.ts' }])
})
test('platform signals: an env example and a provider adapter package never decide alone (R36)', () => {
const detect = (map, sql = []) => {
const dir = files(map)
return detectPlatform(dir, scanRepo(dir), sql)
}
const r36 = detect({
'.env.example': "AUTH_PROVIDER=supabase # or 'clerk'\nSTORAGE_BACKEND=supabase\n# SUPABASE_URL=https://x.supabase.co\n",
'packages/auth-supabase/package.json': '{"dependencies":{"@supabase/supabase-js":"2"}}',
'packages/backend/pyproject.toml': '[project]\ndependencies = ["alembic"]\n',
})
assert.equal(r36.platform, 'postgres')
assert.equal(r36.weak.length, 2)
// With a real signal beside them, Supabase is still found.
assert.equal(detect({ '.env.example': 'SUPABASE_URL=https://x.supabase.co', 'apps/web/package.json': '{"dependencies":{"@supabase/ssr":"1"}}' }).platform, 'supabase')
assert.equal(detect({ 'packages/db/package.json': '{"dependencies":{"@supabase/supabase-js":"2"}}' }).platform, 'supabase', 'a package that is not a provider adapter counts')
})
test('stubExtensions: the stubbed vector type points at skipStatements, and what hangs off the failed table says one key covers it (R12)', async () => {
if ((await import('@electric-sql/pglite-pgvector').catch(() => null)) !== null) return
const stub = { vector: { reason: 'no table the checks read has an embedding', until: '2027-01-01' } }
const sql = 'create extension if not exists vector;\ncreate table public.docs (id int primary key, e vector(3));\ncreate index docs_e on public.docs using ivfflat (e vector_cosine_ops);\n'
const e = await refusal({ [MIGRATION]: sql }, { stubExtensions: stub })
assert.deepEqual(e.problems.map((p) => p.class), ['engine', 'cascade'])
assert.match(e.problems[0].fix, /CONFIG\.skipStatements/)
// What hangs off the failed table says a key naming the table skips it too.
assert.match(e.problems[1].fix, /PGlite or stubbed-extension gap/)
assert.match(e.problems[1].fix, /CONFIG\.skipStatements/)
})
test('load errors: a forward reference inside one file says Postgres refuses it too, and points at no CONFIG source (R04, R22)', async () => {
const sql = 'create table public.a (id int primary key, b_id int references public.b(id));\ncreate table public.b (id int primary key);\n'
const e = await refusal({ [MIGRATION]: sql })
assert.equal(e.class, 'ordering')
assert.match(e.fix, /Real Postgres refuses this too/)
assert.match(e.fix, /add constraint/)
assert.doesNotMatch(e.fix, /run fine once/)
// Across files the wording no longer promises it runs fine either.
const apart = await refusal({ [MIGRATION]: 'create table public.a (id int primary key, b_id int references public.b(id));', 'supabase/migrations/20260102000000_b.sql': 'create table public.b (id int primary key);' })
assert.equal(apart.class, 'ordering')
assert.match(apart.fix, /Real Postgres refuses these in this order too/)
})
test('seeder: a composite-key row that a trigger made on the parent insert is the row (R15)', async () => {
const out = await access(
plus(`create table public.groups (id uuid primary key default gen_random_uuid(), created_by uuid not null references auth.users(id));
create table public.group_members (
group_id uuid not null references public.groups(id),
user_id uuid not null references auth.users(id),
role text not null default 'member',
primary key (group_id, user_id)
);
create function public.add_owner() returns trigger language plpgsql security definer set search_path = '' as $$
begin
insert into public.group_members (group_id, user_id, role) values (new.id, new.created_by, 'owner');
return new;
end $$;
create trigger groups_owner after insert on public.groups for each row execute function public.add_owner();
alter table public.group_members enable row level security;
create policy "own membership" on public.group_members for all to authenticated using (user_id = auth.uid()) with check (user_id = auth.uid());`),
{},
{
edit: (c) => {
c.entities.push({ id: 'group_member', table: 'public.group_members', owner: 'user_id', fields: [{ name: 'user_id', type: 'id' }, 'role'] })
c.access.matrix.push({ actor: 'customer', entity: 'group_member', can: ['read'], scope: 'own' })
return c
},
},
)
const seeds = out.untested.filter((u) => u.code === 'SEED-FAILED')
assert.deepEqual(seeds, [], JSON.stringify(seeds))
assert.ok(out.deny.tests.some((t) => t.table === 'public.group_members'))
})
test('candidates: a manual helper that raises on a placeholder is never the base schema, and a folder of loose SQL is offered (R33, R15)', () => {
const list = found(files({
'supabase/migrations/20250101_a.sql': 'create table a (id int);',
'supabase/migrations/20250102_b.sql': 'alter table a add column b int;',
'supabase/migrations/helper-setup-agent-licenses.sql': "do $$ begin if 'AGENT_EMAIL_HERE' = 'AGENT_EMAIL_HERE' then raise exception 'set AGENT_EMAIL_HERE first'; end if; end $$;",
}))
for (const c of list) assert.ok(!c.sources[0].paths?.[0]?.includes('helper-setup'), `${c.line} starts with the helper`)
const top = list.find((c) => c.path === 'supabase/migrations')
assert.deepEqual(top.sources[0].paths, ['supabase/migrations/20250101_a.sql', 'supabase/migrations/20250102_b.sql'])
assert.match(top.note, /helper-setup-agent-licenses\.sql left out/)
// R15: loose SQL in a folder that also holds a file called migration.sql is a SQL folder, not a Prisma one.
const loose = found(files({
'supabase/migrations/001_profiles.sql': 'create table profiles (id int);',
'app/supabase/migration.sql': 'alter table profiles add column avatar_url text;',
'app/supabase/social_migration.sql': 'alter table profiles add column x text;',
'app/supabase/events_migration.sql': 'alter table profiles add column y text;',
}))
assert.ok(loose.some((c) => c.path === 'app/supabase' && c.sources[0].paths.includes('app/supabase/*.sql')), loose.map((c) => c.line).join('\n'))
assert.ok(!loose.some((c) => c.sources[0].paths?.includes('app/*/migration.sql')))
// A real Prisma folder is unchanged.
const prisma = found(files({ 'prisma/migrations/20250101_init/migration.sql': 'create table a (id int);', 'prisma/migrations/20250102_x/migration.sql': 'alter table a add column b int;' }))
assert.ok(prisma.some((c) => c.sources[0].paths?.includes('prisma/migrations/*/migration.sql')))
})
test('sqlite: names are shown without public., and a waiver written as shown matches (R21)', async () => {
const contractText = JSON.stringify(sqliteContract([{ name: 'number', type: 'text', required: true }, { name: 'due', type: 'date', required: true }]))
const red = files({ 'db/schema.sql': SQLITE_GREEN.replace('due text not null', 'due text'), 'contract.json': contractText })
const waiver = { reason: 'backfill next sprint', until: '2027-01-01' }
const plain = await runIn(red, ['--json'], { config: configWith({ sources: sqliteSources }) })
assert.ok(json(plain).findings.every((f) => !/public\./.test(`${f.object} ${f.message} ${f.fix}`)), plain.out)
const waived = await runIn(red, ['--check'], { config: configWith({ sources: sqliteSources, schemaWaivers: { 'invoices.due': waiver } }) })
assert.equal(waived.code, 0, waived.out)
assert.match(waived.out, /waived\s+invoices\.due\s+REQUIRED-BUT-NULLABLE/)
})
test('summary: when every deny test was waived the line says 0 deny tests ran, not N', () => {
assert.equal(denyTestsPart({ denyTests: 3, accessNote: null }), '3 deny tests')
assert.equal(denyTestsPart({ denyTests: 3, accessNote: 'access NOT checked: every deny test was waived in CONFIG.untested, so no policy was exercised' }), '0 deny tests ran')
assert.equal(denyTestsPart({ denyTests: 3, accessNote: 'access NOT checked: enforced in app code' }), '3 deny tests')
})
// --- fix4: contract hints ---
const HINT = /This policy looks public on purpose: /
const PUBLIC_OWN_WRITES = `create policy "own insert" on public.resumes for insert to authenticated with check (user_id = (select auth.jwt()->>'sub'));
create policy "own update" on public.resumes for update to authenticated using (user_id = (select auth.jwt()->>'sub')) with check (user_id = (select auth.jwt()->>'sub'));
create policy "own delete" on public.resumes for delete to authenticated using (user_id = (select auth.jwt()->>'sub'));`
const readFix = (out) => out.findings.find((f) => f.code === 'LEAK-READ')?.fix
test('contract hints: a using (true) select policy on an own table keeps the finding and offers the contract change first', async () => {
const sql = OWN_TEXT(`create policy "read all" on public.resumes for select to authenticated using (true);\n${PUBLIC_OWN_WRITES}`)
const out = await access({ [MIGRATION]: sql }, {}, { edit: OWN_TEXT_EDIT })
assert.equal(out.exit, 1)
assert.ok(out.all.includes('LEAK-READ'), out.all.join())
assert.match(readFix(out), HINT)
assert.match(readFix(out), /it is using \(true\)/)
assert.match(readFix(out), /change the contract: give user scope 'all' on resume for read/)
assert.match(readFix(out), /If it is not, the policy is the leak: scope the policy to the owner: using \(/)
const lint = out.findings.find((f) => f.code === 'SCOPED-READ-TRUE')
assert.match(lint.fix, /This policy looks public on purpose: it is using \(true\)/)
})
test('contract hints: a policy named for the public gives the text naming the policy, with a non-trivial using', async () => {
const sql = OWN_TEXT(`create policy "Public profiles are viewable by everyone" on public.resumes for select to authenticated using (length(user_id) > 0);\n${PUBLIC_OWN_WRITES}`)
const out = await access({ [MIGRATION]: sql }, {}, { edit: OWN_TEXT_EDIT })
assert.equal(out.exit, 1)
assert.deepEqual(out.all, ['LEAK-READ'])
assert.match(readFix(out), /its name, "Public profiles are viewable by everyone", says it is for everyone/)
})
test('contract hints: an is_published select policy gives the text naming the column', async () => {
const sql = `create table public.resumes (id uuid primary key default gen_random_uuid(), user_id text not null, is_published boolean not null default true);
alter table public.resumes enable row level security;
create policy "resumes read" on public.resumes for select to authenticated using (is_published = true);
${PUBLIC_OWN_WRITES}`
const out = await access({ [MIGRATION]: sql }, {}, { edit: OWN_TEXT_EDIT })
assert.equal(out.exit, 1)
assert.match(readFix(out), /it filters only on is_published/)
assert.doesNotMatch(readFix(out), /its name/)
})
test('contract hints: an owner-check policy that leaks for another reason gets no hint', async () => {
const sql = OWN_TEXT(`create policy "resumes read" on public.resumes for select to authenticated using (user_id = (select auth.jwt()->>'sub') or length(user_id) > 0);\n${PUBLIC_OWN_WRITES}`)
const out = await access({ [MIGRATION]: sql }, {}, { edit: OWN_TEXT_EDIT })
assert.equal(out.exit, 1)
assert.ok(readFix(out), out.all.join())
assert.doesNotMatch(readFix(out), HINT)
})
test('contract hints: write findings on the same table never get the hint, and the exit code stays 1', async () => {
const sql = OWN_TEXT(`create policy "Public read" on public.resumes for select to authenticated using (true);
create policy "anyone writes" on public.resumes for insert to authenticated with check (true);
create policy "anyone edits" on public.resumes for update to authenticated using (true) with check (true);`)
const out = await access({ [MIGRATION]: sql }, {}, { edit: OWN_TEXT_EDIT })
assert.equal(out.exit, 1)
const writes = out.findings.filter((f) => ['LEAK-INSERT', 'LEAK-UPDATE', 'POLICY-ALWAYS-TRUE'].includes(f.code))
assert.ok(writes.length >= 2, out.all.join())
for (const f of writes) assert.doesNotMatch(f.fix, HINT, f.code)
assert.match(readFix(out), HINT)
})
test('contract hints: anon reading a public by design table says to declare a visitor reader', async () => {
const sql = OWN_TEXT(`create policy "Public profiles are viewable by everyone" on public.resumes for select using (true);\n${PUBLIC_OWN_WRITES}`)
const out = await access({ [MIGRATION]: sql }, {}, { edit: OWN_TEXT_EDIT })
assert.equal(out.exit, 1)
const anon = out.findings.find((f) => f.code === 'LEAK-ANON-READ')
assert.ok(anon, out.all.join())
assert.match(anon.fix, /give a visitor actor \(id 'visitor', or kind 'public'\) scope 'all' on resume for read \(\{ actor: 'visitor', entity: 'resume', can: \['read'\], scope: 'all' \}\)/)
assert.match(anon.fix, /If it is not, the policy is the leak: policy "Public profiles are viewable by everyone" on public\.resumes lets a caller/)
// Declaring the visitor reader clears the anon findings, as the hint says.
const declared = await access({ [MIGRATION]: sql }, {}, {
edit: (c) => {
const base = OWN_TEXT_EDIT(c)
base.actors.push({ id: 'visitor', kind: 'public' })
base.access.matrix.push({ actor: 'visitor', entity: 'resume', can: ['read'], scope: 'all' })
return base
},
})
assert.ok(!declared.all.includes('LEAK-ANON-READ'), declared.all.join())
})
test('contract hints: a policy named "visible to team members" that leaks is a scoped policy, not a public one, so no hint', async () => {
const sql = OWN_TEXT(`create policy "Resumes visible to team members" on public.resumes for select to authenticated using (length(user_id) > 0);\n${PUBLIC_OWN_WRITES}`)
const out = await access({ [MIGRATION]: sql }, {}, { edit: OWN_TEXT_EDIT })
assert.equal(out.exit, 1)
assert.ok(readFix(out), out.all.join())
assert.doesNotMatch(readFix(out), HINT)
})
How to tell it works: node --test passes. Remove the grant pre-check so a permission error on a probe counts as denied, and the missing-grants test fails.
Included in
- Any setup that adds Database, for when it stores data in postgres, sqlite or supabase