Lanna Care for Animals

Custom web app for Lanna Care for Animals, a not-for-profit animal shelter, replacing their current AppSheet/Google Sheets system.

Full requirements: docs/requirements/lanna-care-rebuild-requirements.md. Decisions and open questions: docs/decisions/ (one file per decision, from 2026-09-24) and the earlier record in docs/decisions.md.

Stack

• Next.js (App Router) + Tailwind CSS
• Supabase (Postgres + Auth + RLS) — see supabase/migrations/
• Google Drive API for file/photo storage (see docs/decisions.md), except facility plan images, which live in a private Supabase Storage bucket (src/lib/facility-map/plan-store.ts)
• @react-pdf/renderer for resident profile / deceased summary PDFs
• Hosting: a Cloudflare Worker (free tier, via @opennextjs/cloudflare) fronts the site — edge cache for the public pages, then the Raspberry Pi origin through a Cloudflare Tunnel, then rendering in the Worker itself as the fallback. See docs/pi-hosting.md.

Everything is chosen to run on free tiers — see docs/decisions.md for the reasoning and the trade-offs that come with that.

Roles

A role is a row of the roles table (0132), not a database enum: 0181 dropped app_role, user_roles.role and current_user_role(). A login holds one role through user_roles.role_id, assigned at /admin/security, and each role is granted a set of activities in role_permissions. Row-level security policies and app pages both ask has_permission() (src/lib/permissions/) rather than naming a role. The live roles are admin, management, second_in_command, head_of_maintenance, head_of_medical, doctor, volunteer and public_viewer; staff was retired on 2026-10-09 (0173, docs/decisions/2026-10-09-staff-role-removed.md). A doctor belongs to a clinic and sees that clinic’s records only (RLS since 0167/0178).

What each role can do today is Settings → Who does what (/admin/who-does-what, Admin and Management): every activity × every live role, read live from role_permissions, with each box the system does not yet obey marked from src/lib/permissions/fixed-for-now.ts. That list is measured, not written: node scripts/check-who-does-what.mjs (dev) runs every box under each role, as it is and flipped, and fails when the list is wrong or stale; node scripts/check-who-does-what-page.mjs checks the page against the database (docs/decisions/2026-10-11-who-does-what-view.md). The plain-language version is docs/roles-and-permissions.md.

A person who leaves is archived from /admin/security rather than deleted (user_roles.archived_at, 0063): every permission check answers no for them, so every policy and page treats them as having no access, while their name stays on the maintenance jobs they did. Restore reverses it. Archived and role-less logins are refused at sign-in, by password or Google, and a session archived after it signed in is signed out at its next app page.

Settings → Security needs 2-step verification (docs/decisions.md, 2026-09-27). The page and every action behind it require a session that has passed Supabase Auth’s authenticator-app (TOTP) factor — aal2 in the JWT (src/lib/auth/two-step.ts). The first visit sets up the app at /admin/security/verify; after that it asks for a code once per sign-in. The actions check aal2 themselves, because they write with the service role, which bypasses the aal2 RLS policies on user_roles (0100). Nothing else in the app asks for it. A lost phone must not lock the shelter out, and there are no backup codes, so recovery is:

1. Another admin who has passed 2-step presses Reset in the 2-step column of the users table on Security. The person sets the app up again the next time they open Security.

2. Last resort, when no admin can pass 2-step (the only admin lost their phone): the developer removes the factor with the service role —

   node scripts/bootstrap-admin.mjs --env production --reset-2step [email protected]
   

   It changes nothing but that login’s authenticator app, and runs whatever roles exist.

Keep at least two admins enrolled so route 1 is always available. scripts/check-two-step-session.mjs re-measures on dev what the sessions report.

Getting started

1. Install dependencies

   npm install
   

2. Set up environment variables

   Copy .env.example to .env.local and fill in:

   • A Supabase project’s URL/keys (create one free at supabase.com)
   • Google OAuth client credentials + refresh token for the shelter’s Drive account (see src/lib/google/drive.ts for how this is used). node --env-file=.env.local scripts/check-drive-token.mjs says whether they still work; on a deployed site the top of Settings (/admin) does

3. Apply the database schema

   node scripts/apply-migrations.mjs
   

   Applies every file in supabase/migrations/ that the project hasn’t seen yet, in order, and records each one in a schema_migrations table there. --status shows what’s applied and pending; --dry-run runs pending files inside a rolled-back transaction. Needs SUPABASE_ACCESS_TOKEN (a personal access token from the Supabase dashboard) in .env.local; the target project comes from NEXT_PUBLIC_SUPABASE_URL.

   A migration that creates a table, view or sequence must grant it in the same file (authenticated, service_role by default, anon only for deliberately public objects). From 2026-10-30 Supabase no longer adds those grants automatically. npm run lint fails a migration that leaves them out (scripts/check-migration-grants.mjs; see docs/decisions.md, 2026-09-24).

4. Run the dev server

   npm run dev
   

   Open http://localhost:3000.

   From a phone on the same Wi-Fi: next dev listens on every interface, so http://:3000 works too (ipconfig → IPv4 Address). next.config.ts already allows dev assets for 192.168.1.*; for Google sign-in to come back to the phone the dev Supabase project’s Redirect URLs (Authentication → URL Configuration) must also list http://192.168.1.*:3000/auth/callback, or Supabase bounces the sign-in to its Site URL (localhost).

5. Enable the git hooks (once per clone)

   git config core.hooksPath .githooks
   

   .githooks/post-commit pushes the current branch to GitHub after every commit, so the remote always matches the local checkout. .githooks/pre-commit refuses a commit that adds a migration not numbered one past the highest on origin/main, and names the file to rename it to (scripts/check-migration-numbers.mjs, which CI runs too). It never fetches, and it lets the commit through with a note when it cannot give a verdict.

