ACTS — Data Model (Phase 2 prototype)

Source of truth: supabase/migrations/ (DDL) + supabase/seed.sql (taxonomy). Types: types/database.types.ts. Validated against Postgres 16 on 2026-08-10.

Shape

Every mission is Person × Event × Place × Act, posted by a Seeker and fulfilled by a Doer/Helper. A guest can do everything without an account.

`` profiles ──< mission_request >── act / event_type / relationship / place │ │ │ └──< proof (each "pray" tap = one row; count = live counter) │ └──> story_card (shareable completion artifact) └──< helper_offer "I am headed to [PLACE]; who wants [ACT] while I'm there?" └── recognition_score / badge_award (server-computed) comment ── polymorphic, attaches to mission/offer/proof/story_card broadcast ── advocate (pastor) QR/link pointing at a mission ``

Tables

Taxonomy (seeded, code-keyed): relationship (31), event_type (63), place_type (48), act (46, with code families ACT-PRY/CND/RSY/…), badge (11).

Formal catalog: place (17 named basilicas/shrines/pilgrimage journeys with lat/lng — St. Peter's, Lourdes, Fátima, Guadalupe, Knock, Assisi, Camino…).

Identity/community: profiles (member via auth_user_id or guest via guest_key), parish, campaign.

Core objects: mission_request, helper_offer, proof, comment, recognition_score, badge_award, story_card, broadcast.

Key modeling decisions

• Proof is participation + artifact. Each tap is one proof row (proof_type tap/trust); richer proof sets image_url/reflection_text on the same row. count(*) per mission drives the "4,218 praying" counter. No separate boolean "completed" flag.
• Place is dual. A mission/offer references a formal place_id or carries inline geo (place_type_code, place_label, city, region, lat/lng, geo_mode) for near-me / ad-hoc. The first web pray is geo_mode='world' (no GPS) by design — in-app browsers block location.
• Enums carry the fixed vocabularies: audience_layer, mission_mode, proof_type, act_image (prayer/candle/church), geo_mode, statuses.

RLS posture (prototype)

Append-only for the public, mutations server-side:

• SELECT → anon + authenticated on all 17 tables (public read).
• INSERT → anon + authenticated on the 7 guest-facing tables (profiles, mission_request, helper_offer, proof, comment, story_card, broadcast).
• UPDATE / DELETE → none → only service_role (moderation, recognition scoring, status transitions) via lib/supabase/service.ts.

Running it

• Hosted: paste migrations/*.sql then seed.sql into the Supabase SQL editor, or supabase link --project-ref <ref> then supabase db push.
• Local (needs Docker): supabase start then supabase db reset (applies migrations + seed automatically).
• Regenerate types (no Docker): supabase gen types typescript --project-id <ref> --schema public > types/database.types.ts.