backfill-script-creator

Files (8)

• SKILL.md
• backfill-template.ts · assets/
• env-1password.template · assets/
• audit-and-rollback.md · references/
• customization-walkthrough.md · references/
• env-setup.md · references/
• pagination-and-batching.md · references/
• safety-rails.md · references/

SKILL.md (first lines)

# Backfill Script Creator

## What this skill is for

Backfills are the riskiest scripts in the codebase. They run against production data, often touch every row in a table, and a bug can corrupt or delete data with no automatic rollback. This skill captures the patterns from `apps/web/supabase/scripts/backfill-initiative-briefs.ts` (hardened in #1687) so every new backfill inherits the same safety rails:

- **Dry-run by default** — no mutation can happen without an explicit `--execute` flag and an interactive `execute` confirmation typed at the prompt
- **Paginated reads** — Supabase silently caps `select` at 1000 rows; full-table scans MUST page through `range()`
- **Chunked writes** — bulk inserts split into batches so a single failure doesn't take down the whole run
- **Append-only audit log** — every mutation is recorded to a JSONL file with row snapshots
- **Auto-generated rollback SQL** — execute-mode runs produce a `*.rollback.sql` file with reverse statements wrapped in `BEGIN`/`COMMIT`
- **Colocated env** — secrets loaded from a `.env.<script-name>` file next to the script, generated from a 1Password template

If the user is writing a script that mutates production data, this skill applies. The template handles ~80% of the boilerplate; the user only fills in the read queries and the mutation steps.

## Workflow

Follow these steps in order. Each is a checkpoint — confirm the previous one is sound before moving on.

### 1. Gather requirements

Before touching any code, get these from the user (or read them from a Linear ticket if linked):

- **Goal** — what state do we want the data in after the run?
- **Tables affected** — read sources and write targets
- **Read shape** — how do we identify candidate rows? (e.g., `where x is null`, `where created_at < ...`)
- **Mutation plan** — for each step, is it `insert`, `update`, or `delete`?
- **Idempotency** — can the script be safely re-run? (it should be — design for it)
- **Account scoping** — does this need to respect `account_id` boundaries, or is it cross-tenant?
- **Validation** — how will we know it worked? (count queries, spot checks, downstream feature behaviour)

If anything is unclear, ask before scaffolding. A backfill written against the wrong understanding of the schema is worse than no backfill.

### 2. Scaffold from the template

Copy the template assets into place. The script lives next to the existing backfills so it inherits gitignore rules, env loading conventions, and the `logs/` directory:

```bash
# from repo root
cp .agents/skills/backfill-script-creator/assets/backfill-template.ts \
   apps/web/supabase/scripts/<script-name>.ts

cp .agents/skills/backfill-script-creator/assets/env-1password.template \
   apps/web/supabase/scripts/.env.1p.<script-name>
```

Naming convention: `<verb>-<entity>.ts`. Examples: `backfill-initiative-briefs.ts`, `dedupe-orphan-documents.ts`, `repair-stuck-jobs.ts`.

After copying, replace placeholders inside both files:

- `__SCRIPT_NAME__` → the kebab-case script name (e.g., `backfill-initiative-briefs`)
- `__SCRIPT_DESCRIPTION__` → one-line summary

### 3. Customize the business logic

The template ships with all helpers in place. The user only needs to edit four sections, each clearly marked with `// TODO:` in the template:

| Section | What to write | Reference |
| --- | --- | --- |
| **Types** | Row shape interfaces for the rows you read | — |
| **Read functions** | One function per fetch, using `fetchAllPages()` | [references/pagination-and-batching.md](references/pagination-and-batching.md) |
| **Mutation steps in `run()`** | Each mutation MUST go through an `audit*` helper | [references/audit-and-rollback.md](references/audit-and-rollback.md) |
| **Dry-run preview** | What the user sees in default mode | — |

**Critical rule**: NEVER call `supabase.from(...).insert/update/delete(...)` outside of an `audit*` helper's `mutate` callback. The four helpers are:

- `auditDeletedRows(ctx, table, rows, message, mutate)` — for deletes
- `auditUpdatedRowsById(ctx, table, rows, columns, message, mutate)` — for updates by primary key
- `auditUpdatedRowsByMatch(ctx, table, rows, columns, getCurrentMatch, message, mutate)` — for updates by composite key. `getCurrentMatch` is a **function** `(row) => match` that synthesizes the post-mutation where-clause for each row.
- `auditInsertedRows(ctx, table, rows, matchColumns, message, mutate)` — for inserts

These helpers snapshot the affected rows BEFORE the mutation, append the snapshot to the JSONL audit log, and synthesize a reverse SQL statement into the rollback journal. Bypassing them means the rollback file will be incomplete and the run is no longer recoverable.

**Equally critical: call audit helpers per CHUNK, not per ROW.** Each helper invocation pays a fixed cost (two `appendFile` syscalls plus JSON serialization of the row payload). For a 50,000-row backfill, per-row usage is roughly 100x slower than the same backfill with chunked usage. Gather mutations into batches via `chunkArray(rows, MUTATION_BATCH_SIZE)` and call the helper once per batch, with the entire batch as the `rows` argument. The helpers are designed for batch operation; per-row use defeats their purpose.

### 4. Pre-execute review checklist

Before the user runs the script in execute mode, verify ALL of these. If any fail, fix before running:

- [ ] Dry-run mode is the default (`const DRY_RUN = !EXECUTE` line present)
- [ ] Execute mode requires both `--execute` flag AND interactive `execute` confirmation
- [ ] Every `select` that could return >1000 rows uses `fetchAllPages()` — no raw `.select()` without `.range()`
- [ ] Every `insert` of >500 rows uses `chunkArray(rows, MUTATION_BATCH_SIZE)`
- [ ] Every mutation goes through an `audit*` helper, never raw `.insert/update/delete()`
- [ ] `account_id` filters are correct for multi-tenant scoping (or explicitly cross-tenant with a comment explaining why)
- [ ] The final report queries are present (counts after the run for sanity checking)
- [ ] The script is idempotent — re-running it on already-fixed data is a no-op
- [ ] `.env.1p.<script-name>` only contains 1Password references, never literal secrets
- [ ] `apps/web/supabase/scripts/logs/` is gitignored (already covered by repo `.gitignore`)

For the safety rationale behind each item, see [references/safety-rails.md](references/safety-rails.md).

### 5. Run instructions for the user

The script is invoked from `apps/web`:

```bash
# 1. Generate the local env file from the 1Password template
VAULT_NAME=Tux op inject \
  --in-file=supabase/scripts/.env.1p.<script-name> \
  --out-file=supabase/scripts/.env.<script-name> \
  --force

# 2. Dry-run (default). Read-only — prints a preview of every mutation.
pnpm exec tsx supabase/scripts/<script-name>.ts

# 3. Execute (requires interactive TTY + typing 'execute' to confirm)
pnpm exec tsx supabase/scripts/<script-name>.ts --execute
```

Vault name (`Tux`, `Local`, etc.) depends on which Supabase environment the user is targeting. Production runs use the production vault and MUST be reviewed by a second engineer before execution.

After an execute run completes, three artifacts land in `apps/web/supabase/scripts/logs/`:

- `<script-name>-<run-id>.jsonl` — append-only event log with row snapshots
- `<script-name>-<run-id>.rollback.journal.sql` — raw reverse statements as they were generated
- `<script-name>-<run-id>.rollback.sql` — final rollback wrapped in `BEGIN/COMMIT`, statements in reverse order

To roll back, the operator runs the `.rollback.sql` file through psql against the same database. Rollback support means "we generated artifacts to recover from"; it does NOT mean automatic transactional rollback — the database is mutated as the script runs.

From gethamster/skills, MIT.

More from gethamster/skills