6. Check out the backlog worktree (once per clone)

   git worktree add ../Animal_Shelter_Backlog backlog
   

   backlog is a permanent branch that only ever changes docs/backlog.md. Having it in its own folder means an item can be added at any moment without touching whatever the main checkout is in the middle of; see CLAUDE.md “The backlog branch” for how it is merged back.

7. Feature worktrees (one per workstream)

   node scripts/worktree.mjs new 
   

   Creates ../Animal_Shelter_ on branch claude/ from origin/main, copies .env.local in, runs npm ci and picks the next free dev-server port (recorded in .port; node scripts/worktree.mjs dev uses it). The main checkout stays on main; each workstream gets its own folder, dev server and Claude session, so two or three features can be built at once. list, sync and done cover the rest of a stream’s life — see CLAUDE.md “Workstreams”. Google sign-in on a new port needs http://localhost:/auth/callback in the dev Supabase project’s Redirect URLs, once per port.

Environments

Four environments, three of them deployed. UAT exists in the code and the tooling but not yet as a site: until the production domain cutover, lannacare.org is served by the production block and --env uat refuses to deploy. The UAT column is what the cutover makes true.

Dev and Test are recoloured (teal instead of orange, greenish surfaces, a Dev badge beside the logo once signed in) so a tab on the dev database is never mistaken for the live site. UAT is deliberately not recoloured — the customer should be testing the real thing — and differs from Production only by its UAT badge and the watermark on the archive PDF (src/lib/archive/resident-summary-pdf.tsx), which lands in a Drive that dev, Test and UAT share. src/lib/app-env.ts decides from the Supabase project ref the build was made with — not from NODE_ENV, which is production on Test too — and the layout sets ``, which globals.css keys the colour tokens on. Its UAT_PROJECT_REF is empty until the cutover, because the project that will be UAT is production’s today; set early, it would badge the live site.

Each person can also pick a colour theme (account menu → Colours), which redefines the same tokens and so overrides dev’s teal. That is why the header’s teal-and-black strip and the badges use fixed colours no theme touches: under any theme they still say Dev or UAT (docs/decisions/2026-10-10-user-colour-themes.md). Check colours with node scripts/check-theme-contrast.mjs after changing any.

Locked public website. "PUBLIC_SITE": "locked" on a Worker block in wrangler.jsonc (test, UAT, and production until the cutover) closes the public pages to anyone signed out: / shows a sign-in landing page, other pages redirect to /login and come back after sign-in, and every response is noindex. Only /login, /login/forgot, /auth/callback, /privacy and robots.txt stay open. Signed-in staff see everything as usual. src/lib/public-site.ts has the rest; docs/decisions.md (2026-09-25) has why. To look at it locally, put PUBLIC_SITE=locked in .env.development.local (gitignored) and restart next dev.

What the cutover changes here, in one commit: UAT_PROJECT_REF in src/lib/app-env.ts and production’s label in src/app/releases/page.tsx; the lannacare.org routes move from the production block of wrangler.jsonc to uat, and production gets the new domain, RELEASE_MAIL_ENV Production and a From on that domain, and loses "PUBLIC_SITE": "locked", or the live site opens on a sign-in page (scripts/deploy.mjs refuses --env production while the block is off lannacare.org and still locked); SITE_ORIGINS.production in scripts/deploy.mjs and HOST= in scripts/pi/deploy-pi.sh follow. A host move that carries the staff app with it also changes src/lib/site-hosts.ts (the one list behind serverActions.allowedOrigins in next.config.ts and the upload routes’ same-origin.ts) and httpHostHeader in scripts/pi/cloudflared-config.yml; miss either and every Server Action or upload is refused on the new host while every page still loads. node scripts/check-site-hosts.mjs fails if the two disagree. Outside the repo: today’s .env.deploy.production becomes .env.deploy.uat, a new .env.deploy.production holds the new project’s values, and node scripts/deploy.mjs --env uat --secrets gives the UAT Worker its secrets. The Pi has no lanna-care-uat service yet; setup.sh installs only lanna-care.

Two databases, both on Supabase’s free tier (the org allows two). Test deliberately shares the dev database: one developer, throwaway data, and what Test is for is exercising a real Workers build of main on phones before it goes to production. Production holds the shelter’s real records. Both are free-tier projects, which means no automatic backups beyond the daily scripts/backup.mjs dump — see “Backups” below. Free projects also pause after seven days with no requests at all, which the public pages make impossible while the site is up.

scripts/lib/env.mjs is the one place that knows which values belong to which environment; scripts/deploy.mjs, scripts/apply-migrations.mjs, scripts/backup.mjs, scripts/check-public-views.mjs and scripts/bootstrap-admin.mjs all take --env test|uat|production (default test) and print the environment and Supabase project ref before doing anything. --env uat stops at once while .env.deploy.uat does not exist, which is until the cutover.

.env.deploy.production (gitignored) holds the production NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY and SUPABASE_SERVICE_ROLE_KEY (and SUPABASE_DB_PASSWORD for backups); anything it doesn’t set — currently the whole Google Drive set — falls through to .env.local. Next.js and OpenNext never read that file on their own, which is what keeps a production build from happening by accident: only scripts/deploy.mjs puts its values in the shell.

Each environment’s values file also carries NEXT_SERVER_ACTIONS_ENCRYPTION_KEY, the same value on the dev machine and on the Pi, so the Worker’s build and the Pi’s build accept each other’s forms (docs/pi-hosting.md, step 1b; node scripts/actions-key.mjs --generate makes one).

Deploying to Cloudflare

The app deploys to Cloudflare Workers through the @opennextjs/cloudflare adapter (wrangler.jsonc, open-next.config.ts). Google Drive access is a plain-fetch client (src/lib/google/drive.ts) specifically so it runs on the Workers runtime — the googleapis SDK does not (see docs/decisions.md).

