Production-ready v1: migrate SIB to Directus on Fly.io

[dvhthomas]

Scope

Migrate the SIB site's data backend from Google Sheets to Directus Cloud General tier with a Cloudflare Workers logic tier for transformations. Astro static site stays on Cloudflare Pages. v1 stays scoped to the current map-of-SIBs product; Stories rendering and distribution analytics deferred to a v1.5 milestone; multi-city expansion as the explicit v2 next step.

Branch: worktree-atomic-weaving-treehouse
Implementation plan: docs/plans/2026-04-30-001-feat-sib-production-ready-v1-plan.md
Requirements doc: docs/brainstorms/sib-production-ready-v1-requirements.md
Ideation doc: docs/ideation/2026-04-30-sib-stack-migration-ideation.md

Architecture (post-review pivot)

Editors  →  Directus Cloud General tier ($30/mo) — admin + schema + Map field with pin-confirm
Workers  →  workers/*.ts (TypeScript in repo) — geocode-on-save, deploy-hook debounce
              all transformation logic version-controlled, type-checked, unit-tested, PR-reviewed
Build    →  Cloudflare Pages — Astro reads from Directus Cloud at build time
              minimum-records gate prevents silent empty-build replacing prior good deploy
Public   →  CF Pages CDN — static HTML; no runtime hops to Directus or Workers
Backups  →  Directus Cloud handles its own; quarterly off-vendor age-encrypted export to R2

Implementation: 10 units across 6 phases

Phase 1 — Foundation

• U1: Pre-push git hooks (verify + Vitest + conditional Playwright when UI paths touched — compensates for branch protection being unavailable on private-repo Free tier)
• U2: Add Zod schemas (Location, LocationType, Story) + boundary validation
• U3: DataSource interface + adapters (SheetSource, FixtureSource, DirectusSource stub)

Phase 2 — Provisioning

• U4: Directus Cloud (production + staging projects), schema, roles, MFA, rate limiting

Phase 3 — Workers + Integration

• U5: Cloudflare Workers project (Wrangler + GHA deploy) + geocode handler + deploy-hook debouncer
• U6: DirectusSource adapter + minimum-records gate + per-PR staging-build CI (advisory)

Phase 4 — Editor enablement

• U7: Build-status indicator (last_deploy_triggered_at) + revisions config + one-page editor cookbook

Phase 5 — Pre-launch + cutover

• U8: Pre-launch gates — R21 off-vendor export drill + R22 editor-validation
• U9: Cutover — snapshot Sheet to CSV, manual entry of 3 real businesses to Directus Cloud, switch CF Pages env, DNS, disable Apps Script triggers FIRST then rename deploy hook

Phase 6 — Post-cutover

• U10: Astro 5.18 → 6.x upgrade (sequenced after cutover so production migrates against the working version first)

Cost envelope

~$30–35/mo (Directus Cloud General tier $30 + R2 ~$0.30 + MapTiler tile usage unchanged). Well under $100/mo budget.

Pre-launch gates (cutover-blocking)

• R21: off-vendor export drill — both age-keyholders verify the quarterly Directus Cloud export decrypts and re-imports cleanly to a test project
• R22: editor-validation — both editors complete F1 (add Location) and F2 (edit + revert via revisions) on staging Directus without engineer help in <15 min each

Verify before launch

• Directus MSCL Innovation Grant tier eligibility — confirm SIB's hosting org is <$5M revenue AND <50 employees (Directus changed this gate in v12 with the 50-employee cap)
• MapTiler classification — Free tier is non-commercial only; confirm SIB is non-commercial or budget MapTiler Flex (~$25/mo)
• MapTiler key restrictions — restrict to Worker egress so a leaked key can't be replayed from arbitrary origins

Key architectural decisions

• Directus Cloud, not self-hosted on Fly. Removes 4–6 implementation units of Postgres/Fly/backup ops. Tradeoff: data lives in Directus's infra, mitigated by quarterly off-vendor export to R2.
• Cloudflare Workers as the logic tier. Geocoding lives in workers/geocode.ts, version-controlled and tested. Avoids the "Apps Script reborn" anti-pattern of logic-in-a-CMS-admin-UI.
• Geocoding stays via Worker; pin-confirm via Directus native Map field. Editor types address → Worker geocodes via MapTiler → editor drags pin if needed. last_geocoded_address field breaks recursion.
• Astro 5→6 upgrade is the LAST unit (U10), post-cutover. Production migrates against working Astro 5.18 first; framework upgrade is isolated from migration risk.
• Manual entry of 3 real businesses + checked-in CSV snapshot of Sheet at cutover. No migration script (would be days of build/test for a 30-min data-entry task).
• Pre-push hook gates the full suite when UI paths are touched. Branch protection unavailable on this private-repo Free tier (verified: API returns 403). Pre-push runs npm run verify + Vitest unconditionally; Playwright when changed paths match src/components/, src/pages/, tests/, or CSS files. CI runs in GHA but is advisory.
• Status enum lowercase (approved, soon, hidden, pending) per origin R4.
• Big-bang cutover with non-rollback acknowledged. Sequence pins ordering: disable Apps Script triggers FIRST → wait → rename deploy hook. Sheet to read-only post-cutover; CSV snapshot in repo for permanent record.

Deferred

• v1.5 Stories launch — public rendering on per-location pages, gated on editorial pipeline + employee-consent workflow
• v1.5 distribution — Rebrandly QR short links, UTM grammar, Umami self-hosted, Rebrandly QR endpoint pre-launch spike
• v1.5 polish — suggest-an-edit form re-enable, two-stage publish gate, full ops manuals
• v2 next step — multi-city expansion (city discriminator, per-city routing, per-city editor permissions)
• Association-future — Organization-as-distinct-entity, Member entity, Event handling, Resource library, member-facing auth (deliberately deferred but not locked out by v1 schema choices)

Review history

• 7-persona ce-doc-review pass on the brainstorm requirements doc surfaced 16 actionable findings; all walked through interactively and applied
• 6-persona ce-doc-review pass on the implementation plan surfaced 27 actionable findings; 10 critical+high walked through interactively, 16 mediums applied via plan rewrite, 12 lows appended to plan's "Deferred to Open Questions" section
• One critical premise correction (private-repo branch-protection unavailability) caught and applied during the second review

Next step

Pick up U1 in /ce-work (or whichever unit is next in dependency order) and ship as independent PRs.

🤖 Generated with Claude Code