Subscription box admin back office in Retool

# Subscription box admin back office

## Purpose

Internal admin app for a subscription box company. Support and ops staff use it to search customers, pause or skip boxes, issue refunds, work failed payments, and bulk-edit next month's box. The team is small and uses Claude Code daily.

Not customer-facing. Do not build customer UI here.

@SPEC.md for requirements. @PLAN.md for phases.

## Stack

- Retool app. Export app JSON to `retool/` using Retool Source Control or Toolscript.
- Postgres. Schema changes are SQL migrations in `db/migrations/`.
- Node 20+ for scripts and tests.
- Payment provider: not chosen yet (see Open questions in SPEC.md). Use `YOUR_PAYMENT_API_KEY` as a placeholder.

## Repo layout

- `db/migrations/`: numbered, additive migrations (`NNN_description.sql`) with a down section.
- `db/queries/`: named `.sql` files, one per Retool query, mirrored from Retool.
- `db/tests/`: SQL tests for constraints and functions.
- `db/seed.sql`: fake data only.
- `retool/`: exported app JSON.
- `scripts/`: helper scripts, including `check-retool-json.js`.
- `docs/`: human docs.
- `.claude/`: skills, agents, hooks, settings.

## Commands

- Migrations (local only): `psql "$DATABASE_URL" -f db/migrations/NNN_description.sql`
- SQL tests: `npm run db:test`
- SQL lint: `npm run lint:sql` (sqlfluff, runs on `db/`)
- Full check: `npm run verify`
- Set the connection with `export DATABASE_URL=YOUR_DATABASE_URL` pointing at a local database. Never a production or staging host.

## Hard rules

- Never run anything against production. Use `DATABASE_URL` pointing at local or staging only, and only when the task says so.
- Secrets never go in the repo. Use placeholders such as `YOUR_DATABASE_URL` and `YOUR_PAYMENT_API_KEY`.
- No real personal data in seeds, tests, fixtures or logs.

## Money rules

- Store amounts as integer cents. No floats, no numeric math in Retool JS.
- Refunds call the payment provider API first. Only after success, insert a `refunds` row.
- Never `UPDATE` an amount column to fix a refund or payment.
- Refund amount cannot exceed paid minus already refunded. Enforce it in the database, not only in the UI.

## Write query rules

Every write query:

- Is parameterized. Retool `{{ }}` bindings go only in value positions, never in SQL identifiers, never concatenated into SQL strings.
- Runs in a transaction.
- Writes an `audit_log` row: actor, action, target, before, after.
- Has a confirm modal before it runs if it is destructive or bulk.

Destructive or bulk actions (refunds, bulk box edits) also need a row-count preview and a row cap. Example: max 500 rows per bulk edit.

## Pause and skip semantics

- Pause sets `subscriptions.status = 'paused'` and `resume_on`.
- Skip inserts a row into `skipped_months`.
- Neither action deletes data. History stays.
- The billing job reads both `subscriptions` and `skipped_months`. Do not change its contract without updating SPEC.md.
- Cannot skip a month that has shipped or is locked for packing.

## Retool gotchas

- Queries share a resource. Use the read-only resource for search and detail views. Use a separate write resource with limited grants for writes.
- Keep transformers small. Move real logic into SQL.
- Name queries `verb_noun`: `search_customers`, `issue_refund`, `preview_box_edit`.
- Export to `retool/` after every change and keep the JSON diff small.

## Role gating

- `support`: search, pause, skip.
- `ops_admin`: refunds above the threshold, failed-payment retries, bulk box edits.
- Gate every write in both Retool (permission group) and SQL (role or check in the function). The UI is not the only control.

## Working habits

- Explore, then plan, then code, then verify.
- Use plan mode before any write to the schema or to queries.
- Run the `verify` skill before saying a task is done.
- Use `/clear` between unrelated tasks.
- Run `/code-review` before merging. Review rules are in @REVIEW.md.
- If a column or business rule is unclear, add it to Open questions in SPEC.md. Do not guess.

## Pointers

