---
name: zc-dba
description: Zync Database Authority — read before ANY database work (schema, migration, query, drizzle config, DDL verification, Neon branch ops). Authoritative on Neon project IDs, dialect rules, migration journal integrity, drizzle conventions, audit-log requirement, Floor-4 DDL replay protocol, and DB security rules. Conflicts with older docs: this file wins.
---

# zc-dba — Zync Database Authority

Agent context for ALL database work on Zync.is. Read before touching any schema, migration, query, or drizzle config. Authoritative. Conflicts with older docs: this file wins.

---

## NEON PROJECT

| Purpose | Project ID | Name |
|---------|-----------|------|
| DDL replay / Floor 4 verification | `spring-smoke-45518495` | zync-ddl-verify |
| Production (Hyperdrive binding) | TBD — wrangler.toml still has `PLACEHOLDER_HYPERDRIVE_ID` |

**DDL replay caveat:** `spring-smoke-45518495` main branch may carry a stale foreign schema from a prior session. Always `DROP SCHEMA public CASCADE; CREATE SCHEMA public;` on the fresh replay branch before running `drizzle-kit migrate`. Do NOT run this on main itself without user confirmation.

Region: `aws-us-west-2`. PG version: 17.

---

## DIALECT RULES (HARD — no exceptions)

Target: **Neon Postgres via Hyperdrive**. Never D1, never SQLite, never integer PKs.

| Thing | Rule |
|-------|------|
| Primary keys | `uuid('id').defaultRandom().primaryKey()` |
| Foreign keys | `uuid('*_id').references(() => table.id)` |
| Timestamps | `timestamp('created_at', { withTimezone: true }).defaultNow().notNull()` |
| JSON columns | `jsonb('data').default({}).notNull()` |
| Enums | `text('status').notNull()` + CHECK constraint in raw SQL — never `pgEnum()` |
| Money | `numeric('amount', { precision: 19, scale: 4 }).notNull()` |
| Boolean | `boolean('is_active').default(false).notNull()` |
| UUIDs in raw SQL | `gen_random_uuid()` |
| Timestamps in raw SQL | `TIMESTAMPTZ`, `DEFAULT now()` |

---

## MIGRATION RULES

### Never hand-author migration files

All migrations: `drizzle-kit generate --name <descriptive-name>` only.
Hand-authored files break the journal → invisible to `drizzle-kit migrate` → silently missing in production.

### Journal integrity gate (run after every migration change)

```bash
cd packages/db
# All three counts must be equal
cat migrations/meta/_journal.json | grep '"tag"' | wc -l
ls migrations/*.sql | wc -l
ls migrations/meta/*.json | grep -v _journal | wc -l
# No drift
pnpm drizzle-kit generate --name verify_clean 2>&1
# Must print: "No schema changes, nothing to migrate"
```

### Raw DDL — ONLY for things drizzle can't express

Append AFTER the generated SQL in the same file:

```sql
-- raw_ddl: drizzle-kit cannot express these
CREATE INDEX CONCURRENTLY idx_foo_gin ON foo USING gin(col);
CREATE UNIQUE INDEX idx_bar_partial ON bar(tenant_id) WHERE deleted_at IS NULL;
ALTER TABLE baz PARTITION BY RANGE (created_at);
```

**Items that MUST go in raw DDL (never drizzle index() builder):**
- GIN indexes (`USING gin`)
- Partial indexes (`WHERE ...`)
- Expression indexes
- Partitioned tables (`PARTITION BY RANGE/LIST`)
- Child partitions (`PARTITION OF`)
- Triggers and trigger functions
- Custom CHECK constraints that reference multiple columns

**Why:** drizzle-kit snapshot-truncation bug re-emits these every generate pass if expressed as `index()` calls. Raw DDL section is invisible to the snapshot differ, so no re-emit.

### Wave consolidation pattern

