Supabase platform

Sanmopia modernization uses self-hosted Supabase first.

The development stage runs a self-hosted Supabase core stack in Compose: db, auth, rest, realtime, storage, imgproxy, and kong. Kong is the single Supabase API gateway for /auth/v1, /rest/v1, /realtime/v1, and /storage/v1; application services talk to it on the internal Docker network. Traefik/Mutagen expose only the configured public host for browser use. This follows the official self-hosted Docker model while preserving the Sanmopia rule that application services do not publish host ports.

The db service binds POSTGRES_USER from SANMOPIA_POSTGRES_ADMIN_USER, which is constrained to the official image bootstrap owner supabase_admin. The supabase/postgres initialization script depends on that role before it creates/demotes the postgres role and applies bundled migrations. A stage boot that uses another bootstrap owner creates a broken local volume and must be reset by removing only the Supabase stage data/config/storage volumes. Mounted Supabase bootstrap files are committed with runtime-readable modes: SQL/YAML files are 0644, and kong-entrypoint.sh is executable. This matters because official Supabase/Kong images do not run every process as root. The stage PostgREST schema list is public,storage. Do not include graphql_public until the stage also provisions the matching GraphQL schema and policies; otherwise PostgREST cannot load its schema cache.

Defaults

Need	Default
Relational data	Supabase Postgres
Authentication	Supabase Auth
File access	Supabase Storage with RLS
Realtime changes	Supabase Realtime
Background jobs	Supabase Queues / `pgmq`
Scheduling	Supabase Cron / `pg_cron`
Vector search	Supabase Vector / `pgvector`
Service functions	Supabase Edge Functions

Raw Postgres, Redis, RabbitMQ, S3, custom auth, or standalone cron need explicit evidence before adoption.

Tables need explicit grants, RLS, policies, and default-privilege revokes before Data API or GraphQL access is considered available. Sanmopia user data is authenticated-only by default; anon access must appear in contract before any SQL grant. This matches the 2026 Supabase default-grant change: tables in public cannot be treated as Data API-visible unless migration SQL grants the role on purpose. Stage migrations use pnpm supabase:migrate from assembly-repo. The command delegates to the backend’s pinned Supabase CLI and runs supabase db push against the self-hosted stage database. Official supabase_migrations.schema_migrations history is the only migration ledger; do not add a parallel application-managed ledger or a custom psql migration loop. When an existing stage predates official history, the runner performs a one-time supabase migration repair baseline before pushing the newest migration. Booking, payment, document, and privacy smoke tests must target this self-hosted stack, not a parallel local Postgres.

A successful stage db push proves only that the current shared stage accepted the pending migrations. It is not clean-database replay evidence. Schema/RPC claims that require replay proof must use a disposable, test-owned local Supabase stack, apply the repository history from empty twice, and run database lint plus the scoped pgTAP suite after each replay. Never reset, repair, start, or stop a shared stage or an ownership-unknown Postgres instance to manufacture that evidence.

Runtime Ownership

Supabase is the first-choice runtime for durable ledgers, read models, Auth, RLS, Storage, Realtime, Cron, Queues, and generated database contracts. Backend domain/application code owns decisions; Supabase stores the accepted facts and filtered projections.

Restate owns workflows that cross multiple domain commands or external providers: reservation booking, payment/refund closeout, financial lifecycle, settlement export/repair, document rendering, notification fanout, and member privacy lifecycle. Workflow status is projected back into Supabase so portals read stable progress rows and never call Restate internals.

SpiceDB owns relationship authorization for actors, branches, reservations, care teams, and settlement scopes. Supabase Auth identifies the actor; Supabase metadata and profile roles are non-authoritative hints only. Backend APIs must combine the authenticated user id with SpiceDB relationship checks and policy decisions before writing business facts. Supabase RLS remains a defense layer for Data API reads and private Storage access, not the application authorization model.

Mother booking uses sanmopia_mother_booking_operational_contexts as a versioned Supabase read model for the authenticated booking screen. It is not the canonical pricing or caregiver source; policy jobs/materializers build rows from price catalog, regional benefit, branch area, caregiver grade, and payment policy sources. Browser and backend command flows consume the frozen row so later pricing or grade changes do not mutate what the mother already saw.