- @REVIEW.md: review checklist, imported so `/code-review` applies it.
- @SPEC.md: features, data model, acceptance criteria.
- @PLAN.md: phases and `/goal` prompts.

---
name: sql-test-runner
description: Use to run SQL lint, database tests and the full verify script, returning only the failing steps. Keeps long test output out of the main conversation.
model: haiku
tools: Bash, Read
disallowedTools: Edit, Write
maxTurns: 10
---

You run verification for the subscription box back office and report results. You never edit files.

## Steps

1. Run `npm run verify` from the repository root.
2. If it passes, reply with one line: `PASS: npm run verify`.
3. If it fails, identify each failing step (lint:sql, migrations, db:test, retool JSON check, smoke queries).
4. For each failing step, return the step name and the first relevant lines of its error output, up to 20 lines per step.
5. If a failure is unclear, use Read on the named test file or migration to find the line that failed. Do not paste whole files.

## Output format

- One heading per failing step, in sentence case.
- Failing output only, trimmed. No passing output.
- End with a one-line verdict: `FAIL: <n> step(s) failing` or `PASS`.

## Rules

- Never run commands against production or staging. Only use the local `DATABASE_URL`.
- Never print secrets, connection strings or `.env` contents.
- If `DATABASE_URL` is unset, stop and report that it must be set to a local database.

{
  "model": "sonnet",
  "permissions": {
    "allow": [
      "Bash(npm run verify)",
      "Bash(npm run db:test)",
      "Bash(npm run lint:sql)",
      "Bash(git diff:*)",
      "Bash(git status)"
    ],
    "ask": [
      "Bash(psql:*)",
      "Bash(git push:*)",
      "Bash(npm install:*)"
    ],
    "deny": [
      "Read(.env*)",
      "Bash(rm -rf:*)",
      "Bash(curl:*)"
    ],
    "blockWritesOutsideWorkingDirectories": true
  },
  "env": {
    "CLAUDE_CODE_SUBAGENT_MODEL": "haiku",
    "DATABASE_URL": "YOUR_DATABASE_URL"
  },
  "hooks": {
    "PreToolUse": [
      {
        "matcher": "Bash",
        "hooks": [
          {
            "type": "command",
            "command": "\"$CLAUDE_PROJECT_DIR\"/.claude/hooks/block-prod.sh"
          }
        ]
      }
    ]
  }
}

---
name: retool-query
description: Use when adding or changing a Retool query for search, writes, refunds, or bulk edits, so the SQL is mirrored in db/queries and exported to retool/ with the right safeguards.
---

# Retool query

Use this skill when adding or changing a query in the Retool back office. Every Retool query starts as a SQL file in `db/queries/`, then gets wired into the Retool app, then gets exported to `retool/`.

## Steps

### 1. Write the SQL file first

- Create `db/queries/verb_noun.sql` before touching Retool. Example: `search_customers.sql`, `issue_refund.sql`, `bulk_edit_box_items.sql`.
- Put a header comment at the top with:
  - Purpose in one line.
  - Resource: `read_only` or `write`.
  - Required permission group: `support` or `ops_admin`.
  - Sample parameters and the expected row count, used by the verify skill.
- Use named bindings with `{{ }}` only in value positions, such as `WHERE email = {{ email }}`.
- Never place a binding in an identifier position (table or column name) or build SQL by string concatenation.

### 2. Choose the resource

- Search and read queries use the read-only resource.
- Any insert, update, or delete uses the write resource. The write resource has only the grants that Phase 1 defines.
- Do not add write statements to the read-only resource.

### 3. Add transaction and audit for writes

Every write query must:

- Run inside `BEGIN` and `COMMIT`, so partial failures roll back.
- Insert one `audit_log` row in the same transaction, with actor, action, target, before and after.
- Store money as integer cents and pass the amount as a parameter, never as a formatted string.

Refunds are the exception to direct writes. The query calls the payment provider first using the idempotency key, then writes the `refunds` row. Do not `UPDATE` any amount column.