When multiple hand-authored migration files exist (wave fix):
1. Read all files, extract raw DDL items (CREATE INDEX, triggers, partitions, FK/CHECK not expressible in schema.ts)
2. Remove journal entry for highest idx
3. Delete all orphan + duplicate SQL files
4. `drizzle-kit generate --name wave_N_consolidated`
5. Manually append extracted raw DDL to the generated file
6. Verify: generate → "No schema changes"
7. Verify: journal count == SQL count == snapshot count

---

## DRIZZLE SCHEMA CONVENTIONS

File location: `packages/db/src/schema/`

```ts
// Standard table boilerplate
export const fooTable = pgTable('foo', {
  id: uuid('id').defaultRandom().primaryKey(),
  tenantId: uuid('tenant_id').notNull().references(() => tenants.id),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
})
```

**TS6387 warning** — `pgTable` deprecated in newer drizzle versions. Pre-existing codebase-wide. Ignore it. Never fix it (would require touching 127 tables).

---

## QUERY CONVENTIONS

File location: `packages/db/src/queries/`

### ESLint rules on query files

| Rule | Meaning |
|------|---------|
| `zync/require-audit-in-transaction` | Every `tx.insert/update/delete` inside `db.transaction()` must have a paired `tx.insert(auditLog)` in the same scope |
| `no-raw-drizzle-from-routes` | Route handlers must call query functions — never import drizzle directly in routes |

### Audit log requirement

Any `db.transaction(async (tx) => { ... })` that mutates data must also write an audit row:

```ts
import { auditLog } from './_audit-forward'

await db.transaction(async (tx) => {
  await tx.insert(targetTable).values(data)
  await tx.insert(auditLog).values({
    tenantId: data.tenantId,
    actorId: actorId,
    actorType: 'user',
    entityType: 'target_entity',
    entityId: result.id,
    action: 'entity.created',
  })
})
```

**Exemption:** writes that use `db.insert/update/delete` directly (not inside `db.transaction()`) — rule does not fire for non-transactional writes.

Domain-specific audit tables (e.g. `contractAuditLog` in contracts domain) satisfy the rule if they exist inside the same transaction.

### Web Crypto (CF Workers)

No `import { ... } from 'crypto'` (Node crypto). Use Web Crypto API:
```ts
crypto.getRandomValues(new Uint8Array(32))
await crypto.subtle.digest('SHA-256', buffer)
```

---

## CURRENT MIGRATION STATE

As of 2026-06-04 (wave 11 complete):

| Count | Value |
|-------|-------|
| Journal entries | 15 (idx 0–14) |
| SQL files | 15 |
| Snapshot files | 15 |
| Last migration | `0014_wave11_consolidated` |
| Schema tables | 127 pgTable definitions |

All 15 migrations verified against empty Postgres schema on Neon (project `spring-smoke-45518495`, fresh branch, all applied clean).

---

## FLOOR 4 PROTOCOL (DDL Replay)

Run on every wave gate. Steps:

```bash
# 1. Create fresh branch via Neon MCP
#    mcp__Neon__create_branch: project_id="spring-smoke-45518495", branch_name="verify-waveN"

# 2. Get connection string
#    mcp__Neon__get_connection_string: role="neondb_owner", database="neondb"

# 3. Wipe and replay
DATABASE_URL="<conn>" psql -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"
cd packages/db && DATABASE_URL="<conn>" pnpm drizzle-kit migrate

# 4. Verify via Neon MCP SQL:
#    SELECT count(*) FROM drizzle.__drizzle_migrations;  -- must equal journal count
#    SELECT tablename FROM pg_tables WHERE schemaname='public' ORDER BY tablename;

# 5. Spot-check raw DDL items (partitions, GIN indexes, triggers)

# 6. Delete replay branch via mcp__Neon__delete_branch
```

PASS = migration count matches journal; all expected tables present; no SQL errors during apply.

---

## SECURITY RULES

### Webhook URL validation