1. Log in to Cloudflare (once per machine)

   npx wrangler login
   

   Run it from a terminal you can leave open while you click Allow in the browser it opens — it gives up after about five minutes. If PowerShell refuses with “running scripts is disabled”, use npx.cmd instead of npx (the .cmd shim skips the execution policy).

   Every build goes through scripts/win-junction-symlinks.cjs, which on Windows turns the directory symlinks OpenNext creates for Next’s hoisted ESM packages into junctions — real symlinks need Developer Mode or an elevated shell, and the build dies with EPERM … symlink without it. It is a no-op elsewhere.

   Builds are safe to run while a next dev is up. next build and npm run typecheck type-check with tsconfig.build.json, which is tsconfig.json minus .next/dev — the route types the dev server rewrites on every compile. Before that (2026-09-22) a build that read them mid-write died with a wall of TS1005 in routes.d.ts / validator.ts. The editor still uses tsconfig.json, so it sees the live dev types.

2. Preview locally on the Workers runtime — what next dev can’t simulate. It reads .env.local directly, no extra setup:

   npm run preview
   

3. Deploy to Test

   npm run deploy:test
   

   Builds with the dev Supabase values, empties the env-file snapshot OpenNext bakes into the bundle (scripts/strip-baked-env.mjs — check its removed N env var(s) line appears), and uploads the lanna-animal-care-test Worker. Add --secrets after node scripts/deploy.mjs --env test to (re)upload the five runtime secrets from .env.local first — needed once per Worker and whenever a value changes; npx wrangler secret list --env test shows what is set.

   Every production release also goes to test, from the same commit (standing rule, 2026-09-24; the checklist is in docs/release-smoke-test.md). Test is where main is exercised on the Workers runtime, so test drifting behind production makes it useless as a pre-production check — on 0.1.0 production shipped while test sat on 0.0.1. Test being a few commits ahead between releases is expected and healthy; behind is the failure. Deploy test first where the release allows it, since a build that is going to fail should fail somewhere that is not the live site.

   Two consequences of how the builds work. Each environment needs its own build: next build inlines the Supabase URL and anon key, so --skip-build cannot be reused across environments without shipping dev-pointing code to production — budget twice the time, not one build deployed twice. And check what is already on test before overwriting it: test holds one build, and it is sometimes deliberately an integration build that exists nowhere else (on 2026-09-23 it held main + cashflow + claude/utc-today for an overnight timezone check, which a blind test deploy would have destroyed).

   Syncing the two means the same code against different databases — test runs the dev project. It catches a broken build, a Workers-runtime problem or a UTC-in-Workers bug; it will not reproduce anything data-shaped.