Bulk edits must have a row cap, for example 500 rows, enforced in SQL. Reject the statement if the count exceeds the cap.

### 4. Wire it into Retool

- **Confirm modal** for any refund, bulk edit, or retry action. Show the target, the amount or row count, and the reason code.
- **Row-count preview** for bulk edits before the confirm modal opens.
- **Loading and error states** on every query. Show the database error message only to ops_admin; show a generic message to support.
- **Success toast** after a successful write, naming what changed.
- **Refresh dependent queries** after a write, so lists and detail pages show the new state.
- **Permission gating** with the Retool permission group check. Support can search, pause, and skip. Refunds above the threshold and bulk edits require `ops_admin`.
- **Transformers** stay small. Move logic into SQL. If a transformer grows past a few lines, move it into the query.

### 5. Name it verb_noun

- Retool query names match the SQL file name: `search_customers`, `issue_refund`, `list_failed_payments`, `retry_payment`, `bulk_edit_box_items`.
- Do not reuse a name for a different action.

### 6. Export to retool/

- Export the app with Retool Source Control or Toolscript into `retool/`.
- Keep the JSON diff small and intentional. If the diff touches unrelated queries or layout, revert the unrelated changes before committing.
- Do not commit real connection strings, API keys, or hostnames. Resource references must use placeholder names such as `YOUR_DATABASE_URL`.

## Checklist

- [ ] SQL file exists in `db/queries/` and is named `verb_noun.sql`.
- [ ] Header comment lists purpose, resource, permission group, sample parameters and expected row count.
- [ ] Bindings appear only in value positions.
- [ ] Read queries use the read-only resource. Write queries use the write resource.
- [ ] Writes run in a transaction and insert an `audit_log` row.
- [ ] Refunds call the provider first and never update amounts directly.
- [ ] Bulk edits enforce a row cap and show a preview.
- [ ] Confirm modal, loading, error, success toast and refresh are wired.
- [ ] Permission group gating is set.
- [ ] Export to `retool/` is done, the diff is small, and no secrets are committed.

## Finish

Run the `verify` skill before reporting the query as done. Include the verify verdict line in your summary.

---
name: sql-migration
description: Use when writing or changing a Postgres migration in db/migrations, to keep schema changes additive, reversible, constraint-backed and tested.
disable-model-invocation: false
---

# SQL migration

Use this skill for any change to the schema: new tables, columns, constraints, indexes, or functions. Read `CLAUDE.md` for the money and audit rules first.

## File naming and structure

- Name files `db/migrations/NNN_description.sql`, where `NNN` is the next three-digit number. Example: `007_add_skipped_months.sql`.
- Each file has two sections: an up section first, then a down section below a clear `-- DOWN` marker.
- The down section must reverse the up section exactly. If a step cannot be reversed (for example, dropping data), write a comment in the down section explaining why and what must be restored by hand.

## Rules

1. **Additive first.** Add new tables and nullable columns before anything else. Do not drop or rename a column in the same migration that starts using its replacement.
2. **Backfill in steps.** To add a required column to an existing table:
   - Add the column as nullable.
   - Backfill in a separate migration or a batched statement.
   - Add the `NOT NULL` constraint in a later migration.
3. **Concurrent indexes.** Create indexes with `CREATE INDEX CONCURRENTLY` where possible. Note in the file header if concurrency is not possible, and why.
4. **Money as integer cents.** Store every amount in an `integer` or `bigint` column named `*_cents`. Never use `numeric` or `float` for money. Never store currency amounts as text.
5. **Timestamps.** Use `timestamptz` for every timestamp column. Do not use `timestamp without time zone`.
6. **Business rules as constraints.** Every rule needs a database-level enforcement, not only application checks. Examples:
   - `CHECK (refunded_cents <= paid_cents)` or a trigger for the refund cap.
   - Unique constraints for idempotency keys.
   - Foreign keys for every relationship.