`assertSafeWebhookUrl(url)` required before any `fetch()` to a user-supplied URL:
- Scheme: `https:` only
- Block: `localhost`, `127.x`, `0.0.0.0`, `10.x`, `172.16-31.x`, `192.168.x`, `169.254.x`, `::1`, `fe80::/10`, `fc00::/7` (fc/fd), `::ffff:` (IPv4-mapped), `::` (any-addr)
- `redirect: 'manual'` on all webhook fetch calls
- **DNS rebinding residual:** CF Workers has no `dns.promises.lookup` — cannot pre-resolve hostnames to verify IPs before fetch. Literal IP blocking is the best achievable at application layer. Platform mitigation: Cloudflare Gateway egress policy.

### isomorphic-dompurify for HTML sanitization

`DOMPurify` alone is browser-only — SSR silently no-ops → XSS. Always use `isomorphic-dompurify`:
```ts
import DOMPurify from 'isomorphic-dompurify'
const clean = DOMPurify.sanitize(html)
```

---

## PACKAGE LOCATIONS

```
packages/db/
├── src/schema/         # pgTable definitions (127 tables)
├── src/queries/        # Query functions (never import drizzle in routes)
├── src/schema/index.ts # Barrel export
├── migrations/         # SQL files (journal-tracked only)
├── migrations/meta/    # _journal.json + per-migration snapshots
└── drizzle.config.ts   # Points at DATABASE_URL env var
```

---

## CHECKLIST BEFORE SUBMITTING DB CODE

- [ ] No hand-authored migration files — `drizzle-kit generate` only
- [ ] Journal count == SQL count == snapshot count
- [ ] Second `drizzle-kit generate` prints "No schema changes"
- [ ] GIN/partial/expression indexes in raw DDL section (never `index()` builder)
- [ ] All `db.transaction()` mutation scopes have paired `tx.insert(auditLog)`
- [ ] No `import ... from 'crypto'` — Web Crypto API only
- [ ] UUID PKs, TIMESTAMPTZ, JSONB, text+CHECK enums (no pgEnum, no integer PKs)
- [ ] Money columns: `numeric({ precision: 19, scale: 4 })`
- [ ] User-supplied URLs validated with `assertSafeWebhookUrl` before `fetch()`

---

## Learned Rules

### neon-http-tagged-template | fired:1 | 2026-06-10
Calling `.query('select 1')` on the `@neondatabase/serverless` http client → wrong; the http client has NO `.query()` method, it uses tagged-template syntax only: `` sql`select 1 as ok` ``. Agent confidently asserted `.query` exists, build failed `s.query is not a function`, resorted to a fake-TemplateStringsArray hack.
Prevent: with neon http client always use `` sql`...` `` tagged templates; never call `.query()`. For dynamic SQL build a real TemplateStringsArray or use a pooled client.

### drizzle-insert-jsonb-tuple | fired:1 | 2026-06-10
Converting a raw-SQL `audit_log` insert to `tx.insert(auditLog)` assuming identical semantics → wrong. Drizzle wraps values written to a jsonb column (e.g. `changes`) as `[null, value]` tuples; the raw SQL wrote a scalar. Silent behavior change none of the 7 floors detect; downstream consumer expected one shape.
Prevent: when swapping raw SQL ↔ drizzle on a jsonb column, diff the actual stored shape (scalar vs `[null,value]` tuple) against every consumer before committing.

### verify-schema-column-names | fired:1 | 2026-06-10
Referencing columns/tables by assumed names → wrong: `calendar_connections.providerAccountId` (real: `external_user_id`), invoice `total_amount` (real: `total`), `contacts` (real: `customers`), projects `budget`/`cost_to_date` (do not exist). Caused TS2339 and wrong specs.
Prevent: grep `packages/db/src/schema/` for the actual column/table name before referencing it in code OR spec; never infer column names from domain intuition.

### trace-import-usage-before-remove | fired:1 | 2026-06-10
Lint-fixer removed `lte` from drizzle-orm imports as "unused" → wrong; it was used on line 199 (`resolveStatutoryWithholdingRate`), breaking the build.
Prevent: before removing any "unused" import, grep the whole file for the symbol; trust full-file usage trace over the linter's single-pass claim.