4. Deploy to UAT or Production

   npm run deploy:uat
   

   npm run deploy:prod
   

   Same steps with .env.deploy.uat / .env.deploy.production, and a guard: it refuses unless the checkout is main, clean, and identical to origin/main, so what the customer tests and what is live are always commits GitHub has. Merge first, then deploy. Every environment, Test included, also refuses when its env file points at a project src/lib/app-env.ts would badge as something else — which is why deploy:uat fails today: UAT has no project of its own until the cutover.

   Why the strip step matters. The OpenNext adapter copies everything in .env.local into the Worker bundle as a fallback for any variable not set on Cloudflare — convenient for npm run preview, but it would ship dev credentials (and unrelated things like SUPABASE_ACCESS_TOKEN) to production. With the snapshot emptied, a secret you forgot to set fails loudly at runtime instead of silently using the dev value. To preview the stripped build locally the way production runs, put the five values in a gitignored .dev.vars file (wrangler’s local stand-in for secrets).

   Never interrupt an OpenNext build on Windows (2026-09-23, which cost four deploy attempts). The bundling step rewrites symlinks under node_modules — that is what scripts/win-junction-symlinks.cjs is managing — so killing the build mid-way can leave a package as an empty directory. Here it gutted @react-pdf/reconciler, and the damage only surfaced on the next build as Module not found: Can't resolve '@react-pdf/reconciler' pointing at src/lib/archive/resident-summary-pdf.tsx, several minutes into a run that looked fine. If a build has to be stopped, treat npm ci as mandatory before the next attempt; node -e "…" over package-lock.json comparing each entry against a package.json on disk is how the two gutted packages were found. Prefer letting a bad build finish and rolling back after (the Pi: ./scripts/pi/deploy-pi.sh --ref ; the Worker fallback: npx wrangler rollback --env production) over killing it.

   npm ci alone is not always enough, and neither is that audit. Later the same day a worktree had every one of the 713 packages present with its package.json intact — so both the audit above and npm ci itself reported nothing to do — while files inside packages were missing: next/server.d.ts, next/headers.d.ts, next/types.d.ts, deleted by an interrupted purge. npm ci decides from package presence, so it no-opped, and npm rebuild restored the .bin shims without restoring any file. The only thing that caught it was running the compiler: error TS7016: Could not find a declaration file for module 'next/server'. So when a build fails on missing types or a missing module that the lockfile clearly contains, do not trust a package-level audit — purge node_modules outright and reinstall. On Windows rm -rf and rmdir /s /q both stall or fail on the deep node_modules tree (the Cloudflare SDK’s resource folders exceed the 260-character path limit); what works is mirroring an empty directory over it and then removing it:

   robocopy %TEMP%\empty node_modules /MIR /NFL /NDL /NJH /NJS /R:1 /W:1
   rmdir /s /q node_modules
   npm ci --no-audit --no-fund
   

   Capture real exit codes when running the gates. npm run build | tail makes $? the exit status of tail, not of npm, so a failed build reports success — redirect to a file and read $?, or use ${PIPESTATUS[0]}. A test plan ticked from piped output is ticking something that never passed. node scripts/gates.mjs runs all four (typecheck, lint, test, build) and prints each exit code, which is why it exists.

   There is a test runner: npm test. It is Node’s built-in node --test (no dependency) over the *.test.mjs files beside the pure security modules (public-paths, next-path, same-origin, can, password-change, worker/origin, csp-report, translations). It takes about a second and needs no database or network. scripts/test-support/ lets the tests import the .ts files as written. The 130-odd scripts/check-*.mjs are still separate harnesses, several of them database-driven. Why and how: docs/decisions/2026-10-12-node-test-runner.md.

   Tracked files are CRLF on disk — preserve the line endings when editing one by script. The repository stores LF, but Git for Windows ships core.autocrlf=true in its system gitconfig, so every checkout writes \r\n (git ls-files --eol shows i/lf w/crlf). .gitattributes pins only scripts/pi/* to LF. Git keeps reporting LF will be replaced by CRLF, which is easy to read as noise, and Git Bash’s grep '\r$' finds nothing on these files, so a quick check says they are LF when they are not; tr -cd '\r' < f | wc -c counts them. Two consequences, both hit while cutting 0.2.0 on 2026-09-24. A string anchor written with \n silently fails to match, so a script that edits the file reports something like unreleased opening not found and looks like a parsing bug in the file it is reading. Worse, writing LF back converts the whole file, so a seventeen-line change arrives as a whole-file diff and the actual edit is invisible to review. Detect the existing endings, edit with \n internally, and write back what you found:

   const raw = readFileSync(f, "utf8");
   const crlf = raw.includes("\r\n");
   let s = crlf ? raw.split("\r\n").join("\n") : raw;
   // …edit s with \n anchors…
   writeFileSync(f, crlf ? s.split("\n").join("\r\n") : s);
   

   ERROR Failed to copy … color-string and … data-uri-to-buffer are cosmetic. They appear during Building server function on every run, with or without a clean .open-next, a dev server, or a fresh node_modules, and the packages land in the bundle anyway — verified in .open-next/server-functions/default/node_modules/ after a successful deploy. Do not go looking for a missing dependency; three separate diagnoses of these lines were all wrong. Note also that a green next build proves only build-time resolution: Turbopack resolves the server-component import graph, which is why the reconciler failure above was fatal, but OpenNext’s copy step is a later, separate stage, so build success alone does not prove the bundle is complete. And a deploy:prod failure is not necessarily a code problem — CI passed on f32f2c1 three times while this machine could not build it, because CI runs npm ci on a clean runner and this checkout’s tree was damaged.

   The hostnames are Workers custom domains (routes in wrangler.jsonc) on the zone registered in the same Cloudflare account, so Cloudflare manages DNS and certificates; the *.workers.dev URLs are disabled. Every origin an environment answers on must be in its Supabase project’s Redirect URLs (step 6) as https:///auth/callback, since sign-in and password reset redirect through the requesting origin.

5. Database migrations. Dev/Test first, production once the branch is merged (CLAUDE.md has the rules). First, what production has not run — it compares production’s schema_migrations with the files on origin/main, names both halves (files not applied, rows with no file) and exits 1 if they disagree:

   node scripts/apply-migrations.mjs --drift production
   

   Production and UAT refuse to apply, dry-run or baseline from a checkout whose supabase/migrations/ differs from origin/main’s in any way, before reading an env file or reaching the database; there is no override, so merge first and run from the main checkout.

   node scripts/apply-migrations.mjs --env production --dry-run
   

   node scripts/apply-migrations.mjs --env production
   

   On a fresh project that applies every file from 0001 up (2026-09-21: all 63 ran clean on the new production project); otherwise only what is pending. Then confirm the public views are readable and not writable by the anonymous role, and that anon is refused on every other table and view — Supabase’s default privileges granted anon everything on every new object, which 0081_anon_view_grants.sql revokes. Likewise anon may execute only the five functions the public site calls, and is refused every other RPC (0082_anon_function_execute.sql). The check lists the Data API’s tables, views and functions with SUPABASE_SERVICE_ROLE_KEY, so a new one is covered without being added to the script:

   node scripts/check-public-views.mjs --env production
   

   Afterwards, load /, /adopt and /our-work signed out and confirm residents and stories actually appear, then set the hero photo, contact details (email, phone, LINE, map link, visiting hours) and the wording of the story, how-to-adopt, Foster, Volunteer and Donate pages at /admin/website — the pages ship with placeholder copy (bank details on Donate are literally “(bank name)”) that must be replaced before the site is announced. The same page picks the optional “Pet of the week” from the publicly listed residents; project stories are published from /projects/[id] (“Show on website”). Open Graph previews take the page’s own host for the image URL, so nothing needs configuring — set NEXT_PUBLIC_SITE_URL only if the site ever sits behind a proxy that rewrites the host. (scripts/deploy.mjs sets it to the environment’s own origin for every Worker build and refuses a leftover env file naming another environment; write-env.mjs is Pi-only.)

6. Auth on a new Supabase project. Logins are admin-provisioned, so a fresh database has nobody who can sign in; seed the first admin, then sign in with Google using that address (it links automatically) and create everyone else at /admin/security:

   node scripts/bootstrap-admin.mjs --env production [email protected]
   

   In the project’s Authentication settings (all done for production on 2026-09-21 via the Management API): URL Configuration → Site URL is the production origin and Redirect URLs has https:///auth/callback for every hostname; Providers → Google uses the same OAuth web client as dev, which means the client in Google Cloud Console must list the new project’s https://.supabase.co/auth/v1/callback as an authorised redirect URI. SMTP Settings → Resend, field by field in docs/email-sending.md §2 — Supabase’s built-in mailer is for development only and is rate-limited to a handful of messages an hour, so Forgot password? only works in production once this is done. The reset email template can carry the shelter’s name and logo.

7. Every deploy is a release. Release notes live in src/lib/releases.ts and show at /releases for every signed-in role. A PR that changes something users notice adds a line to unreleased (the test plan asks, and check-test-plan.mjs flags a UI change that has no line and no stated reason); before deploying, a small release PR moves those lines into a new numbered entry and sets package.json’s version to match (the rules are at the top of that file). deploy.mjs refuses a UAT or production deploy that has unreleased notes or a version mismatch, tags the Worker version with the release (dashboard → Workers → Deployments), and after deploying emails a major release that is new to the site to that environment’s admins, with [UAT] / [Production] in the subject; --no-mail skips the email. Test never sends. The one-off Cloudflare setup the email needs — verifying each admin’s address under Email Routing, free — is docs/email-sending.md §1.

Backups

Free-tier Supabase projects have no automatic backups or point-in-time recovery, so production’s safety net is scripts/backup.mjs: a pg_dump (custom format) of the public, private and auth schemas — every record, the views and functions the app keeps in private, and the accounts that can sign in — encrypted with age before it goes anywhere, uploaded to a Backups/ folder under the Drive root as lannacare--.dump.age, keeping the newest twelve per environment (older ones go to the Drive trash, which empties itself after 30 days).

Encryption is not optional and uses a public key (security assessment DB-1, 2026-09-30). The machine that runs the backup holds only the administrator’s public key (BACKUP_AGE_RECIPIENT=age1… in .env.deploy.production, or --age-recipient), so it can write a backup that nobody on that machine can read. The private key is held by the administrator in two places only: an encrypted USB drive and their Apple Keychain. backup.mjs refuses to run without a recipient, refuses a private key in that slot, never writes a plaintext dump to disk (the dump is encrypted in memory), and leaves out the session tables (auth.refresh_tokens, sessions, mfa_*, one_time_tokens, flow_state). It also no longer prints the Drive link, since the output is kept in backup.log. Because mfa_* holds the authenticator secrets, after a restore everyone re-enrols their authenticator app (the recovery in “2-step verification” above).

Making the key pair — once, by the administrator, on their own machine (not the Pi, not in this repo; the private key is never pasted into chat, an env file or a ticket):

age-keygen -o lannacare-backup-key.txt

(winget install FiloSottile.age on Windows, sudo apt-get install age on Debian.) It prints Public key: age1…. Save the whole file in the password manager (the AGE-SECRET-KEY-1… line is the private key), then delete the file. Put only the age1… public key in BACKUP_AGE_RECIPIENT on each machine that takes backups. Several keys may be listed, comma-separated (a second administrator, or a sealed spare); any one of them can decrypt. Where the dumps go in Drive: by default Backups/ under GOOGLE_DRIVE_ROOT_FOLDER_ID; set BACKUP_DRIVE_FOLDER_ID to a folder id and backup.mjs uploads straight into that folder and the status tile reads it, so Backups/ can sit outside the photo tree, shared with one person (decisions/2026-10-02-backup-encryption-rollout.md). The upload prefers its own Google token (BACKUP_GOOGLE_OAUTH_CLIENT_ID, _CLIENT_SECRET, _REFRESH_TOKEN); without it backup.mjs stops before the dump unless --shared-drive-token is given, which uses the app’s token with a warning. The Pi’s cron line carries that flag (set 2026-10-13) (docs/pi-hosting.md, “Backups on this Pi”). A lost private key means every encrypted backup is unreadable, so keep a second copy of it somewhere as safe as the first. To change the key later, install the new public key and keep the old private key for as long as any backup made with it is kept (fourteen days under the daily job).

node scripts/backup.mjs --env production

node scripts/check-backup-encryption.mjs is the offline check of all this (round trip with a throwaway key, exclusions, no plaintext, no link); it needs no database or key of the shelter’s.

--keep-days N removes dumps older than N days (the daily job uses 14), but never the newest five whatever their age, and nothing at all unless that run’s upload was confirmed; --keep N instead keeps the newest N (default 12). Give one, not both. node scripts/check-backup-prune.mjs is the offline check of those rules. --local writes the encrypted dump there instead of uploading (for a restore rehearsal); --local-copy uploads and keeps a copy there, removing local copies by the same retention after the upload is confirmed and printing each one it removes. The folder must be outside the repo; it is created 0700 and each dump 0600. It prints the environment and project ref first, like every other script, and exits non-zero if any step fails.

Where it runs: the Raspberry Pi. Since 2026-09-30 the backup is the Pi’s cron job (daily at 03:00 Thailand time since 2026-10-12; it was Sundays before), the same box that hosts the site (docs/pi-hosting.md):

0 3 * * * cd $HOME/Animal_Shelter_App && node scripts/backup.mjs --env production --keep-days 14 --local-copy $HOME/backups/lannacare >> $HOME/backups/backup.log 2>&1

Drive stays the off-site copy: the Pi is in the same house as the origin it protects, so its own copies guard against a bad Drive upload or a revoked token, not against the house. The dumps live in ~/backups/lannacare, readable only by the Pi’s user and nowhere under the repo, and encrypted, so a copy of that folder is useless without the administrator’s private key. ~/backups/backup.log is the run history; ls -l ~/backups/lannacare shows the newest dump and its date. The steps below describe the setup on any machine; the Pi’s own are in docs/pi-hosting.md.

One-time setup on the machine that runs it:

1. PostgreSQL 17 command-line tools — the projects run Postgres 17 and pg_dump must not be older than the server. No local server is needed. On the Pi (Debian 13): sudo apt-get install postgresql-client-17; trixie’s own package is 17, so no extra repo is needed (Debian 12’s is 15 and would refuse a 17 server). The dev machine has the “binaries” zip from (the winget installer kept failing with a 403 from EDB’s CDN) unpacked so that pg_dump.exe sits at %LOCALAPPDATA%\Programs\PostgreSQL\17\bin\pg_dump.exe — the zip’s bin and lib folders are enough to back up; the restore rehearsal (below) also runs a throwaway server, which needs the zip’s share folder beside them (added 2026-10-13, when its absence stopped initdb with “postgres.bki does not exist”). The script looks there and under C:\Program Files\PostgreSQL\ (where the installer would put it) itself; nothing needs adding to PATH (set PG_DUMP to point it elsewhere).

2. The database password — SUPABASE_DB_PASSWORD in .env.deploy.production (and in .env.local for the dev project). It is the password chosen when the Supabase project was created; Project Settings → Database resets it if it was never written down. The dump goes over Supabase’s session pooler on port 5432 (the direct db..supabase.co host is IPv6-only), whose host and user the script reads from the Management API with SUPABASE_ACCESS_TOKEN.

3. The administrator’s public key — BACKUP_AGE_RECIPIENT in .env.deploy.production (see above). No age program is needed on the backup machine: the script encrypts itself (the age-encryption package).

4. The daily schedule — on the Pi, the cron line above, and nowhere else. The Windows laptop also ran one until 2026-10-04, when the Pi’s first scheduled Sunday run was confirmed (both machines backed up that morning, which is why Drive holds two files for it) and the laptop’s task was unregistered. scripts/backup-schedule.ps1 stays in the repo for registering it again on a machine that needs it, with -Remove to unregister and Get-ScheduledTaskInfo -TaskName "Lanna Care production backup" to show the last run and its result (0 is success).

   A scheduled run can still be missed — the Pi can be off, as it was for an hour on 2026-10-02. Settings → System status makes that visible: its Daily backup tile reads the newest lannacare-production-*.dump.age in Backups/ and turns amber after 2 days, red after 3. The Drive file’s own creation time is the record of the run, so nothing else needs storing.

System status (/admin/status, admins only) runs every health check server-side with a timeout and caches the results for a minute; the checks are small functions in src/lib/status/. Its visitor count needs two optional values in the environment’s .env.deploy.production: CLOUDFLARE_ANALYTICS_TOKEN (a Cloudflare API token with Zone → Analytics → Read on lannacare.org) and CLOUDFLARE_ZONE_ID. Put them in both copies of that file — the dev machine’s, which deploy.mjs --secrets pushes to the Worker, and the Pi’s, which deploy-pi.sh writes into the Pi’s own settings (docs/pi-hosting.md, Day to day). The Pi serves the pages, so with the values only on the Worker the Pi has nothing to read. When a value is missing the tile names it and the machine it is missing from (the Pi, the Worker or this laptop) and the file to put it in; on production that is red, since the count is promised there, and on a dev laptop grey. One of the pair without the other is red anywhere. The query first ran against the live API on 2026-10-10 and answers at 7, 30 and 90 days (docs/decisions/2026-10-10-visitor-count-live.md).

Status alerts mail the admins when a health tile goes red and when it recovers. A Worker cron trigger (*/15 * * * * on the test and production Workers, wrangler.jsonc) hands POST /api/status/alerts straight to the Next handler inside the Worker, bearer-authenticated with the service-role key; the run is src/lib/status/alerts.ts and remembers what it saw in status_alert_checks / status_alert_runs (0098). Mail goes out through the STATUS_ALERT_MAIL send_email binding from STATUS_ALERT_FROM, so, like release mail, only to addresses verified under Email Routing → Destination addresses. STATUS_ALERT_SITE switches it on per environment. To prove the path on a dev server, put STATUS_ALERT_SIMULATE_FAIL=drive in .env.development.local (dev database only) and press Run the alert check now twice; the mail is printed to the server log.