7. **Comments.** Add `COMMENT ON TABLE` and `COMMENT ON COLUMN` for every new table and every non-obvious column. Mark unknown columns with `TODO` and add them to the Open questions list in `SPEC.md`.
8. **Never delete history.** Pause, skip and refund data is append-only. Do not add `ON DELETE CASCADE` to tables holding payments, refunds, skipped months, or audit rows.

## Checklist

Before finishing a migration, confirm each item:

- [ ] File is named `db/migrations/NNN_description.sql` with the next free number.
- [ ] Up and down sections both exist, and the down section reverses the up section.
- [ ] No column is dropped or renamed in the same step as its replacement is introduced.
- [ ] All amounts are integer cents. All timestamps are `timestamptz`.
- [ ] Each business rule has a constraint, trigger, or unique index.
- [ ] Search columns (`lower(email)`, phone, name) have indexes where queries need them.
- [ ] Tables and non-obvious columns have comments.
- [ ] A test exists in `db/tests/` for each new constraint or function.

## Testing

For each new constraint or function, add a test file in `db/tests/` named after the rule, for example `refund_cap.sql` or `skip_locked_month.sql`. Each test should:

- Insert the minimum fixture rows it needs.
- Assert that a valid write succeeds.
- Assert that an invalid write fails with the expected error.
- Roll back, so the test leaves no data behind.

## Finish

Run the `verify` skill before reporting the migration as done. Include the verify verdict line in your summary.

---
name: verify
description: Run after any SQL, migration or Retool export change, and before declaring a task done, to prove the change works on a local test database.
---

# Verify

Use this skill after changing anything in `db/`, `retool/`, or `scripts/`, and before saying a task is done. Run the steps in order. Stop at the first failing step, fix it, and rerun from that step.

## Safety rules

- Only ever target a local database. Before step 2, confirm `$DATABASE_URL` points at `localhost` or `127.0.0.1`. If it points anywhere else, stop and report it. Do not continue.
- Never run against production or staging databases, even for read-only checks.
- Use the `box_test` database created by the script below, not `box_dev`, so the seed is reset each run.
- Never print connection strings with real credentials. Use `YOUR_DATABASE_URL` in any output you write.

## Steps

### Step 1: Lint SQL

Run:

```
npm run lint:sql
```

This runs sqlfluff over `db/`. Record pass or fail.

### Step 2: Build a fresh local database

Run the reset script:

```
scripts/reset-test-db.sh
```

The script does the following:

1. Drops and recreates `box_test` on the local server.
2. Applies the up section of each file in `db/migrations/` in filename order, using `psql box_test -f`.
3. Loads `db/seed.sql`.

If the script is missing, stop and report that Phase 1 scripts are incomplete. Do not build the database by hand.

Record pass or fail.

### Step 3: Run database tests

Run:

```
npm run db:test
```

These tests must cover:

- Refund cap: a refund cannot exceed paid minus refunded.
- Skip rules: a skip on a shipped or locked month is rejected.
- Bulk edit rollback: a failing bulk edit leaves no partial rows.
- Audit: each write function inserts an `audit_log` row with actor, action, target, before and after.

Record pass or fail for each named test group.

### Step 4: Validate Retool JSON

Run:

```
node scripts/check-retool-json.js
```

This checks that every `retool/*.json` file parses and has the expected top-level structure. Record pass or fail.

### Step 5: Smoke run key queries

For each file in `db/queries/`, run the query with the sample parameters listed in its header comment, against `box_test`. Assert the expected row count given in the same comment.

Key queries include `search_customers`, `list_failed_payments`, and the bulk box preview query.

Record pass or fail per query. If a query has no expected row count in its header, treat that as a failure and fix the header.

## Report format

Keep the report short:

1. One line per step: `Step N: name - PASS` or `Step N: name - FAIL`.
2. For each failing step, show only the failing output, trimmed to the relevant lines.
3. A final one-line verdict: `VERIFY PASSED` or `VERIFY FAILED at step N`.

If the verify run is delegated to the `sql-test-runner` subagent, the same report format applies. Its output replaces the raw test logs in the main conversation.

## When to use this skill

