Jesse_Chen
231 lines
14 KiB
TypeScript
231 lines
14 KiB
TypeScript
import assert from "node:assert/strict";
|
|
import { readFileSync } from "node:fs";
|
|
import test from "node:test";
|
|
import { PERSONAL_REPORT_FAILURE_CODES } from "../src/lib/personal-report-service-core.ts";
|
|
|
|
const localMigration = readFileSync(
|
|
new URL("../db/migrations/20260806000000_personal_reports.sql", import.meta.url),
|
|
"utf8",
|
|
);
|
|
const supabaseMigration = readFileSync(
|
|
new URL("../supabase/migrations/20260806010000_personal_reports.sql", import.meta.url),
|
|
"utf8",
|
|
);
|
|
const localDocumentV2Migration = readFileSync(
|
|
new URL("../db/migrations/20260814025000_personal_report_document_v2.sql", import.meta.url),
|
|
"utf8",
|
|
);
|
|
const supabaseDocumentV2Migration = readFileSync(
|
|
new URL("../supabase/migrations/20260814025000_personal_report_document_v2.sql", import.meta.url),
|
|
"utf8",
|
|
);
|
|
const localJobsV2Migration = readFileSync(
|
|
new URL("../db/migrations/20260814040000_personal_report_jobs_v2.sql", import.meta.url),
|
|
"utf8",
|
|
);
|
|
const supabaseJobsV2Migration = readFileSync(
|
|
new URL("../supabase/migrations/20260814040000_personal_report_jobs_v2.sql", import.meta.url),
|
|
"utf8",
|
|
);
|
|
|
|
const localMigrations = [
|
|
"20260714000000_local_auth_compatibility.sql",
|
|
"20260720000100_backend_foundation.sql",
|
|
"20260721000100_self_hosted_identity.sql",
|
|
"20260721000200_identity_business_bridge.sql",
|
|
"20260727000000_admin_viewer_identity.sql",
|
|
"20260806000000_personal_reports.sql",
|
|
"20260814025000_personal_report_document_v2.sql",
|
|
"20260814040000_personal_report_jobs_v2.sql",
|
|
];
|
|
const supabaseMigrations = [
|
|
"20260805030000_reconcile_rectification_v4_conversational_turns.sql",
|
|
"20260806010000_personal_reports.sql",
|
|
"20260814025000_personal_report_document_v2.sql",
|
|
"20260814040000_personal_report_jobs_v2.sql",
|
|
];
|
|
|
|
test("migration filenames use unique, correctly ordered 14-digit versions", () => {
|
|
const pattern = /^\d{14}_[a-z0-9_]+\.sql$/;
|
|
assert.match("20260806000000_personal_reports.sql", pattern);
|
|
assert.match("20260806010000_personal_reports.sql", pattern);
|
|
for (const filename of [...localMigrations, ...supabaseMigrations]) {
|
|
assert.match(filename, pattern);
|
|
}
|
|
for (const migrations of [localMigrations, supabaseMigrations]) {
|
|
assert.deepEqual(migrations, [...migrations].sort(), "each migration stream must be ordered");
|
|
const versions = migrations.map((name) => name.split("_")[0]);
|
|
assert.equal(new Set(versions).size, versions.length, "versions must be unique within a migration stream");
|
|
}
|
|
});
|
|
|
|
test("document v2 and durable job migrations are exact db/supabase mirrors", () => {
|
|
assert.equal(localDocumentV2Migration, supabaseDocumentV2Migration);
|
|
assert.equal(localJobsV2Migration, supabaseJobsV2Migration);
|
|
});
|
|
|
|
test("document v2 migration preserves v1 reads and adds explicit product depth", () => {
|
|
for (const sql of [localDocumentV2Migration, supabaseDocumentV2Migration]) {
|
|
assert.match(sql, /add column if not exists skill_name text/);
|
|
assert.match(sql, /add column if not exists skill_version text/);
|
|
assert.match(sql, /add column if not exists depth text not null default 'standard'/);
|
|
assert.match(sql, /check \(depth in \('concise', 'standard', 'deep', 'research'\)\)/);
|
|
assert.match(sql, /check \(schema_version in \('report_document\.v1', 'report_document\.v2'\)\)/);
|
|
}
|
|
});
|
|
|
|
test("durable job migration defines the complete state, lease, retry, and identity contract", () => {
|
|
for (const sql of [localJobsV2Migration, supabaseJobsV2Migration]) {
|
|
assert.match(sql, /create table if not exists public\.personal_report_jobs/);
|
|
assert.match(sql, /status text not null default 'queued'[\s\S]*'queued',[\s\S]*'running',[\s\S]*'suspended',[\s\S]*'retrying',[\s\S]*'ready',[\s\S]*'failed',[\s\S]*'cancelled'/);
|
|
assert.match(sql, /foreign key \(user_id, request_id\)[\s\S]*references public\.personal_reports \(user_id, request_id\)[\s\S]*on delete cascade/);
|
|
assert.match(sql, /unique \(user_id, request_id\)/);
|
|
assert.match(sql, /attempt_count between 0 and max_attempts[\s\S]*max_attempts between 1 and 10/);
|
|
assert.match(sql, /status = 'running'[\s\S]*lease_token is not null[\s\S]*lease_owner is not null[\s\S]*lease_expires_at is not null[\s\S]*heartbeat_at is not null/);
|
|
assert.match(sql, /create unique index if not exists personal_report_jobs_one_active_per_user[\s\S]*where status in \('queued', 'running', 'suspended', 'retrying'\)/);
|
|
assert.match(sql, /personal_report_job_terminal_immutable/);
|
|
assert.match(sql, /personal_report_job_identity_immutable/);
|
|
assert.match(sql, /personal_report_job_invalid_transition:%s:%s/);
|
|
assert.match(sql, /personal_report_job_request_fingerprint_conflict/);
|
|
}
|
|
});
|
|
|
|
test("durable job migration atomically enqueues reports and exposes lease RPCs only to service_role", () => {
|
|
for (const sql of [localJobsV2Migration, supabaseJobsV2Migration]) {
|
|
assert.match(sql, /create trigger personal_reports_90_enqueue_job[\s\S]*after insert on public\.personal_reports/);
|
|
assert.match(sql, /insert into public\.personal_report_jobs[\s\S]*new\.user_id,[\s\S]*new\.request_id,[\s\S]*new\.request_fingerprint/);
|
|
assert.match(sql, /from public\.personal_reports as report[\s\S]*where report\.status = 'generating'[\s\S]*on conflict \(user_id, request_id\) do nothing/);
|
|
assert.match(sql, /create or replace function public\.claim_personal_report_job\(\s*p_worker_id text,\s*p_lease_seconds integer default 60,\s*p_job_id uuid default null\s*\)/);
|
|
assert.match(sql, /for update skip locked/);
|
|
assert.match(sql, /create or replace function public\.heartbeat_personal_report_job\(\s*p_job_id uuid,\s*p_lease_token uuid,\s*p_lease_seconds integer default 60\s*\)/);
|
|
assert.match(sql, /create or replace function public\.complete_personal_report_job\(/);
|
|
assert.match(sql, /v_job\.status <> 'running'[\s\S]*v_job\.lease_token is distinct from p_lease_token[\s\S]*v_job\.lease_expires_at <= v_completed_at/);
|
|
assert.match(sql, /where job\.id = p_job_id[\s\S]*for update;[\s\S]*v_completed_at := clock_timestamp\(\);[\s\S]*v_job\.lease_expires_at <= v_completed_at/);
|
|
assert.match(sql, /update public\.personal_reports[\s\S]*status = 'ready'[\s\S]*update public\.personal_report_jobs[\s\S]*status = 'ready'/);
|
|
assert.match(sql, /create or replace function public\.fail_personal_report_job\(/);
|
|
assert.match(sql, /v_job\.status <> 'running'[\s\S]*v_job\.lease_token is distinct from p_lease_token[\s\S]*v_job\.lease_expires_at <= v_failed_at/);
|
|
assert.match(sql, /where job\.id = p_job_id[\s\S]*for update;[\s\S]*v_failed_at := clock_timestamp\(\);[\s\S]*v_job\.lease_expires_at <= v_failed_at/);
|
|
assert.match(sql, /update public\.personal_reports[\s\S]*status = 'failed'[\s\S]*update public\.personal_report_jobs[\s\S]*status = 'failed'/);
|
|
assert.match(sql, /p_skill_name is null[\s\S]*p_skill_name !~[\s\S]*p_skill_version is null[\s\S]*p_skill_version !~/);
|
|
assert.match(sql, /create or replace function public\.recover_expired_personal_report_jobs\(\s*p_limit integer default 100\s*\)/);
|
|
assert.match(sql, /when candidates\.report_status = 'ready' then 'ready'[\s\S]*when candidates\.report_status = 'failed' then 'failed'[\s\S]*when job\.attempt_count < job\.max_attempts then 'retrying'[\s\S]*else 'failed'/);
|
|
assert.match(sql, /exhausted_reports as \([\s\S]*update public\.personal_reports[\s\S]*status = 'failed'[\s\S]*failure_code = 'calculation_unavailable'[\s\S]*candidates\.attempt_count >= candidates\.max_attempts/);
|
|
assert.match(sql, /left join exhausted_reports on exhausted_reports\.id = candidates\.report_id/);
|
|
|
|
assert.match(sql, /alter table public\.personal_report_jobs enable row level security/);
|
|
assert.match(sql, /create policy personal_report_jobs_select_own[\s\S]*for select[\s\S]*to authenticated[\s\S]*using \(auth\.uid\(\) = user_id\)/);
|
|
assert.doesNotMatch(sql, /create policy personal_report_jobs_delete_own[\s\S]*for delete/);
|
|
assert.match(sql, /grant select on table public\.personal_report_jobs to authenticated/);
|
|
assert.doesNotMatch(sql, /grant (?:select, )?(?:insert|update|delete)[^;]* on table public\.personal_report_jobs to authenticated/);
|
|
assert.match(sql, /grant select, insert, update, delete on table public\.personal_report_jobs to service_role/);
|
|
assert.match(
|
|
sql,
|
|
/revoke all on function public\.personal_report_enqueue_job_after_insert\(\) from public, anon, authenticated/,
|
|
);
|
|
for (const signature of [
|
|
"claim_personal_report_job(text, integer, uuid)",
|
|
"heartbeat_personal_report_job(uuid, uuid, integer)",
|
|
"complete_personal_report_job(uuid, uuid, uuid, uuid, uuid, text, text, jsonb, text, text, text, text, text, text)",
|
|
"fail_personal_report_job(uuid, uuid, uuid, uuid, uuid, text, text, text)",
|
|
"recover_expired_personal_report_jobs(integer)",
|
|
]) {
|
|
const escaped = signature.replace(/[()]/g, "\\$&");
|
|
assert.match(sql, new RegExp(`revoke all on function public\\.${escaped} from public, anon, authenticated`));
|
|
assert.match(sql, new RegExp(`grant execute on function public\\.${escaped} to service_role`));
|
|
}
|
|
}
|
|
});
|
|
|
|
test("both migrations define the same table shape with request_fingerprint after request_id", () => {
|
|
const parseColumns = (sql: string) => {
|
|
const body = sql.split("create table if not exists public.personal_reports")[1].split(");")[0];
|
|
const columns: string[] = [];
|
|
let depth = 0;
|
|
for (const raw of body.split("\n")) {
|
|
const line = raw.trim();
|
|
if (!line) continue;
|
|
const before = depth;
|
|
for (const ch of line) {
|
|
if (ch === "(") depth += 1;
|
|
if (ch === ")") depth -= 1;
|
|
}
|
|
const first = line.split(/\s+/)[0];
|
|
if (before === 1 && /^[a-z_][a-z0-9_]*$/.test(first) && first !== "check" && first !== "unique") {
|
|
columns.push(first);
|
|
}
|
|
}
|
|
return columns;
|
|
};
|
|
const expected = [
|
|
"id", "user_id", "session_id", "chart_profile_id", "request_id",
|
|
"request_fingerprint", "report_type", "status", "schema_version",
|
|
"presentation_mode", "requested_themes", "report_document",
|
|
"calculation_hash", "evidence_hash", "skill_source_commit",
|
|
"skill_snapshot_sha256", "failure_code", "created_at", "updated_at",
|
|
"completed_at",
|
|
];
|
|
assert.deepEqual(parseColumns(localMigration), expected);
|
|
assert.deepEqual(parseColumns(supabaseMigration), expected);
|
|
});
|
|
|
|
test("request_fingerprint is not null and restricted to sha256 hex in both migrations", () => {
|
|
for (const sql of [localMigration, supabaseMigration]) {
|
|
assert.match(sql, /request_fingerprint text not null check \(request_fingerprint ~ '\^\[0-9a-f\]\{64\}\$'\)/);
|
|
}
|
|
});
|
|
|
|
test("status, report_type, presentation_mode and schema_version checks are identical", () => {
|
|
for (const sql of [localMigration, supabaseMigration]) {
|
|
assert.match(sql, /status text not null check \(status in \('generating', 'ready', 'failed'\)\)/);
|
|
assert.match(sql, /report_type text not null check \(report_type in \('personal_full', 'personal_thematic'\)\)/);
|
|
assert.match(sql, /presentation_mode text not null check \(presentation_mode in \('default', 'research'\)\)/);
|
|
assert.match(sql, /schema_version text not null check \(schema_version = 'report_document\.v1'\)/);
|
|
}
|
|
});
|
|
|
|
test("ready requires document, completion time and both hashes; failed requires failure_code", () => {
|
|
for (const sql of [localMigration, supabaseMigration]) {
|
|
assert.match(sql, /check \(\(status = 'ready'\) = \(report_document is not null\)\)/);
|
|
assert.match(sql, /check \(\(status = 'ready'\) = \(completed_at is not null\)\)/);
|
|
assert.match(sql, /check \(\(status = 'ready'\) = \(calculation_hash is not null\)\)/);
|
|
assert.match(sql, /check \(\(status = 'ready'\) = \(evidence_hash is not null\)\)/);
|
|
assert.match(sql, /check \(\(status = 'failed'\) = \(failure_code is not null\)\)/);
|
|
}
|
|
});
|
|
|
|
test("failure_code enum in SQL matches the service constant exactly", () => {
|
|
for (const sql of [localMigration, supabaseMigration]) {
|
|
const block = sql.match(/failure_code text check \(failure_code in \(([\s\S]+?)\)\)/)?.[1] ?? "";
|
|
const sqlCodes = [...block.matchAll(/'([a-z_]+)'/g)].map((match) => match[1]);
|
|
assert.deepEqual(sqlCodes, [...PERSONAL_REPORT_FAILURE_CODES]);
|
|
}
|
|
});
|
|
|
|
test("both migrations keep the (user_id, request_id) lock and one-generating-per-user index", () => {
|
|
for (const sql of [localMigration, supabaseMigration]) {
|
|
assert.match(sql, /unique \(user_id, request_id\)/);
|
|
assert.match(sql, /create unique index if not exists personal_reports_one_generating_per_user[\s\S]*where status = 'generating'/);
|
|
assert.match(sql, /references auth\.users\(id\) on delete cascade/);
|
|
}
|
|
});
|
|
|
|
test("RLS policies plus explicit owner grants: select/delete only, never insert/update", () => {
|
|
for (const sql of [localMigration, supabaseMigration]) {
|
|
assert.match(sql, /alter table public\.personal_reports enable row level security/);
|
|
assert.match(sql, /create policy personal_reports_select_own[\s\S]*for select[\s\S]*to authenticated[\s\S]*using \(auth\.uid\(\) = user_id\)/);
|
|
assert.match(sql, /create policy personal_reports_delete_own[\s\S]*for delete[\s\S]*to authenticated[\s\S]*using \(auth\.uid\(\) = user_id\)/);
|
|
// Policies alone do not grant privileges: explicit owner grants are required.
|
|
assert.match(sql, /grant select, delete on table public\.personal_reports to authenticated/);
|
|
assert.doesNotMatch(sql, /grant (insert|update) on table public\.personal_reports to authenticated/);
|
|
assert.match(sql, /grant select, insert, update, delete on table public\.personal_reports to service_role/);
|
|
}
|
|
});
|
|
|
|
test("least privilege: anon/public revoked and no direct admin_runtime body access", () => {
|
|
for (const sql of [localMigration, supabaseMigration]) {
|
|
assert.match(sql, /revoke all on table public\.personal_reports from public, anon, authenticated/);
|
|
}
|
|
assert.doesNotMatch(localMigration, /to admin_runtime/);
|
|
assert.doesNotMatch(supabaseMigration, /to admin_runtime/);
|
|
});
|