Worship Service Builder — Multi-Church

Plan worship services (lectionary lookup, scripture-matched hymn suggestions, AI-written PC(USA)-friendly liturgy, Word export, service archive, and "exclude hymns used in the last 12 weeks"). Multiple churches share one deployment; each church's hymnal, archive, contacts, and members are fully isolated. People sign in with Google, then create a church (becoming owner) or join one by invite.

New web app (React + FastAPI) — migration in progress

The app is moving off Streamlit in slices (see docs/superpowers/specs/2026-09-25-react-fastapi-migration-design.md). Until the last slice lands, the Streamlit app (app.py) keeps working on the same database.

Part	Folder	Hosted on
Frontend (Next.js, React)	frontend/	Vercel
API (FastAPI)	backend/	Railway
Sign-in (Google only) + database	—	Supabase

Run locally (two terminals):

cd backend && ../.venv/bin/python -m uvicorn api.main:app --reload --port 8000
cd frontend && npm run dev

Copy backend/.env.example → backend/.env and frontend/.env.example → frontend/.env.local first. The API does not create tables: create or update the local database with (cd backend && DATABASE_URL=sqlite:///../data/app.db ../.venv/bin/alembic upgrade head) the first time and after pulling a new migration (a data/app.db made before slice 1a needs ../.venv/bin/alembic stamp 0001_baseline once first, and one made by init_db() after slice 1a needs ../.venv/bin/alembic stamp head instead of the upgrade, with the same DATABASE_URL; see Database → Schema migrations). Tests: .venv/bin/python -m pytest -q (backend and Streamlit) and cd frontend && npm test.

Deploying:

• Railway (service root backend; Settings → Deploy → Pre-deploy Command alembic upgrade head and Healthcheck Path /health/ready, set by hand in the Railway UI: Railway does not read /backend/railway.toml, which only records them): env DATABASE_URL (Supabase session pooler), SUPABASE_URL, CORS_ORIGINS (exact Vercel URL, no trailing slash, comma-separated for multiple), plus the carried-over OPENAI_API_KEY and GOOGLE_* gmail.send vars.
• Vercel (project root frontend): env NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY (publishable key), NEXT_PUBLIC_API_URL (the Railway URL).
• Supabase Auth: Google only; Site URL = the Vercel URL; redirect URLs https://<app>.vercel.app/** and http://localhost:3000/**; keep mailer_autoconfirm off.
• Vercel preview deployments won't be able to sign in until their preview origins are added to CORS_ORIGINS and to the Supabase redirect URLs.

Architecture at a glance

• Auth: Streamlit native OIDC (st.login / st.user) with Google. Login uses only openid + email scopes. Sending a bulletin uses a separate, opt-in gmail.send grant (google_oauth.py).
• Storage: one relational database via SQLAlchemy 2.x, selected by DATABASE_URL. SQLite for local dev, Supabase Postgres in production.
• Tenancy: every request re-derives the caller's membership + role server-side (streamlit_tenancy.require_active_church in Streamlit, api.deps.require_church in the API — both call the shared DB check tenancy.validate_active_church); all church data is filtered by the validated church_id.

Local setup

pip install -r requirements.txt
cp .env.example .env          # fill in values (never commit .env)

.env holds the environment variables (DATABASE_URL, OPENAI_API_KEY, the GOOGLE_* gmail.send client, and the migration-only NOTION_*). The Streamlit login config is separate — see the [auth] block below.

Streamlit login: .streamlit/secrets.toml

[auth]
redirect_uri = "http://localhost:8501/oauth2callback"
cookie_secret = "<strong random string>"
client_id = "<google client id>"
client_secret = "<google client secret>"
server_metadata_url = "https://accounts.google.com/.well-known/openid-configuration"

The redirect_uri must end in /oauth2callback and be registered on the Google OAuth client. It differs between dev (localhost:8501) and prod (the Streamlit Cloud URL), so each environment needs its own value.

Run

streamlit run app.py

Google OAuth client — two redirect URIs

A single Google "Web application" OAuth client backs both flows and needs two registered redirect URIs (each must match exactly — scheme, host, path, trailing slash):

1. https://<app>/oauth2callback — for st.login (handled internally by Streamlit; never reaches app code). This is the [auth] redirect_uri.
2. https://<app>/ (the bare app root, trailing slash included) — for the manual gmail.send flow. Set GOOGLE_OAUTH_REDIRECT_URI to this exact app-root value. The return trip is identified by its single-use ?state=; Streamlit's login callback lives on /oauth2callback and never delivers a code to the app root, so the flows cannot collide.

Enable the Gmail API, and while the app is unverified add each sender under Test users (up to 100). A dedicated OAuth client for gmail.send is an allowed, slightly safer alternative to reusing the login client.

Database

• Local dev: DATABASE_URL=sqlite:///data/app.db.
• Production (Supabase): use the session pooler host, e.g. postgresql+psycopg2://postgres.<project-ref>:<password>@aws-0-<region>.pooler.supabase.com:5432/postgres. Do not use the direct db.<ref>.supabase.co host — it is IPv6-only on the free tier and unreachable from Streamlit Community Cloud's IPv4-only egress.

Store DATABASE_URL in Streamlit secrets (App → Settings → Secrets).

Schema migrations (Alembic)

Since slice 1a the schema comes from Alembic (backend/alembic.ini, backend/migrations/), and the API no longer creates tables at startup: it logs the WARNING schema revision <current> != head <head> when the database is not at the latest revision. Alembic reads only an exported DATABASE_URL, never backend/.env. For a local database, from backend/:

cd backend
export DATABASE_URL=sqlite:///../data/app.db
../.venv/bin/alembic upgrade head

A local database made by the old create_all (before slice 1a) needs stamping first: ../.venv/bin/alembic stamp 0001_baseline && ../.venv/bin/alembic upgrade head. One made by init_db() after slice 1a (local Streamlit, import_hymnal.py) already has head's tables: ../.venv/bin/alembic stamp head. Tests build their SQLite databases themselves. The Postgres-only tests run with TEST_DATABASE_URL set to a local, throwaway Postgres (TEST_DATABASE_URL=postgresql://postgres:<password>@localhost:5432/postgres .venv/bin/python -m pytest -m postgres -q from the repo root) and skip without it. Production is migrated by Railway's Pre-deploy Command, set in the Railway UI (backend/railway.toml only records it); the one-time stamping runbook and the details are in backend/migrations/README.md.

One-time migration (Notion + legacy contacts → database)

Import the founder church's existing Notion data (hymns, archive, usage) and saved contacts. Set NOTION_API_KEY, NOTION_DATABASE_ID, NOTION_ARCHIVE_DATABASE_ID, NOTION_USAGE_DATABASE_ID, and DATABASE_URL, then:

python migrate_to_db.py \
  --founder-email [email protected] \
  --church-name "Conner Presbyterian" \
  --timezone America/New_York

It creates the founder user + church (owner), imports the enriched Notion hymns into hymn_catalog, seeds the founder church's hymns, imports the archive and hymn usage, and copies contacts into the founder church only. It is re-runnable to convergence (idempotent) and prints a report of counts plus any liturgy rows flagged as truncated. Enrichment is validated at the end; an empty scripture lookup aborts the run non-zero.

Service rubric: hymn year and familiarity

Hymns carry two facts from Hymnary.org: the year the words were written and how many hymnals include them (familiarity). They drive the rubric's "prefer older" and "prefer familiar" hymn suggestions.

Run each command below from the repo root, with DATABASE_URL set to the deployed (Supabase) database. When DATABASE_URL is not exported, the scripts load the nearest .env found walking up from backend/ (backend/.env, else the repo-root .env). Each script first prints Database: ... (password hidden) before changing anything: check that it shows the Supabase pooler host before trusting the rest of its output.

1. The columns come from the Alembic migrations (alembic upgrade head). Railway applies them before each deploy (backend/migrations/README.md); never run Alembic against production outside that runbook.

2. Preview the fill from Hymnary.org's public API. It writes nothing and takes about a second per scripture reference:

   (cd backend && ../.venv/bin/python backfill_hymn_facts.py --dry-run)
   

   Before the real run, read the summary lines at the end. References that "hit Hymnary's 100-text cap" may leave hymns cited only by them unknown. References "Hymnary could not parse" were skipped; fix the stored reference and dry-run again.

3. Fill them in. This fills blanks only, so it is safe to re-run (for example, to retry references it reports as failed):

   (cd backend && ../.venv/bin/python backfill_hymn_facts.py)
   

Run step 3 again after importing a hymnal (import_hymnal.py) or adding hymns in Settings: new hymns start with both facts unknown, and rank as "unknown year", until the backfill fills them.

The backfill covers the shared catalog and every church's hymns, and never overwrites a value, so these re-runs are safe.

Operations

Keep-alive (required)

The free Supabase project pauses after ~7 days idle. .github/workflows/keepalive.yml runs daily at 09:17 UTC and curls the API's GET /health/ready, which runs a SELECT 1 through the API's own connection pool, so the first visitor each week never hits a paused database. The job holds no database credentials: it needs only the Actions variable API_BASE_URL (the Railway URL, already public in the frontend bundle). Details, and what to do when it is red: docs/ops-runbook.md → Keep-alive.

Backups (required)

Supabase Free retains no backups. .github/workflows/backup.yml (db-backup) runs daily at 08:37 UTC: it dumps the public schema with pg_dump (custom format, client major matched to the server), encrypts the dump with age to the public keys in .github/backup/age-recipients.txt before anything is written to disk, and uploads backup-<timestamp>.dump.age as an artifact kept 30 days. Only the owner's age private key can open it. The job reads the Supabase session-pooler URL from BACKUP_DATABASE_URL, a secret of the GitHub Environment backup that only main can use, added only after the encrypted workflow is on main. Key custody, key rotation and the restore drill are in docs/ops-runbook.md → Backups.

Limits & upgrade path

Free tier: 500 MB DB, ample connections for a handful of churches. Each church owns an independent copy of the ~700-hymn hymnal, so catalog-wide corrections do not propagate automatically. Scaling to many churches may require the paid tier (which also enables direct IPv4 connections).

Removed since single-tenant

APP_PASSWORD, the shared SMTP fallback (GMAIL_ADDRESS / GMAIL_APP_PASSWORD), the ?gmail=<email> sender mechanism, and Notion as a runtime dependency (Notion is now used only by migrate_to_db.py).

Deployment verification

Before relying on production, run through docs/manual-verification.md on the deployed URL (the two OAuth flows, login round-trip, and invite-link survival cannot be fully covered by unit tests).