- After writing or editing a migration, a seed file, or a test.
- After changing a query in `db/queries/` or exporting a Retool app to `retool/`.
- Before reporting any phase or task as complete.

# Install and setup

Setup for the subscription box admin back office: Retool app on Postgres, with Claude Code as the build assistant.

## Prerequisites

- Claude Code 2.1.296 or newer. Check with `claude --version`.
- Postgres 15 or newer, running locally.
- Node 20 or newer.
- A Retool workspace with source control enabled.

## Create the local database

Use a local database only. Never point these steps at production or staging.

1. Create the repo folders:

   ```
   mkdir -p db/migrations db/queries db/tests retool scripts .claude/skills .claude/agents .claude/hooks
   ```

2. Create the local database:

   ```
   createdb box_dev
   ```

3. Set the connection string in your shell. Use your own local user and port:

   ```
   export DATABASE_URL=postgres://USER:PASSWORD@HOST/DB
   ```

   In docs and shared files, write the value as `YOUR_DATABASE_URL`. Do not commit a real connection string.

## Create database roles

Create two roles on the local database. Retool search uses the read-only role. Write queries use the write role, which has only the grants it needs.

Replace the passwords with your own values. Do not reuse these roles in production.

```sql
CREATE ROLE box_readonly LOGIN PASSWORD 'YOUR_READONLY_PASSWORD';
GRANT CONNECT ON DATABASE box_dev TO box_readonly;
GRANT USAGE ON SCHEMA public TO box_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO box_readonly;

CREATE ROLE box_writer LOGIN PASSWORD 'YOUR_WRITER_PASSWORD';
GRANT CONNECT ON DATABASE box_dev TO box_writer;
GRANT USAGE ON SCHEMA public TO box_writer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO box_writer;
GRANT INSERT, UPDATE ON subscriptions, skipped_months, refunds TO box_writer;
GRANT INSERT ON audit_log TO box_writer;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO box_writer;
```

Notes:

- The writer gets no DELETE on any table.
- The writer gets no UPDATE on refunds or audit_log, so history stays intact.
- Adjust the table list once the Phase 1 migrations exist. Add grants only when a query needs them.
- Create the Retool resources with these roles: a read-only resource for search, and a separate write resource for pause, skip, refunds, and bulk edits.

## Install plugins

1. Update Claude Code and confirm the version:

   ```
   claude update
   claude --version
   ```

2. Add the team marketplace:

   ```
   claude plugin marketplace add anthropics/knowledge-work-plugins
   ```

3. Install the plugins:

   ```
   claude plugin install code-review@claude-plugins-official
   claude plugin install security-guidance@claude-plugins-official
   claude plugin install commit-commands@claude-plugins-official
   claude plugin install engineering@knowledge-work-plugins
   ```

The `knowledge-work-plugins` marketplace has auto-update off by default. Update it by hand when you want new workflows.

## Copy project files and set up the hook

1. Copy the generated files into the repo: CLAUDE.md, SPEC.md, PLAN.md, REVIEW.md, INSTALL.md, and the `.claude/` folder.
2. Make the production guard hook executable:

   ```
   chmod +x .claude/hooks/block-prod.sh
   ```

3. Start Claude Code from the repo root:

   ```
   claude
   ```

4. Run `/init` and compare its draft with CLAUDE.md. Merge only lines that add something new. Keep CLAUDE.md under 120 lines.

## Run the first phase

1. Open PLAN.md and read Phase 1 (schema and seed).
2. Use plan mode to review the proposed schema before any file is written.
3. Run the Phase 1 `/goal` prompt from the end of PLAN.md.
4. The run stops when `npm run verify` passes, or after 40 turns. Read the report and any blockers before continuing.
5. Record unknown columns or rules in SPEC.md under Open questions. Do not guess.

Later phases follow the same pattern: plan mode, build, then the phase `/goal`.

## Daily habits

- Run `/clear` between unrelated tasks to keep context small.
- Run `/usage` to see where tokens went.
- Use plan mode before any write, migration, or Retool export change.
- Run the verify skill before saying a change is done.
- Never run commands against production. Keep `DATABASE_URL` set to a local database.