Payment product plans are also Supabase-first. The seeded sanmopia_payment_product_plan_catalog_items table is a versioned catalog of sellable or operator-billable payment products. Backend adapters read the latest active/effective catalog, validate rows with Pydantic, and derive domain PaymentProductPlan objects for both Kill Bill pre-registration and GET /payment-product-plans. This removes payment-purpose and source payment-type id branching from UI code while preserving future catalog revisions with effective dates. Catalog rows also carry JSONB settlement line templates and communication routes, so voucher/gift fee lines and Novu/SMS/workflow routing are selected by backend product policy instead of browser or PHP string branches. Payment-product communication route keys must exist in the generated communication workflow catalog and Supabase seed, so a catalog revision cannot point at an unknown provider workflow.

Payment and refund provider abstraction is Kill Bill. Target domain, application, contract, and docs vocabulary should describe Sanmopia payment instructions, refund intents, gateway commands, provider events, ledgers, and workflow status. PortOne-named domain/API abstractions are not part of the target model. Source or adapter evidence can name historical providers only to explain migration provenance.

Kill Bill operations run behind backend ports for pre-registration, paid-state verification, cancel/refund, webhook handling, virtual-account closeout, and admin lookup/repair. Supabase stores product-plan rows, payment records, refund ledger entries, workflow request rows, webhook/admin audit facts, and settlement-continuation state. Restate sequences closeout and repair work; SpiceDB gates branch/HQ operator repair actions.

Reservation collaboration uses Supabase Realtime as a workspace coordination channel, not as the write authority. Backend commits first compare expectedRevision, atomically bump sanmopia_reservation_bookings.revision_number, append sanmopia_reservation_change_journal_entries, store a realtime projection event, and then publish a private Broadcast event named reservation_projection_invalidated. Operator portals subscribe to the returned reservation topic and track Presence on the same private channel. Presence is limited to slow-changing editor state such as actor, workspace id, and active field group; high-frequency cursor or typing state must use Broadcast or remain local. Workspace open and browser-level events are persisted through sanmopia_reservation_workspace_sessions, sanmopia_reservation_workspace_events, and sanmopia_record_reservation_workspace_event; this covers dirty drafts, history/back navigation blocks, recovery, discard, and conflict support cases without turning them into domain fact changes. Postgres Changes can be used for backend/admin observability, but the operator UX contract is the domain journal, private Broadcast invalidation, and the workspace event log.

Runtime start order:

Generate config with pnpm mutagen:render.
Pull the immutable project-owned SANMOPIA_ASSEMBLY_IMAGE digest built on the shared FractalOps Kubernetes BuildKit execution plane.
Start Compose services without host ports or local image builds.
Start or resume the Mutagen project so local HTTP forwards to Traefik.
Run pnpm supabase:migrate.
Run pnpm authorization:bootstrap so SpiceDB accepts stage relationship seeds.
Run pnpm workflow:register.
Run pnpm supabase:seed:mother-booking-context for booking smoke data and the stage branch-operator relationship.
Run pnpm smoke:mother-booking-workflow for the frontend stage path, then run backend pnpm run supabase:smoke:stage:reservation-booking to verify Restate start, Supabase workflow ledger, branch-operator progress read, and requested_by_user_id actor preservation.

OSS CMS

Content authoring uses Strapi over Supabase Postgres and Supabase Storage. Strapi reduces handmade CMS CRUD for notices, community articles, campaign pages, customer inquiries, member notifications, and partner benefit lead operations where OSS admin workflow can replace bespoke CRUD. The same OSS admin surface also owns versioned document definitions, promotion rule editing, caregiver daily-care report definitions, caregiver performance seasons, caregiver performance metric rules, and caregiver grade thresholds. It is an admin authoring tool, not the public application API. Public app flows still go through Sanmopia contracts, backend policies, and Supabase RLS.

