Thalix VaultDocumentation
Browse documentation
Operators
publishedimplementedEdit this page

Runbook: FOLDERS-01 Root Folder migration rollback

This runbook covers supabase/migrations/20260826060000_folders01_root_folder_cutover.sql (issue #49), the migration that renames the Project domain to Root Folder throughout the database: table/column/index renames, RPC renames, stored-value and JSON-key rewrites in historical audit/revision rows, and dropping every project-named function. Read this before applying the migration to a production database, not after something has already gone wrong.

What this migration does (and does not do)

  • Renames in place. projects -> folders, files.project_id -> files.folder_id, access_grants.project_id -> access_grants.folder_id, project_metadata_audit_events -> folder_metadata_audit_events. ALTER TABLE ... RENAME never touches row data or object OIDs, so every existing row keeps its exact id, every foreign key keeps pointing at the same row, and every existing file remains downloadable byte-for-byte.
  • Rewrites stored vocabulary, not identity. Enum-like text values (access_grants.scope_type, folder_metadata_audit_events.event_type/target_type, file_metadata_revisions.change_type, operation_audit_events.target_type) and JSON keys inside historical metadata/details/ metadata_before/metadata_after blobs (projectId -> folderId, fromProjectId/toProjectId -> fromFolderId/toFolderId, domain: "projects" -> domain: "folders") are rewritten by update statements. Every row's own id, occurred_at / changed_at / created_at, and actor attribution are untouched — only the "what this event was about" fields change vocabulary.
  • Drops old RPCs, adds new ones. discover_projects, create_project, deactivate_project, admin_list_projects, authorize_projects_actor, actor_manages_project, move_file_project, grant_project_access, revoke_project_access, list_project_access, and project_projection are dropped. Their renamed replacements (discover_folders, create_folder, ..., folder_projection) are new database objects with the folder/requested_folder_name vocabulary.
  • Never deletes a row. No DELETE statement appears anywhere in this migration. Every Project (now root folder), file, grant, metadata revision, and audit event that existed before the migration still exists after it, under the same id.

Pre-migration checklist

  1. Take a fresh database backup (or confirm your managed Postgres provider's point-in-time recovery window covers the deploy window) before applying this migration to production, per the existing Local verification practice this repository already follows for every migration.
  2. Run the full local verification loop first: npx supabase start, npx supabase db reset, npx supabase db lint --level error, then bun run test:db (which now includes supabase/tests/database/folders-metadata.test.sql, supabase/tests/database/folders01-migration-cutover.test.sql, and the updated access-delegation.test.sql / files-search-metadata.test.sql / audit-visibility.test.sql suites) against the fully-migrated schema.
  3. Deploy the application code (contracts, backend, routes, CLI) that speaks the new folder vocabulary in the same release as this migration. This is a clean cutover, not a phased rollout: there is no compatibility shim, so an old tv project ... client or an old backend build cannot talk to a database that has already run this migration, and a new tv folder ... client cannot talk to a database that has not yet run it. Coordinate the migration apply and the application deploy to land together.

When to roll back

Roll back only if a problem is discovered before, during, or immediately after this migration applies — before any caller has written data using a folder-only concept that has no project-shaped equivalent (there are none in this migration: it introduces no new columns, only renamed ones, so this condition is automatically satisfied for any window before the next migration lands). If the migration applied cleanly and the application has been serving folder traffic for a while, prefer rolling forward with a fix instead — the same guidance supabase/README.md already gives for identity/auth migrations: "Rollback is operational, not destructive."

Rollback procedure

The sequence below is a review checklist and template, not a paste-ready SQL script. The function and view bodies are intentionally sourced from the last known-good release so rollback cannot silently drift from the deployed schema. Before migration approval, the operator must materialize those bodies into a reviewed, environment-specific transaction, verify that no placeholder remains, and execute the completed transaction atomically. Never run only the structural statements below.


-- 1. Drop the folder-named RPCs and recreate the project-named ones. The
--    bodies are identical to this migration's "before" state (see
--    supabase/migrations/20260826060000_folders01_root_folder_cutover.sql's
--    own history for the exact prior definitions of discover_projects,
--    create_project, deactivate_project, admin_list_projects,
--    authorize_projects_actor, actor_manages_project, move_file_project,
--    grant_project_access, revoke_project_access, list_project_access, and
--    project_projection -- restore each from the migration immediately
--    preceding 20260826060000 in git history, e.g.
--    `git show <pre-FOLDERS-01-commit>:supabase/migrations/...` for the
--    relevant function). Recreate discover_metadata_definitions in the same
--    step, after authorize_projects_actor, because its body depends on that
--    helper.
drop function if exists public.folder_projection(public.folders);
drop function if exists public.discover_folders(text, text);
drop function if exists public.create_folder(text, text, text);
drop function if exists public.deactivate_folder(text, text, text);
drop function if exists public.admin_list_folders(text, text);
drop function if exists public.actor_manages_folder(uuid, uuid, uuid);
drop function if exists public.move_file_folder(text, text, uuid, text, text);
drop function if exists public.grant_folder_access(text, text, text, text, text, text, text);
drop function if exists public.revoke_folder_access(text, text, text, text, text, text);
drop function if exists public.list_folder_access(text, text, text);
drop function if exists public.authorize_folders_actor(text, text, boolean);
-- (recreate the project-named functions here, byte-for-byte matching their
-- pre-migration bodies)

-- 2. Reverse the historical JSON-key/value rewrites.
update public.file_audit_events
  set metadata = (metadata - 'folderId') || jsonb_build_object('projectId', metadata -> 'folderId')
  where metadata ? 'folderId';
update public.file_audit_events
  set metadata = (metadata - 'fromFolderId' - 'toFolderId')
    || jsonb_build_object('fromProjectId', metadata -> 'fromFolderId', 'toProjectId', metadata -> 'toFolderId')
  where metadata ? 'fromFolderId' and metadata ? 'toFolderId';
update public.access_audit_events
  set metadata = (metadata - 'folderId') || jsonb_build_object('projectId', metadata -> 'folderId')
  where metadata ? 'folderId';
update public.operation_audit_events
  set details = (details - 'folderId') || jsonb_build_object('projectId', details -> 'folderId')
  where details ? 'folderId';
update public.operation_audit_events
  set details = (details - 'folder') || jsonb_build_object('project', details -> 'folder')
  where details ? 'folder';
update public.operation_audit_events
  set details = details || jsonb_build_object('domain', 'projects')
  where details ->> 'domain' = 'folders';
update public.file_metadata_revisions
  set
    metadata_before = case when metadata_before ? 'folderId'
      then (metadata_before - 'folderId') || jsonb_build_object('projectId', metadata_before -> 'folderId')
      else metadata_before end,
    metadata_after = case when metadata_after ? 'folderId'
      then (metadata_after - 'folderId') || jsonb_build_object('projectId', metadata_after -> 'folderId')
      else metadata_after end
  where change_type = 'folder';

-- 3. Reverse the constraint/value rewrites (drop the folder-vocabulary
--    check constraints, rewrite the enum-like columns' values back, add
--    the project-vocabulary check constraints back).
alter table public.file_metadata_revisions drop constraint if exists file_metadata_revisions_change_type_check;
update public.file_metadata_revisions set change_type = 'project' where change_type = 'folder';
alter table public.file_metadata_revisions
  add constraint file_metadata_revisions_change_type_check
  check (change_type in ('metadata_values', 'project', 'lifecycle'));

alter table public.operation_audit_events drop constraint if exists operation_audit_events_target_type_check;
update public.operation_audit_events set target_type = 'project' where target_type = 'folder';
alter table public.operation_audit_events
  add constraint operation_audit_events_target_type_check
  check (target_type in ('file', 'project', 'group', 'access_grant', 'user', 'organization', 'query'));

alter table public.access_grants drop constraint if exists access_grants_scope_type_check;
alter table public.access_grants drop constraint if exists access_grants_scope_check;
update public.access_grants set scope_type = 'project' where scope_type = 'folder';
drop index if exists public.access_grants_folder_active;
create index access_grants_project_active
  on public.access_grants (folder_id) where scope_type = 'project' and revoked_at is null;
alter table public.access_grants
  add constraint access_grants_scope_type_check check (scope_type in ('file', 'project')),
  add constraint access_grants_scope_check check (
    (scope_type = 'file' and file_id is not null and folder_id is null)
    or (scope_type = 'project' and folder_id is not null and file_id is null)
  );

alter table public.folder_metadata_audit_events drop constraint if exists folder_metadata_audit_events_event_type_check;
alter table public.folder_metadata_audit_events drop constraint if exists folder_metadata_audit_events_target_type_check;
update public.folder_metadata_audit_events set event_type = replace(event_type, 'folder.', 'project.') where event_type like 'folder.%';
update public.folder_metadata_audit_events set target_type = 'project' where target_type = 'folder';
alter table public.folder_metadata_audit_events
  add constraint project_metadata_audit_events_event_type_check check (event_type in (
    'project.created', 'project.deactivated', 'metadata.defined', 'metadata.updated', 'metadata.deactivated'
  )),
  add constraint project_metadata_audit_events_target_type_check check (target_type in ('project', 'metadata_definition'));

-- 4. Reverse the structural renames last (once every value/constraint
--    dependent on the new column/table names above has already been
--    reversed).
alter table public.folder_metadata_audit_events
  rename constraint folder_metadata_audit_events_organization_id_fkey
  to project_metadata_audit_events_organization_id_fkey;
alter table public.folder_metadata_audit_events rename to project_metadata_audit_events;
alter index public.folder_metadata_audit_events_target rename to project_metadata_audit_events_target;
alter index public.folder_metadata_audit_events_correlation rename to project_metadata_audit_events_correlation;

alter table public.access_grants rename column folder_id to project_id;
alter table public.access_grants
  rename constraint access_grants_folder_id_fkey to access_grants_project_id_fkey;
alter table public.files rename column folder_id to project_id;
alter table public.files rename constraint files_folder_id_fkey to files_project_id_fkey;
alter index public.files_org_folder rename to files_org_project;

alter table public.folders rename constraint folders_created_by_fkey to projects_created_by_fkey;
alter table public.folders rename to projects;
alter index public.folders_org_normalized_name rename to projects_org_normalized_name;

-- 5. Restore the audit_events_view's source-table literal and the
--    admin_audit_list target-type allowlist, and the safe_audit_details
--    allowlisted keys, back to the project vocabulary (mirror image of
--    this migration's Phase 8 -- see the migration file for the exact
--    "before" bodies to restore, substituting 'projects' for the
--    'folder_metadata_audit_events' source_table literal and 'project' for
--    'folder' in the target-type/allowed-keys lists).

-- The reviewed operational transaction supplies COMMIT only after every
-- function/view body above has been materialized and checked.

Because every step above is a rename-back or a value-rewrite-back — never a DELETE — this rollback is itself safe to run against a database that has already served some folder-vocabulary traffic, as long as no row was written that only makes sense under folder-only concepts introduced by a later migration (this migration itself introduces no such concept — it is a pure rename). If a later migration has since built on top of folders (added a parent_id column, for example), this rollback must be reversed together with that later migration's own rollback, in the reverse order they were applied — never roll back a single migration out of order when later migrations depend on it.

Verifying a rollback

After rolling back, re-run the same local verification loop from the pre-migration checklist against the rolled-back schema, substituting the pre-FOLDERS-01 test suite (supabase/tests/database/projects-metadata.test.sql and the pre-FOLDERS-01 versions of access-delegation.test.sql / files-search-metadata.test.sql / audit-visibility.test.sql, restored from git history at the commit immediately before this migration landed) to confirm the project vocabulary is fully restored and every RPC/constraint matches its pre-migration shape exactly.

Continue with Reconciliation or Incident investigation.