# Back office plan

Six phases. Run each phase in plan mode first. Run `/clear` before starting the next phase.

Every phase ends with a `/goal` prompt. The check is `npm run verify` passing plus the named tests. Constraints apply to every phase: no production or staging access, no secrets, and only touch the folders listed in the prompt.

## Phase 1: schema and seed

- Migrations for all tables in SPEC.md.
- Fake seed data in `db/seed.sql`.
- SQL tests in `db/tests`.
- `verify` skill working end to end.

```
/goal Complete Phase 1 of PLAN.md. Read CLAUDE.md, SPEC.md and PLAN.md first. Propose the schema in plan mode, then write it. End state: (1) additive, reversible migrations in db/migrations create customers, subscriptions, boxes, box_items, skipped_months, payments, refunds and audit_log, with integer cents, timestamptz, a constraint blocking refunds above paid minus refunded, and indexes for lower(email), phone and name search; (2) db/seed.sql loads at least 200 customers, 300 subscriptions, 50 failed payments and next month's box with 12 items, all fake; (3) db/tests has tests named refund_cap, skip_locked_month and audit_insert that pass; (4) package.json defines db:test, lint:sql and verify. Check: npm run verify passes every step, including refund_cap, skip_locked_month and audit_insert, and the output is shown. Constraints: touch only db/, scripts/, package.json and docs/; use DATABASE_URL pointing at a local database only; no production or staging; no secrets, use YOUR_DATABASE_URL; no global installs without asking. Ambiguous columns or rules go under Open questions in SPEC.md, not guesses. Use the sql-test-runner subagent for verify. Stop after 40 turns and report blockers.
```

## Phase 2: customer search and detail

- Search Retool query on the read-only resource.
- Debounced input, paginated results, max 50 rows.
- Customer detail page with subscriptions and payments.

```
/goal Complete Phase 2 of PLAN.md: customer search and detail page in Retool, per SPEC.md Feature 1. Use the retool-query skill. End state: (1) db/queries contains search_customers and get_customer_detail, both parameterized and named verb_noun; (2) both are wired to the read-only resource in retool/ as exported JSON; (3) tests named search_by_email, search_limit_50 and search_empty_term pass in db/tests. Check: npm run verify passes, including those tests, and the output is shown. Constraints: touch only db/queries, db/tests, retool/ and docs/; read-only resource only; no production or staging; no secrets; no new global tools. Stop after 40 turns and report blockers.
```

## Phase 3: pause, skip and audit writes

- Pause and skip writes in transactions.
- Locked-month checks.
- Role gating for support and ops_admin.
- Audit row on every write.

```
/goal Complete Phase 3 of PLAN.md: pause and skip actions per SPEC.md Feature 2, with transactions, audit rows and role gating. Use the retool-query and sql-migration skills where they apply. End state: (1) db/queries contains pause_subscription and skip_month, each in one transaction that writes audit_log; (2) skip_month rejects locked or shipped months; (3) tests named pause_keeps_history, skip_locked_month and skip_duplicate pass in db/tests; (4) retool/ export shows confirm modals and hides actions for support users. Check: npm run verify passes, including those tests, and the output is shown. Constraints: touch only db/, retool/ and docs/; write resource only for these queries, with limited grants; no production or staging; no secrets. Stop after 40 turns and report blockers.
```

## Phase 4: refunds and failed payments

- Refund flow with provider stub and idempotency key.
- Failed payments list, filters, CSV export.
- Retry limited to ops_admin.

```
/goal Complete Phase 4 of PLAN.md: refunds and failed payments per SPEC.md Features 3 and 4. The provider is a stub behind an interface in scripts/; no real provider calls. End state: (1) db/queries contains issue_refund (idempotency key, cap check, provider call then DB write) and list_failed_payments (filters, paging); (2) retry_payment increments retry_count and sets last_attempt_at, ops_admin only; (3) tests named refund_idempotent, refund_provider_fail_no_write and failed_filter_and pass in db/tests; (4) CSV export is wired in retool/. Check: npm run verify passes, including those tests, and the output is shown. Constraints: touch only db/, scripts/, retool/ and docs/; no real provider hosts; no production or staging; no secrets, use YOUR_PAYMENT_API_KEY. Stop after 40 turns and report blockers.
```