Printable HTML templates live in Supabase Storage, referenced by sanmopia_document_definitions.storage_bucket and storage_object_key. Backend adapters read templates through the Supabase Storage port, then render HTML/PDF with OSS print tooling such as Jinja and WeasyPrint or browser print. Generated render artifacts are uploaded to the private reservation-documents bucket and tracked in sanmopia_document_render_artifacts by idempotent request_code, so a repeated render updates the same logical artifact instead of minting inconsistent download rows. Downloads stay private by default. Backend download orchestration creates short-lived Supabase Storage signed URLs for the stored object only after the caller passes Sanmopia authorization checks; frontend components receive the signed URL, not Storage service credentials. The download handoff uses the logical render_request_code to find the stored artifact, then checks VIEW_RESERVATION through reservation-stage policy and SpiceDB before signing the object. Bundle downloads are modeled as a catalog-validated member handoff first: backend verifies the requested definition keys belong to the configured service-kind bundle, checks reservation view permission once, then returns short-lived signed URLs for each ready artifact plus missing definition keys. Creating one persisted ZIP artifact remains a later schema slice because the current render artifact table is intentionally constrained to HTML/PDF outputs.

Campaign popup/banner/event placement uses sanmopia_content_publication_placements instead of hardcoded PHP arrays or route switches. Strapi authors slots such as home_popup, home_banner, community_hero, and event_list; Supabase RLS exposes only enabled placements whose parent ContentPublication is published and in window.

Caregiver daily-care report forms use sanmopia_daily_care_report_definitions instead of PHP view-level fields such as incisionSite, feedingCount, or otherService. Strapi authors the section and field definition. Supabase stores submitted answers in sanmopia_daily_care_report_submissions with reservation, caregiver role, handoff order, service date, and policy version so one contract can receive multiple caregiver reports without fixed columns.

OSS communication

Transactional and lifecycle notifications use Novu instead of bespoke FCM, SMS, or Alimtalk sender code. Strapi authors sanmopia_communication_workflow_rules; Novu owns subscriber preferences, in-app inbox, channel routing, and provider integrations. Supabase stores sanmopia_communication_preferences, sanmopia_communication_dispatch_requests, and sanmopia_communication_dispatch_events for RLS, idempotency, realtime status, and audit. SupabaseCommunicationDispatchStore persists dispatch requests by workflow-code/version catalog lookup, upserts by idempotency key, and appends provider dispatch event rows so Novu delivery is never only an in-memory side effect. Backend domain emits stable communication triggers such as gift_coupon_issued, virtual_account_payment_cancelled, and birth_information_requested; source notification names map only at import boundaries. Backend adapters trigger Novu Event API with the Sanmopia dispatch idempotency key as Novu transactionId, so provider retries do not duplicate workflow runs. Reservation birth-information reminders use Supabase Postgres task rows plus Supabase Cron/Queues for scheduling, then hand off to Novu dispatch requests. The source-era direct FCM cron shape is not rebuilt. The backend queue workload contract defines sanmopia_notification_dispatch and its dead-letter queue as the notification work plane; Novu remains provider orchestration, while Supabase stores Sanmopia idempotency, retry, suppression, and audit facts. The default Novu/Strapi workflow catalog is generated from the backend application feature catalog. Supabase seed data should consume that catalog so workflow codes, provider workflow keys, channels, marketing consent, and policy version labels stay one source of truth. Payment workflow rules are part of this catalog rather than payment-controller branches: virtual-account issued, payment completed, coupon fulfilled, and payment-change completed events map to explicit audiences such as mother, payer, coupon receiver, branch operator, and branch secondary-contact. backend-repo/supabase/seed.sql is generated by uv run python scripts/render_supabase_seed.py --write; the colocated backend test fails if the file drifts from the catalog renderer.

Reservation birth information is stored in sanmopia_reservation_birth_profiles with unbounded child records in a JSON array instead of fixed first/second/third slot columns. Reminder state is stored in sanmopia_birth_information_reminder_tasks so mothers, caregivers, branch members, and HQ see one audited lifecycle through Supabase RLS.

Partner benefit lead intake uses Supabase Postgres for versioned program policies, requests, export batches, and export items. Result files use the private mother-lead-results Supabase Storage bucket with owner-folder RLS.

Caregiver performance recognition uses Supabase Postgres for seasons, metric rules, grade rules, scorecards, contribution facts, manual adjustments, and branch scorecards. Strapi authors only policy/config rows; generated scorecards remain backend facts with authenticated read policies for caregivers, branch members, and HQ.