caelum0x 261ea5dea7 feat: scribase-migrate — one-command migration from Supabase/Neon/Clerk/Resend onto Scribase
Composes Scribase's own shipped APIs (the `scribase` SDK's imports/migrations/
database/environments resources, plus the GoTrue admin API every self-hosted
Supabase migration tool already uses) rather than reinventing a dump format:

- supabase: wraps the real POST /v1/imports one-call migration (Postgres,
  auth.users with hashes, storage).
- neon: pg_dump schema+data as plain SQL, submitted through POST .../migrations
  (environments.connection() is internal-network-only — verified live — so
  this is the only path that actually reaches a Scribase env from outside);
  auto-generates owner-based RLS so the real migration lint's BLOCK on
  RLS-less public tables is satisfied without ever opening a table to every
  user; branches land in branch_<name> schemas.
- clerk: exports users via the real Backend API, creates them via the
  destination's GoTrue admin API with a one-time password + reset flag
  (Clerk never exposes a portable hash on any plan).
- resend: exports sending domains + broadcasts (as templates) to a portable
  JSON bundle; honest about Scribase having no write endpoint for either yet.

Verified live end-to-end against a real local Supabase-shaped Postgres
(Docker, GoTrue-shaped auth.users with a real bcrypt hash) and a real
Scribase project (ereboslab/scribase-migrate-verify-internal): schema+RLS
applied with 0 lint blocks, data row counts matched via a real HTTP query
(2/2 profiles, 3/3 notes), and a real password-grant login returned a real
access_token for the migrated user. Full artifacts in
test-fixtures/verification-run/.

docs/migrate-page.md is a ready-to-paste scribase.com/migrate page.
2026-11-17 14:54:36 +03:00

137 lines
5.6 KiB
JavaScript

/**
* Scribase's migration lint BLOCKs any new `public` table that has neither
* RLS enabled nor a policy (verified live against a real environment while
* building this tool — see README "Why RLS is generated"). A raw
* `pg_dump --schema-only` of a Supabase/Neon app has no RLS at all (Neon
* has no RLS concept; many Supabase apps relied on the service key and
* never wrote policies), so a one-command migration either stalls on that
* lint for every table, or — far worse — some tool "fixes" it by writing a
* blanket `using (true)` policy that exposes every row to every signed-in
* user. This module infers a safe, specific policy instead:
*
* 1. A column that is itself a foreign key to `auth.users(id)` -> that row
* belongs to that user: `using (<col> = auth.uid())`.
* 2. A column that is a foreign key to another public table which itself
* has a case-1 owner column (one hop) -> an `exists` policy through that
* table: the common `notes.profile_id -> profiles.id -> auth.users`
* shape.
* 3. No owner column found by either rule -> RLS enabled, zero grants
* (`using (false)`): the service-role key the migration itself used
* still reads/writes the table (service role bypasses RLS entirely);
* no anon/authenticated client can. Logged clearly so the operator
* knows to replace it with a real policy before shipping that table to
* an app.
*
* This is a deliberately conservative heuristic, not a schema-design tool:
* it never widens access past what rule 1/2 can prove, and it says exactly
* which tables it could not infer for.
*/
/**
* @param {import('pg').Client} pgClient connected to the SOURCE database
* @param {string} schema
* @returns {Promise<{ tables: Map<string, { columns: string[], fks: {column: string, refTable: string, refSchema: string, refColumn: string}[] }> }>}
*/
export async function introspectSchema(pgClient, schema = 'public') {
const columnsResult = await pgClient.query(
`select table_name, column_name
from information_schema.columns
where table_schema = $1
order by table_name, ordinal_position`,
[schema],
);
const fkResult = await pgClient.query(
`select
tc.table_name, kcu.column_name,
ccu.table_schema as ref_schema, ccu.table_name as ref_table, ccu.column_name as ref_column
from information_schema.table_constraints tc
join information_schema.key_column_usage kcu
on tc.constraint_name = kcu.constraint_name and tc.table_schema = kcu.table_schema
join information_schema.constraint_column_usage ccu
on tc.constraint_name = ccu.constraint_name
where tc.constraint_type = 'FOREIGN KEY' and tc.table_schema = $1`,
[schema],
);
const tables = new Map();
for (const row of columnsResult.rows) {
const table = tables.get(row.table_name) ?? { columns: [], fks: [] };
table.columns.push(row.column_name);
tables.set(row.table_name, table);
}
for (const row of fkResult.rows) {
const table = tables.get(row.table_name) ?? { columns: [], fks: [] };
table.fks.push({
column: row.column_name,
refSchema: row.ref_schema,
refTable: row.ref_table,
refColumn: row.ref_column,
});
tables.set(row.table_name, table);
}
return { tables };
}
/**
* @param {Map<string, {columns: string[], fks: {column:string,refSchema:string,refTable:string,refColumn:string}[]}>} tables
* @returns {{ sql: string, decisions: {table: string, kind: 'direct'|'join'|'locked', detail: string}[] }}
*/
export function generateRlsStatements(tables) {
const decisions = [];
const statements = [];
const directOwnerColumn = (fks) =>
fks.find((fk) => fk.refSchema === 'auth' && fk.refTable === 'users' && fk.refColumn === 'id');
for (const [name, table] of tables) {
statements.push(`alter table public.${name} enable row level security;`);
const direct = directOwnerColumn(table.fks);
if (direct) {
decisions.push({ table: name, kind: 'direct', detail: `${direct.column} = auth.uid()` });
statements.push(
`create policy "${name}_owner" on public.${name} for all using (${direct.column} = auth.uid()) with check (${direct.column} = auth.uid());`,
);
continue;
}
let joined = null;
for (const fk of table.fks) {
if (fk.refSchema !== 'public') continue;
const target = tables.get(fk.refTable);
if (!target) continue;
const targetOwner = directOwnerColumn(target.fks);
if (targetOwner) {
joined = { via: fk, ownerColumn: targetOwner.column };
break;
}
}
if (joined) {
decisions.push({
table: name,
kind: 'join',
detail: `${joined.via.column} -> public.${joined.via.refTable}.${joined.via.refColumn} -> ${joined.ownerColumn} = auth.uid()`,
});
statements.push(
`create policy "${name}_owner_via_${joined.via.refTable}" on public.${name} for all using (\n` +
` exists (select 1 from public.${joined.via.refTable} t where t.${joined.via.refColumn} = public.${name}.${joined.via.column} and t.${joined.ownerColumn} = auth.uid())\n` +
`) with check (\n` +
` exists (select 1 from public.${joined.via.refTable} t where t.${joined.via.refColumn} = public.${name}.${joined.via.column} and t.${joined.ownerColumn} = auth.uid())\n` +
`);`,
);
continue;
}
decisions.push({
table: name,
kind: 'locked',
detail: 'no owner column found by rule 1 or 2 — locked to the service role only',
});
statements.push(
`create policy "${name}_service_role_only" on public.${name} for all using (false) with check (false);`,
);
}
return { sql: statements.join('\n'), decisions };
}