## Phase 5: bulk edit of next month's box

- Editor for box_items with stock validation.
- Diff preview before apply.
- One transaction, 500-row cap, cutoff lock.

```
/goal Complete Phase 5 of PLAN.md: bulk edit of next month's box per SPEC.md Feature 5. End state: (1) db/queries contains preview_box_diff and apply_box_edit; apply runs in one transaction, enforces the 500-row cap and rejects edits after cutoff_at; (2) stock shortfalls block apply and name the SKU; (3) tests named bulk_rollback, bulk_cap_500, bulk_after_cutoff and bulk_stock_block pass in db/tests; (4) retool/ export shows the diff table and confirm modal, ops_admin only. Check: npm run verify passes, including those tests, and the output is shown. Constraints: touch only db/, retool/ and docs/; write resource with limited grants; no production or staging; no secrets. Stop after 40 turns and report blockers.
```

## Phase 6: hardening

- Permission review across all queries.
- PII masking for support users.
- Security review and docs.

```
/goal Complete Phase 6 of PLAN.md: hardening. End state: (1) a permission matrix in docs/permissions.md maps every query in db/queries to its permission group and resource; (2) support users see masked email and phone via query-level masking, with tests named pii_mask_support and pii_unmasked_ops in db/tests; (3) security-guidance and code-review findings are fixed or listed in docs/open-risks.md; (4) docs/ has a short runbook for the team. Check: npm run verify passes, including those tests, and the output is shown. Constraints: touch only db/, docs/ and retool/; no production or staging; no secrets; no real personal data in any file. Stop after 40 turns and report blockers.
```

# Review checklist

Used by `/code-review` through the `@REVIEW.md` import in CLAUDE.md. Check every item that touches the changed files. Flag anything missing.

## Money

- Amounts are integer cents. No float or numeric math in app code or SQL.
- Refund amount cannot exceed paid minus refunded. Look for a CHECK, trigger, or a guard in the query.
- Refunds go through the payment provider call first, then write a refunds row. No direct UPDATE of amounts.

## Writes

- Every write is parameterized. Retool `{{ }}` bindings appear only in value positions.
- No SQL built by string concatenation of user input. Identifiers are never bound from user input.
- Every write runs in a transaction.
- Every write inserts an audit_log row with actor, action, target, before, and after.
- Every write is gated by the right permission group (support or ops_admin).

## Bulk and destructive actions

- Bulk edits show a row-count preview before apply.
- Bulk edits enforce the row cap (500 rows per edit unless SPEC.md says otherwise).
- Refunds and bulk edits have a confirm step in the Retool UI.

## Data and privacy

- No PII in logs, comments, or test output.
- Seed data is fake. Names, emails, and phone numbers are generated.
- No secrets or real connection strings are committed. Use YOUR_DATABASE_URL style placeholders.

## Pause and skip

- Pause and skip never delete history rows.
- Skip is rejected for shipped or locked months.

## Schema and migrations

- Migrations are additive and reversible, with a matching down section.
- New search columns have indexes: lower(email), phone, and name (trigram).
- Each business rule has a constraint or a test.

## Retool

- Queries are named verb_noun (search_customers, issue_refund).
- No large inline JavaScript in transformers. Move logic into SQL or a small named transformer.
- Exported JSON in retool/ diffs small and intentional. Flag unrelated reformatting.

## Tests

- Each new SQL function or constraint has a test in db/tests.
- `npm run db:test` covers the change and passes.

# Back office spec

Internal admin app for a subscription box company. Built in Retool on Postgres.

## Users and permissions

- Support agents: permission group `support`.
- Ops admins: permission group `ops_admin`.
- Support can search, pause and skip.
- Refunds above the threshold and bulk box edits need `ops_admin`.
- Failed payment retries need `ops_admin`.

