backfill-script-creator
Create a new Supabase data backfill, dedup, or one-off data correction script for Hamster Studio in apps/web/supabase/scripts/. Use whenever the user asks for a backfill script, data migration script, dedup script, one-off data fix, repair job, or anything that needs to mutate production data outside of a regular schema migration. Provides a battle-tested template with dry-run-by-default safety, paginated reads, chunked writes, append-only audit logging, and auto-generated rollback SQL. Trigger even if the user only says 'I need to fix some bad rows in production' or 'we need to fill in this column for all existing accounts' — those are exactly the situations this skill exists for.
- License 许可
- MIT
- 最近更新
- 2026-11-05
- Stars
- ★ 4
- Forks
- 0
01 前言 Front Matter
- name
- backfill-script-creator
- version
- 1.0.0
- description
- Create a new Supabase data backfill, dedup, or one-off data correction script for Hamster Studio in apps/web/supabase/scripts/. Use whenever the user asks for a backfill script, data migration script, dedup script, one-off data fix, repair job, or anything that needs to mutate production data outside of a regular schema migration. Provides a battle-tested template with dry-run-by-default safety, paginated reads, chunked writes, append-only audit logging, and auto-generated rollback SQL. Trigger even if the user only says 'I need to fix some bad rows in production' or 'we need to fill in this column for all existing accounts' — those are exactly the situations this skill exists for.
02 资源文件 7
- 脚本assets/backfill-template.ts26.6 KB
- 资产文件assets/env-1password.template455 B
- 参考文档references/audit-and-rollback.md19.4 KB
- 参考文档references/customization-walkthrough.md14.2 KB
- 参考文档references/env-setup.md6.3 KB
- 参考文档references/pagination-and-batching.md9.6 KB
- 参考文档references/safety-rails.md9.8 KB
03 SKILL.md 正文
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
--executeflag and an interactiveexecuteconfirmation typed at the prompt - Paginated reads — Supabase silently caps
selectat 1000 rows; full-table scans MUST page throughrange() - 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.sqlfile with reverse statements wrapped inBEGIN/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, ordelete? - Idempotency — can the script be safely re-run? (it should be — design for it)
- Account scoping — does this need to respect
account_idboundaries, 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:
# 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 |
Mutation steps in run() |
Each mutation MUST go through an audit* helper |
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 deletesauditUpdatedRowsById(ctx, table, rows, columns, message, mutate)— for updates by primary keyauditUpdatedRowsByMatch(ctx, table, rows, columns, getCurrentMatch, message, mutate)— for updates by composite key.getCurrentMatchis a function(row) => matchthat 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 = !EXECUTEline present) - [ ] Execute mode requires both
--executeflag AND interactiveexecuteconfirmation - [ ] Every
selectthat could return >1000 rows usesfetchAllPages()— no raw.select()without.range() - [ ] Every
insertof >500 rows useschunkArray(rows, MUTATION_BATCH_SIZE) - [ ] Every mutation goes through an
audit*helper, never raw.insert/update/delete() - [ ]
account_idfilters 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.
5. Run instructions for the user
The script is invoked from apps/web:
# 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 inBEGIN/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.
Safety invariants (non-negotiable)
These are the rules that exist because they prevented real incidents in #1687. A backfill script that violates any of them is broken even if it appears to work:
- Dry-run is the default execution mode. A user who runs the script without flags gets a read-only preview. Mutation requires both the flag and the interactive confirmation. Never invert this.
- Every full-table read pages through
range(). Supabase caps at 1000 rows per request. Aselectwithout paging silently truncates and the script will skip rows. - Bulk inserts are chunked. A single
insertwith 50,000 rows will fail or time out. Chunk toMUTATION_BATCH_SIZE(500). - Every mutation flows through an audit helper. Direct
.insert/update/delete()calls bypass the audit log AND the rollback journal. There is no recovery path for what you didn't record. - Audit context is finalized in both success and failure paths. The template's top-level
.then/.catchalready does this — don't strip it. A crashed run with a half-written rollback journal is still recoverable; a crashed run with no journal is not. - Secrets come from 1Password via the colocated env file. Never hardcode keys, never use
process.env.SECRET || 'fallback', never commit.env.<script-name>files.
Reference files
Read these when you need depth on a specific area. Don't read all of them upfront — the SKILL.md above is enough for ~80% of cases.
| File | Read when |
|---|---|
| references/pagination-and-batching.md | Implementing read functions, bulk inserts, or wiring up progress reporting for long-running phases |
| references/audit-and-rollback.md | Designing mutation steps, choosing the right audit helper, or understanding the rollback model |
| references/env-setup.md | Setting up the 1Password template, debugging env loading errors, or running against a non-default Supabase environment |
| references/safety-rails.md | Reviewing a backfill before merge, or explaining to a reviewer why the safety patterns exist |
| references/customization-walkthrough.md | First time customizing the template — walks through which lines to edit and what to leave alone |
What goes in the assets
| File | Purpose |
|---|---|
| assets/backfill-template.ts | Full scaffolding script. Copy and customize. ~700 lines, mostly helpers — the user-edited section is small. |
| assets/env-1password.template | 1Password injection template. Copy and rename to .env.1p.<script-name>. |
Common mistakes to avoid
- Calling
.select('*')without paging — works fine in dev with 50 rows, silently drops data in prod with 50,000. - Wrapping the whole script in a try/catch — the top-level
.then/.catchis the right place. Inner try/catch swallows errors that should kill the run. - Logging instead of auditing —
console.log('updated 500 rows')is not an audit log. The audit helper is what creates the recoverable artifact. - Forgetting to re-check derived state after dedup — if a backfill deletes rows, downstream computations that depend on those rows must re-run AFTER the dedup. The reference backfill does this with
findMissingInitiativeBriefs()afterapplyInitiativeDedup(). - Skipping the dry-run before execute — the dry-run preview is the only opportunity to catch a bad query before it mutates. Always run dry-run first, even for "small" backfills.
- Hand-writing rollback SQL after the fact — by then it's too late. The rollback file must be generated as the script runs, not reconstructed later.
When NOT to use this skill
- Schema migrations — use
pnpm --filter web supabase:db:diffandapps/web/supabase/migrations/instead. Schema changes belong in migrations, not in tsx scripts. - Idempotent housekeeping inside the app — if the operation runs continuously (e.g., a cron) it belongs in the NestJS app (
apps/ai/), not as a one-off script. - Read-only data exports — a script that only reads doesn't need any of the safety machinery. Just write a tsx file that queries and prints/writes a CSV.
If the user's task fits one of these, point them at the right pattern instead of using this skill.
04 同仓库更多 Skills
writing-waterfall-requirements-documents
Write a waterfall requirements document: elicit, structure and review testable, traceable requirements, then baseline the specification before design.
writing-vision-and-values-statements
Write a V2MOM vision a reader can test and a ranked list of values that settles real tradeoffs, so the rest of the document has a clear anchor.
writing-system-prompts-for-claude
Write Claude prompts and CLAUDE.md files that supply exact inputs, scope the task, and define done so the first attempt can be checked.
writing-page-specific-website-copy
Website copywriting by page type: write homepage, landing page, pricing page and feature page copy that fits each page's visitor and single job.
writing-internal-press-releases
How to write an internal press release: the one-page working backwards press release that opens a PR/FAQ document before anything is built.
writing-email-sequences-that-sell
Email sequence copywriting for nurture and sales: give each email one job, write clear subject lines, and close with one specific call to action.