# Worker guide: moving queries from D1 into RepoState / AccountState

Read `docs/do-migration.md` first. This guide is the mechanical recipe every migration worker follows.

## The API

```ts
import { repoDb, accountDb, stmt } from "~/server/scoped-db"; // stmt here is for scoped batches only
import { locate, registerLocator, forgetLocator } from "~/server/scoped-db/locators";
import { accountsById } from "~/server/people"; // id → { id, name, displayName } from the D1 directory
```

- `repoDb(repoId)` and `accountDb(accountId)` return `{ first, all, run, batch }` with the same signatures as
  `~/server/db`. Behind them is the repo's `RepoState` / the account's `AccountState` Durable Object SQLite.
- Which table lives where is defined in `scripts/gen-scoped-schema.ts` (`REPO_TABLES`, `ACCOUNT_TABLES`).
  Everything else stays in D1 and keeps using `~/server/db`.
- **Stays in D1 (directory):** `accounts`, `users`, `user_emails`, `repos`, `repos_fts`, redirects,
  `retired_account_names`, `collaborators`, `org_members`, `stars`, `watches`, `follows`, `scope_locators`, and
  operator tables (`admin_audit_log`, `abuse_reports`, `site_abuse_reports`, `instance_settings`,
  `signup_waitlist`, `stripe_events`, `cron_locks`, `hourly_limits`, `artifacts_*`, `action_archives`,
  `action_manifests`, `action_pin_checks`, `lfs_blobs`, `oauth_clients`, `integrations` and its child tables,
  `commit_signatures`, `signing_key_ids`, `commit_email_days`, `r2_gc_marks`, `workflow_event_receipts`).
  Credit, usage and billing tables are account-scoped, not directory.

## Rules

1. **Every query that touches a scoped table goes through `repoDb`/`accountDb`.** No D1 query may read or write
   a scoped table after your change. Check with `rg` over your files when done.
2. **No joins across stores.** A scoped query can't join `accounts`, `repos`, `users`, `collaborators`, or a
   table from another scope. Split it: query the scoped table, collect ids, then look them up
   (`accountsById`, or a D1 query on `repos`), and merge in code. Keep result shapes identical for callers.
3. **One transaction per store.** A D1 `batch` that mixed scoped and directory statements becomes two writes:
   the scoped `batch` first (it is the source of truth), then the D1 directory write. Note the order in a
   comment only if a crash between them matters.
4. **Find the scope id.** Most code already has the repo (`repo.id`, `issue.repo_id`, `pull.repo_id`) or the
   account. When code only has an id or a token hash for a scoped row, use `locate(kind, key)`, and call
   `registerLocator(kind, key, scopeId)` whenever you insert such a row (`forgetLocator` on delete). Kinds are in
   `src/server/scoped-db/locators.ts`; if you need a new kind, add it there and tell the coordinator so the
   backfill migration can include it.
5. **Cross-scope lists become fan-out or indexes.** A query over many repos or accounts (dashboards, admin
   overview, crons, exports) loops over the relevant scope ids from the D1 directory and queries each object.
   Bound the fan-out (most recent N) where the old query had a LIMIT. Cross-repo per-user lists use the
   `UserInbox` involvement index (`src/server/involvement-read.ts`).
6. **Crons that scanned a scoped table** iterate the directory (`SELECT id FROM repos` / `accounts`) and run the
   per-scope query. Data is tiny today; correctness first.
7. **Don't change exported function signatures** unless unavoidable; if you must, update every caller you can
   find with `rg` and list the change in your report.
8. **Tests:** tests that mocked `./db` for scoped tables now also mock `~/server/scoped-db` (path relative to
   the test) with `memoryScopes().module` from `src/server/scoped-db/testing.ts`, seeding rows through
   `memoryScopes().repoDb(id)` / `.raw("repo", id)`. Keep tests passing for your files.
9. **Stay in your file list.** You may create new helper or test files. Do not edit files owned by another
   worker, `wrangler.jsonc`, migrations, or `schema.gen.ts`; ask the coordinator instead.
10. **Never commit or push.** Don't run `git checkout`/`git stash`/`git reset`.

## Done means

- `rg` finds no D1 (`~/server/db`) query on a scoped table in your files.
- `bunx tsc --noEmit` has no errors in your files (others may be mid-edit; ignore theirs).
- `bun test --isolate <your test files>` passes; `bunx oxlint <your files>` has no errors.
- Your report lists: files changed, new locator kinds, signature changes, and anything you could not finish.