Waves DB rules

1. Always pass the knexfile

Every knex CLI invocation MUST name the knexfile explicitly, with the path
relative to your current working directory:

# from the repo root:
npx knex <command> --knexfile server/knexfile.js

# from server/:
npx knex <command> --knexfile knexfile.js

Without --knexfile, knex's default discovery picks the wrong config (or
none) depending on cwd, and has silently mis-targeted prod before. The flag
is resolved against cwd too — --knexfile server/knexfile.js run from
inside server/ fails looking for server/server/knexfile.js. This
applies to migrate:latest, migrate:make, seed:run — all of it.

2. The timestamptz window leak

The portal's behavior layer is all Eastern Time, but storage is
timestamptz (UTC). The trap: interpolating a naive ISO string (no
offset) into a WHERE clause — Postgres reads it as UTC and your "today in ET"
window silently shifts 4–5 hours. That moves boundary rows into the wrong
day/week (reminders, digests, KPI windows).

Rules:

• Build ET boundaries with server/utils/datetime-et.js
  (parseETDateTime, etDateString) and pass real Date objects to knex —
  never hand-built 'YYYY-MM-DDT00:00:00' strings.
• Crons are scheduled with { timezone: 'America/New_York' } — keep their
  query windows in the same frame.
• When reviewing a diff, any string literal that looks like a timestamp
  inside .where(...) is a finding until proven offset-aware.
• The trap runs the other way too: a timestamptz column takes a SINGLE
  AT TIME ZONE — reviewers have falsely flagged correct timestamptz SQL
  as naive. Verify the column's udt_name in the target environment before
  accepting the finding; production access still requires §3 authorization.

3. Local DB access — Codex uses dev/preview only

Default for agent sessions (per the AGENTS.md database policy): use a
Railway dev or preview Postgres branch as DATABASE_URL. Do not point a
session at production for routine work, and never run migrations or other
writes against prod from a local machine.

Codex sessions never connect to production databases, including for
read-only verification. The AGENTS.md prohibition applies throughout this
skill. Codex must not obtain or use production database credentials; when
production evidence is needed, request it from an authorized operator and
continue independent work against dev/preview.

For other operators whose applicable policy permits it, prod access is
break-glass: owner-authorized for the specific task,
read-only (SELECT), and through a restricted role when one exists for the
domain (e.g. newsletter_verifier) instead of the full-write URL. This
skill deliberately ships no copy/paste prod connection command — get the
credential for the restricted role from the owner at authorization time.

When authorized, three operational facts (not a recipe):

• The private DATABASE_URL is unreachable from outside Railway; the
  public endpoint lives in the Postgres service's env, not the app
  service's (the app service's value fails with SSL errors).
• Export the credential under a task-specific name (e.g. PROD_RO_URL)
  and have the script read that name explicitly — never set DATABASE_URL
  in your shell, so stray knex/node tooling that defaults to
  DATABASE_URL cannot silently target prod.
• Each Bash tool call is a fresh subshell — inline the env var in the same
  command; an export in a previous call does not persist.

Never print the URL or paste it into logs/PRs. Prod writes outside a
deployed migration additionally need Adam's explicit authorization, a stated
expected row count, and a dry-run first. Remember: direct prod DB writes
fire ZERO customer communications — that's sometimes the point (bulk
schedule inserts) and sometimes the bug (you expected a reminder to send).

4. Migrations

• Filename convention: YYYYMMDD0000NN_short_name.js in
  server/models/migrations/.
• Never edit an already-run migration — and a migration file that is
  already on the PR's remote branch HAS run (the Railway preview deploys
  every push). Knex tracks by filename — an edit is a silent no-op in
  every environment that already ran it, review-round fixes included. Ship
  a NEW migration instead. Enforced: the pre-push hook's applied-migration
  guard (scripts/hooks/pre-push) blocks a push that modifies, renames, or
  deletes any file under server/models/migrations/ that exists at the
  remote branch's sha (or at the merge base with origin/main for a new
  branch). Content that already equals origin/main's passes: merging
  main after main repaired a migration in place is not your edit. The
  guard sees pushes, not deploys: SKIP_MIGRATION_GUARD=1 is
  for exactly two VERIFIED cases — the file never ran (its deploy failed
  before migrating; no knex_migrations row on the preview/prod — a failed
  first migration cannot be repaired by a second file, knex re-runs the
  first) or the preview database was cleaned by hand (below).