Rehearsing a restore — every quarter (first done 2026-10-13; next due January 2027). This proves a backup unlocks, loads, and holds every table, row, view, function, access policy and trigger, using a throwaway database on the administrator’s laptop. Nothing leaves the laptop and nothing touches the live site. It takes about ten minutes:

1. Make a folder outside the repo and outside OneDrive (OneDrive would upload the key): C:\Temp\lannacare-restore.

2. Put the newest backup in it: from Drive’s Backups folder, download the newest lannacare-production-.dump.age (or ask Claude to copy one from the Pi’s ~/backups/lannacare).

3. Get the private key. It is kept in two places: the encrypted USB drive and the Apple Keychain.

   • From the USB drive (better): plug it in and unlock it. If the key is there as a file, use that file’s path in step 4 directly. Nothing is copied, so there is nothing to delete afterwards.
   • From the Keychain: copy the one line that starts AGE-SECRET-KEY-1, open Notepad, paste it, then File → Save as into the folder from step 1, set Save as type to All files, and name it key.txt. (With the type left on Text documents, Notepad saves key.txt.txt, which Windows shows as key.txt; the script spots this and says so.)

4. In PowerShell, in the main checkout (C:\Development\Animal_Shelter_App), with the key’s path after --identity:

   node scripts/restore-rehearsal.mjs C:\Temp\lannacare-restore\lannacare-production-.dump.age --identity C:\Temp\lannacare-restore\key.txt
   