## Data model assumptions

Confirm these in Phase 1. Columns marked TODO are unknown.

- customers: id, name, email, phone, created_at, TODO address fields
- subscriptions: id, customer_id, status (active, paused, cancelled), resume_on, next_charge_date, created_at
- boxes: id, ship_month (date), cutoff_at (timestamptz), locked_at, TODO packing status
- box_items: box_id, sku, quantity
- skipped_months: subscription_id, box_month, created_by, created_at
- payments: id, subscription_id, amount_cents, status, failure_reason, retry_count, last_attempt_at, provider_payment_id
- refunds: id, payment_id, amount_cents, reason_code, idempotency_key, provider_refund_id, created_at
- audit_log: id, actor, action, target, before (jsonb), after (jsonb), created_at

Rules that apply to all tables:

- Money is integer cents (`amount_cents`).
- Timestamps are `timestamptz`.
- Nothing is hard-deleted from history tables.

## Feature 1: customer search

- Search by name, email, phone or subscription id.
- Debounced input.
- Paginated, max 50 rows per page.
- Each result links to a customer detail page.
- Detail page shows subscriptions, status, next charge date and payments.

Acceptance criteria:

- A search for an exact email returns that customer in the first page.
- A query returns at most 50 rows.
- Searching with an empty term returns no rows and no error.
- Search uses the read-only Postgres resource.

## Feature 2: pause or skip

- Pause sets `subscriptions.status` to `paused` and stores `resume_on`.
- Skip adds one row to `skipped_months` for a chosen box month.
- Neither action deletes data.
- The UI shows the effect on the next charge date before confirming.
- A month that has already shipped or is locked for packing cannot be skipped.
- Every pause or skip writes an `audit_log` row in the same transaction.

Acceptance criteria:

- Pausing a subscription with a resume date keeps all prior rows.
- Skipping a locked month shows an error and writes nothing.
- Skipping the same month twice is rejected by a unique constraint.
- Support users can pause and skip. Other actions are hidden for them.

## Feature 3: refunds

- Full or partial refund against one payment.
- Reason code is required.
- Amount cannot exceed paid minus already refunded.
- Flow: provider call first, then DB write.
- Each refund uses an idempotency key so retries do not double refund.
- Above the threshold, only `ops_admin` can issue the refund.
- Writes a `refunds` row and an `audit_log` row.

Acceptance criteria:

- A refund above unrefunded paid amount is rejected before any provider call.
- Retrying the same idempotency key returns the first result.
- If the provider call fails, no refunds row is written.
- The confirm modal shows the amount and the customer name.

## Feature 4: failed payments

- List of failed payments with failure reason, retry count and last attempt.
- Each row links to the customer.
- Filter by date range and failure reason.
- Export the current filtered view to CSV.
- Retry action is limited to `ops_admin`.

Acceptance criteria:

- Filters combine with AND.
- CSV export contains only the filtered rows.
- A support user sees the list but no retry button.
- Retry increments `retry_count` and sets `last_attempt_at`.

## Feature 5: bulk edit next month's box

- Table editor for `box_items` by SKU and quantity.
- Validate quantities against stock.
- Preview a diff of before and after before applying.
- Apply all changes in one transaction.
- Cap at 500 rows per bulk edit.
- The box locks at `cutoff_at`. After that, edits are rejected.

Acceptance criteria:

- A stock shortfall blocks apply and names the SKU.
- A failed apply leaves `box_items` unchanged.
- An edit after cutoff is rejected.
- The diff preview matches the rows written.

## Non-goals

- Customer-facing UI.
- Changing the payment provider.
- Building the billing job. The billing job only reads `skipped_months` and `subscriptions.status`.

## Open questions

- Which payment provider, and does it have a refund API with idempotency keys?
- What is the cutoff time for each box?
- What is the refund approval threshold in cents?
- Which PII fields must be masked for support users?
- Are addresses in scope for search?
- Does a paused subscription keep its box month or lose it?