• Never delete, rename, or re-stamp a migration file once ANY deploy ran
  it — Railway PR previews included. Every push to an open PR deploys a
  preview environment with its own Postgres that runs the branch's
  migrations; if a later push removes or renames that file, knex fails
  every subsequent deploy of that environment with "The migration
  directory is corrupt, the following files are missing" (the merge state
  shows UNSTABLE from the Railway check). Superseding a migration mid-PR
  means a NEW file with a fresh stamp whose up reverses the old one; the
  old file stays. If the damage is already done, prove prod never ran the
  file (knex_migrations read-only), then clean the PREVIEW database
  (delete its knex_migrations row and undo the DDL by hand) so the
  preview deploys again (#3869, 2026-09-05).
• House style: exports.up = async function up(knex) with a symmetric
  down; guard with hasTable on the first line and per-column
  hasColumn before alterTable — idempotent in both directions.
• Migrations that touch seeded/admin-editable rows must preserve admin edits
  (read-modify-write the row, don't overwrite wholesale) and write an audit
  row when an audit table exists. Exemplar:
  server/models/migrations/20260611000003_pest_footprint_1750_bracket.js.
• pg-function splice migrations (string-editing a function body): wrap the
  original IF condition in parentheses before AND-ing onto it; fixtures must
  be the verbatim pg_get_functiondef text from prod; simulate the splice
  against that prod text before merge.
• Migration writes get a dry-run inside BEGIN; … ROLLBACK; against a
  dev/preview Postgres branch that mirrors prod schema before merge —
  mocked tests miss type/constraint mismatches, and one failed migration
  blocks EVERY Railway deploy (migrations run pre-deploy). Never execute
  migration DDL/DML against prod from local, even wrapped in a rollback
  (it takes locks and can fire side effects, and the read-only role can't
  run writes anyway) — verify the schema/type assumptions the migration
  relies on via read-only SELECTs instead (§3, §5).
• After merging a PR that ships a migration, verify the deploy actually ran
  it (deploy log or knex_migrations) before relying on the new schema.
• A migration that keeps an ownership record derives its key from its own
  stamp (migration.<stamp>.state in system_settings, the
  migration:<stamp> audit tag). Stamps are NOT unique across files, so
  after merging origin/main check no other migration derives the same
  literal — a shared key means the later up() overwrites the earlier
  file's rollback record and the later down() deletes it. Enforced by
  server/tests/migration-state-key-uniqueness.test.js (also fails on a
  stale stamp after a rename); fix = take a free stamp, never share one.
• The 000018 pending-count illusion.
  20260415000018_pest_service_cost_truing.js is a no-op placeholder
  (source lost, recreated empty), and …000019 deleted its
  knex_migrations row ONCE when 000019 itself ran — so in an environment
  that has run 000019, 000018 reads as pending again and the next
  migrate:latest (or a migrate:up, instead of your migration) re-runs
  the harmless no-op and chain counts come out one higher than expected
  for that run. Do NOT conclude migrate:down is broken from any of this
  (that wrong call shipped once). To test a specific migration's up/down,
  require() the module and call up(knex)/down(knex) directly against
  a throwaway DB.
• Seed/data-correction migrations whose up() preserves admin edits must
  make down() a DOCUMENTED NO-OP — a blanket revert erases exactly the
  admin edits up() preserved. Seed rollbacks are never destructive.
• SMS-template-seeding migrations dated before 20260706000010 must seed
  bodies as fixed points of its normalizeTemplateBody (trailing
  "Reply STOP to opt out.", "Hello {first_name}" greeting, no phone
  numbers) — the normalizer already ran on prod, so late-seeded rows are
  never normalized (compliance gap + prod/fresh drift).
• audit_log is append-only: rows are NEVER deleted, and all writes go
  through services/audit-log.js (recordAuditEvent — pass trx or the
  write silently uses its own handle). A migration down() APPENDS a
  rollback event, never deletes.

5. Raw SQL verification

• Execute new query shapes and column references against a verified
  dev/preview PostgreSQL database with the relevant schema before claiming
  database verification. Mock builders do not verify SQL identifiers,
  types, constraints, or transaction behavior.
• For a task whose acceptance criteria concern the deployed schema, obtain
  deployment evidence or production evidence supplied by an authorized
  operator. Codex must not connect to production to collect it. Other
  operators may use restricted read-only access only under §3 and their
  applicable policy. Dev verification alone does not establish production state.
• Without the required safe database or authorization, report verification
  blocked and continue independent implementation or static checks.
  Never substitute production credentials for missing development access.
• db.raw() with request-derived input must use ? placeholders; string
  interpolation is a SQL-injection P0 (AGENTS.md).

5b. A try/catch inside a transaction is NOT fail-open

Postgres aborts the entire transaction after any failed statement. Catching
the JS error and returning a default does not restore it — every later
statement on that connection fails with current transaction is aborted, so a
"best-effort" read takes down the whole operation hosting it. This has now bitten
twice: the inspection-credit booking marker (#3178 r4 P1) and the accept-path
catalog lookup (#3328 r1 P1), where a lookup meant to degrade gracefully during
deploy skew would instead have failed the estimate accept itself.

Any optional/best-effort query that runs on a caller's trx MUST be wrapped in
a SAVEPOINT — await trx.transaction(async (sp) => { ... }) — so a failure rolls
back only that statement. Exemplar:
markBookingForInspectionCredit in server/services/inspection-credit.js.

Corollary for tests: a hand-rolled fake connection cannot reproduce this —
it has no transaction semantics, so the broken version passes. Prove it against
a real Postgres transaction (rename the column away inside a transaction you
then roll back, and assert the transaction is still usable).

5c. An array column cannot carry a scalar unique index

When a lookup column is a jsonb/array (one row owning several keys), there is no
unique index to stop two rows claiming the same key — and an unordered .first()
then resolves NONDETERMINISTICALLY. Fetch up to two and resolve only on exactly
one match (fail closed); a CI/contract test catches existing drift but cannot
prevent the runtime write (#3328 r3 P1).

5d. A JS array into a jsonb column must be JSON.stringify'd

pg serialises a JS ARRAY as a Postgres ARRAY literal ({...}), not JSON — so
insert({ overridden_floors: [{...}] }) into a jsonb column fails with
invalid input syntax for type json in prod while an in-memory test store
accepts it happily. A plain object is fine (pg JSON-encodes objects); an array
needs JSON.stringify(arr). Read side: pg hands jsonb back parsed, so a reader
that must also work over a fake store parses strings defensively. Caught only
by the real-DB ui-verify walkthrough (the step 4 PR 2b "Acquire anyway" 500).

6. Schema truth traps (wrong-answer generators)

• customers.active is TRUE for leads. A "real customer" =
  pipeline_stage IN (active_customer, won, at_risk) — use the
  whereRealCustomer helper. customers.status does not exist.
  member_since is canonical; customer_since is dead.
• referrals.id is a UUID even though migration 000054 reads increments
  (a dead hasTable-guarded no-op). Migration text lies; prod schema doesn't.
• services (the catalog) names its display column name, NOT
  service_name — when downstream code reads row.service_name off a
  join, select sv.name as service_name. (service_key IS the real key
  column.)
• scheduled_services.status is gated by a CHECK constraint — a new status
  string without a migration extending the CHECK throws at runtime and CI
  won't catch it (AGENTS.md P0).
• service_requests backs TWO flows; its partial-unique dedup index only
  releases on terminal status — never remove the admin resolve PATCH.
• Verifying a reschedule: check scheduled_date + the HTTP 200 in Railway
  logs. updated_at and reschedule_log are dead signals (false
  negatives).
• Staging has its OWN database and vault key — never copy encrypted
  ciphertext between DBs, and failed staging twins are not prod failures.
• estimates.notes and scheduled_services.notes are CUSTOMER/TECH
  VISIBLE (estimate-public.js serves estimate.notes;
  scheduled-service notes render into tech Property Alerts). Internal,
  calibration, or ops-audit text about a visit goes in
  scheduled_services.internal_notes, NEVER notes. The estimates
  table has NO internal_notes column — internal text about an estimate
  must live outside the estimates row entirely (admin-only/audit tables),
  never in estimates.notes. This has bitten twice.
• notification_prefs.updated_at is published as the marketing-SMS consent
  capturedAt (marketingSmsConsentBasisForContract) — a system backfill
  must NEVER restamp it (falsifies consent provenance); it's also the only
  reliable "unchanged since our write" marker for a migration down().
• lead_source='square' means legacy import, not a marketing channel
  (lead_source_channel is NULL on those rows).
• Completion truth lives in scheduled_services — many recurring customers
  have ZERO service_records rows ever, so "last completed service" read
  from service_records (or the app) is a false-lapse signal.
• Churn source of truth is CURRENT pipeline_stage='churned' — never
  churned_at (sparse AND sticky; survives reactivation). Churn time =
  pipeline_stage_changed_at. customer_ltv is populated only by the
  admin pricing-strategy recalculation action
  (admin-pricing-strategy.js → pricing-intelligence.js) — verify row
  counts before relying on it; it is empty unless that has been run.
• Admin in-app notifications = the notifications table with
  recipient_type='admin'; the type column is category, not type.
• customers.property_sqft = treated LAWN area per the source-arbitration
  contract — NEVER a building-footprint/home-sqft source.
• payments has NO invoice_id column — link via the PI id + metadata
  variants (mirror findInvoiceForPayment); stripe_surcharge_status is
  never written.
• The uuid-vs-integer split is PER-TABLE: referrals.id is UUID but
  referral_invites.promoter_id / referral_promoters.id ARE integer —
  verify each table in the target environment under §5.
• scheduled_services.scheduled_date is a plain DATE (::text to
  inspect; no timestamptz comparisons), and any direct SQL date move must
  sync appointment_reminders too.
• One-shot guard stamps need DEDICATED columns — estimate_data (jsonb)
  full-blob writers can erase embedded stamps and un-burn caps.
• Customer-merge FK discovery must stay DYNAMIC from information_schema
  (110+ tables reference customers.id; 19 have undeclared FKs, incl.
  leads) — never hardcode the table list. The retired-loser phone
  sentinel merged-<id8> is load-bearing for intake lookups.
• message_drafts has a prod insert-guard trigger
  (message_drafts_disabled_guard) that blocks non-allowlisted intents —
  a new pending-queue intent needs a pg-function splice migration (§4) or
  its drafts die silently.
• sms_templates: a MISSING row is ACTIVE (isTemplateActive) — deleting
  a disabled template row silently ENABLES the blocked send. The
  ai_assistant/ai_approved/ai_revised rows are ops kill switches —
  never delete them. Template-inactive ≠ feature-off (confirmation/72h/24h
  reminders fall back to EMAIL), never disable rows to change scheduling
  (kills the original_message_type mapping), and strip hardcoded fallback
  copy when retiring an SMS action (it sends even with templates deleted).

7. Verification before "done"

Confirm data changes by SELECTing the affected rows (or matched/updated
counts for backfills), not by absence of errors. Migration merged ≠
migration ran. If something could not be verified (no dev DB in the
sandbox, no authorized prod access), say so explicitly instead of implying
verification happened.