5. Read the last lines. REHEARSAL PASSED is the only good result; anything else names what did not come back. Each check prints ok or FAIL above it.

6. If the key came from the Keychain, delete key.txt (it is the master key, unencrypted). Delete the folder, then empty the Recycle Bin. If it came from the USB drive, just lock and unplug the drive. The script has already deleted its database; it never writes an unlocked copy of the backup to disk.

The script (scripts/restore-rehearsal.mjs) does not trust pg_restore’s exit status: every restore of these dumps reports two harmless errors (schema "public" already exists, and auth.flow_state, a session table the backup leaves out), so it exits 1 even on a good restore, and a dump missing whole objects can still exit with the same status. The script instead checks that those two are the only errors and compares every count against the archive itself. A backup made before 2026-10-13 lacks the private schema and fails this rehearsal (see below); that is correct, not a fault in the script.

What the rehearsal does not prove is that a Supabase project comes back. Ownership, grants, Supabase’s own auth set-up and signing in only exist on Supabase, and the roles the rehearsal creates are empty stand-ins. That half is still unproved and is its own backlog item (docs/decisions/2026-10-13-restore-rehearsal-local.md).

Restoring into a Supabase project (production after a disaster, or a scratch project) — never yet run against a real Supabase project. The steps below were corrected on 2026-10-13 by running them locally against a stand-in for a fresh project; each correction is noted. Run the rehearsal above on the chosen backup first, so you know the file itself is good. Then, on the administrator’s own machine, with the private key saved to a file out of the repo:

node scripts/decrypt-backup.mjs lannacare-production-.dump.age --identity path/to/key.txt
# (or, with the age program:  age -d -i path/to/key.txt -o x.dump x.dump.age)

Then, in this order, with DB set to the project’s session-pooler connection string (postgresql://postgres.:@aws-0-ap-south-1.pooler.supabase.com:5432/postgres):

1. The accounts first, rows only (the auth tables already exist in every Supabase project). Run it with PGOPTIONS="-c session_replication_role=replica" set, so a table can load before the one it points to (identities comes before users in the dump and is otherwise rejected): pg_restore --dbname="$DB" --schema=auth --data-only --no-owner --no-privileges .dump Corrected: the old recipe ran this step last, so the twenty links from app tables to auth.users were created against an empty table and failed, quietly: the rows loaded, and the database was left without those checks. Whether Supabase’s postgres user may set session_replication_role is not yet proved.
2. Create the private schema: create schema if not exists private; in the SQL editor. Corrected: pg_restore --schema=… restores the objects in a schema but never the schema itself.
3. The app’s data and structure: pg_restore --dbname="$DB" --schema=public --schema=private --clean --if-exists --no-owner --no-privileges .dump Corrected: the old recipe left out private, which holds no rows but is what about fifteen public views and their access policies are built on, including resident_current_state.
4. node scripts/apply-migrations.mjs --status --env … to confirm schema_migrations came across, and to apply anything newer than the backup.
5. Delete the decrypted .dump: it is plaintext.

The dump has no session tables, so nobody is signed in afterwards, and authenticator apps are re-enrolled (see above).

Backups made before 2026-10-13 have no private schema (backup.mjs dumped only public and auth until then). Their data is complete, since private has never held rows, but restored alone they leave those views missing. With only such a backup, restore it as above and then recreate private’s views and functions from the newest migration defining each (0082 and 0086 moved them there; grep -l "private\." supabase/migrations/*.sql lists the rest) before opening the app. That path has not been tried; with any later backup it does not arise. The Pi’s nightly backups include private only from the release after 2026-10-13, since the Pi runs the released code.

Project structure

• supabase/migrations/ — database schema, RLS policies, and the core business-logic functions (immunization recording, bulk appointments, deceased-workflow cascade) described in the requirements doc.
• src/lib/supabase/ — Supabase client helpers (browser + server).
• src/lib/google/ — Google Drive client helper, including the per-record folder helpers (resident photos / blood tests / procedures, and the maintenance job folder that follows a job between status folders, and the project folder path that mirrors the project tree).
• src/lib/clinics/ — clinic scope (which clinic a doctor account belongs to) and each clinic’s doctor list.
• src/lib/contacts/ — the contacts vocabulary (contact_type), the one-tap link builders (tel:, LINE, Messenger, WhatsApp, mailto:, maps) used by the /contacts list and hub, and the Carer-only loader behind the resident hub’s foster / adopt picker.
• src/lib/shelter-friends/ — Shelter Friends (0076), the businesses thanked on the public /friends page: friends.ts holds the one gate on which contact types may become a Friend, the row types and the staff preview; public.ts is the public slice, which reads only the public_shelter_friends view (published, live contacts, opted-in details only); staff-drafts.ts is the one exception, a count of unpublished drafts shown only to signed-in staff on /friends. src/components/FriendCard.tsx is the card both /friends and the contact hub’s preview draw. Add a Shelter Friend (/management/shelter-friends/new) is the staff wizard that adds one end to end: it reuses intake’s WizardChrome, and saves through addShelterFriend (contact if new, then the profile row, then the logo via uploadFriendLogo); see docs/decisions/2026-10-02-shelter-friend-wizard.md.
• src/lib/links/ — checks for links staff paste into forms that end up on a public page (https only; Facebook links on facebook.com / fb.com).
• src/lib/maintenance/ — the enclosure maintenance feature’s shared pieces: status vocabulary and colours, the job loader, and the after-change Drive folder sync used by the server actions.
• src/lib/my-tasks/ — /my, “My tasks”: the common MyTask shape every source returns (types.ts) and one loader per source — maintenance so far — plus the count behind the menu badge. A new source is a loader beside maintenance.ts; see docs/decisions.md, 2026-09-26.
• src/lib/projects/ — the project folder browser’s shared pieces: the category list, folder/photo loaders, and the Drive sync that keeps Projects///… matching the tree after renames and moves; public.ts is the read-only slice behind the public /our-work pages (the public_projects views and the locale/English-fallback helpers).
• src/lib/site/ — the public site’s editable content: the site_content singleton (contact details, paired Thai labels), the site_pages rows behind the story and the Foster / Volunteer / Donate / how-to-adopt pages, and the three-rule body format they are written in. /privacy is the exception: its text is in the dictionaries, because Google’s sign-in consent screen links to it and it must never be blank.
• src/lib/tags/ — the addresses programmed into enclosure QR codes and resident RFID cards (/e/, /r/, short redirects — see docs/decisions.md, 2026-09-22) and the origin they are prefixed with; src/components/CopyTagLink.tsx is the copy control on the hubs and lists, and src/app/e/ / src/app/r/ the redirects.
• src/lib/residents/ — the public slice of a resident (public_resident_profiles, similar residents) and the adoption recommendation fields the intake and edit forms share.
• src/lib/translations/ — free text across languages (0056): the row types, the loader for a record’s translations rows, the manager queue query, and localizedField() / localizedFromRow() that pick the approved other-language text by locale with the original as the fallback. src/components/TranslationPanel.tsx is the one editor, used by /management/translations and under the fields themselves; the server actions are in src/app/management/translations/actions.ts. Which fields are translatable is the translatable_fields table, not code (today: resident bio / temperament / past story, project story, photo caption, maintenance job title / description). Short labels (diet, medicine and setup-list names, units, places, website captions) are the other half (0166): a paired _th column, registered in translatable_labels, read on the page with label_translations() and written with set_label_th(). labels.ts has the page’s types, groups and links, and localLabel(), which every screen uses to show a label in the reader’s language.
• src/lib/archive/ — the deceased resident archive: the summary PDF, the offline index.html index page written beside it in the resident’s Drive folder, the step that moves that folder to Residents/Deceased/, and its reverse for a death withdrawn as recorded in error.
• src/lib/manual/ — the in-app user manual’s content (en.ts, English only so far), rendered by src/app/manual/page.tsx at /manual for every signed-in role, filtered to the reader’s role by each topic’s roles (filter.ts; ?view=all shows the rest, greyed). Its screenshots live in public/manual/ and are regenerated, not edited: node scripts/manual-screenshots.mjs opens the machine’s Edge/Chrome on the running dev server, waits for you to sign in as an admin, and captures every screen the manual references. It then writes each PNG’s pixel size to src/lib/manual/screenshot-sizes.json so the page can reserve the space; --sizes rewrites just that file after a PNG is added or replaced by hand.
• src/lib/assistant/ — the assistant’s brain: one small parser per intent under intents/, tried in the order parse.ts lists (the order is load-bearing — see docs/decisions.md, 2026-09-23). Each one turns a typed sentence into a Draft with nulls for whatever the sentence didn’t say, matching resident, enclosure and vet names against real rows and a small date/time vocabulary in English and Thai. data.ts loads those rows and the caller’s role; audit.ts writes the assistant_actions row (0070) every turn leaves behind, confirmed, cancelled or unrecognised. Version 2 (an LLM — see the “Assistant” section of docs/backlog.md) replaces this directory and keeps the rest.
• src/components/assistant/ — the conversation: the message list and input, one preview card per writing intent, and the answers to the read-only questions. Rendered both by /assistant and by the header’s slide-over. Nothing is written until a card is confirmed, and Confirm calls src/app/assistant/actions.ts, which runs the same server helper the matching page or form runs — the assistant has no write path of its own. Admin, management and staff can confirm writes; volunteers get the lookups (“where is …”, “who is in …”, “what is due this week”).
• worker/index.mjs — the Worker entry: edge cache → Pi through its tunnel → OpenNext render (docs/pi-hosting.md).
• worker/translations.mjs, worker/translate-engines.mjs — machine-drafted translations: on the Worker’s 15-minute cron, pending translations rows get a draft from Workers AI (or M2M100 / Ollama, via TRANSLATE_ENGINE) for a manager to check; never approved. Needs the AI binding and SUPABASE_SERVICE_ROLE_KEY; node scripts/check-translate-worker.mjs tests it. docs/decisions/2026-10-04-translations-worker.md.
• scripts/pi/ — the Pi origin: one-time setup.sh, deploy-pi.sh, the systemd unit and cloudflared config.
• scripts/ — one-off tooling: apply-migrations.mjs (migration runner), load-residents.mjs (bulk resident loads from a reviewed CSV — dry run by default, --apply to write; see docs/decisions/2026-10-05-bulk-resident-loader.md; its run also lists possible duplicates of existing residents), correct-resident.mjs (removes a resident entered twice, keeping the original, optionally copying details such as the Thai name across; refuses anything with real history; dry run by default, --apply to write, receipt on the Desktop first; see docs/decisions/2026-10-09-resident-corrections-script.md), upload-blood-work.mjs (the /upload-blood-work skill’s writer: loads lab reports from the Blood Work inbox on Drive as a clinic visit plus one blood test per panel with the report attached; dry run by default, --apply to write, test by default and --env production when the skill’s lessons file says live; see docs/decisions/2026-10-15-upload-blood-work-skill.md), audit-contact-map-links.mjs (lists contacts and Shelter Friends whose map link is dead, missing or has words after it, in plain words for whoever fixes them — read-only, dev by default, --env production when asked; see docs/decisions/2026-10-08-contact-map-link-audit.md), move-contact-map-links.mjs (moves a map link left at the front of a contact’s address into its Map link, map_url — dry run by default, --apply to write, --env production when asked; see docs/decisions/2026-10-08-contacts-address-and-map-link.md), check-public-views.mjs (go-live check), manual-screenshots.mjs (user-manual screenshots), appsheet-export.mjs + import-appsheet.mjs (the legacy data migration — see docs/data-migration.md), the Google OAuth setup helpers, the deploy-time env stripper, and extract-yoga-wasm.mjs (refreshes src/lib/archive/yoga/yoga.wasm after a yoga-layout upgrade — see docs/decisions.md, 2026-09-22).
• docs/ — requirements, decisions log, backlog and the data-migration mapping/runbook.
• CLAUDE.md — the working rules for this repo (branching, migrations, finishing a feature). Written for Claude Code sessions, but they’re the rules for anyone committing here.

Day-to-day workflow

The short version of CLAUDE.md. Once a day, in the main checkout, main is pulled and the backlog branch folded in; /plan-day does this, reads the backlog and proposes two or three workstreams that touch different parts of the app. Each becomes a worktree (scripts/worktree.mjs new) with its own branch, port and Claude session, and every commit auto-pushes (.githooks/post-commit). A feature is finished by merging main in, passing node scripts/gates.mjs (CI runs the same three on the PR), merging the PR, and scripts/worktree.mjs done — merges are serial, and the other streams sync after each one. Schema changes go first as their own small PR, applied to dev with node scripts/apply-migrations.mjs at merge time, so only one branch ever carries a migration.