Supabase backend

Project

Two hosted projects in the existing Sefaja organisation, incico-staging and incico, both in eu-west-1 (Ireland, where Saymail runs) so all personal data stays in the EU. Staging exists (project ref ugdoylltwhcbegccqlje, created 2026-09-12); the production project is created in phase 7, because nothing needs it before the beta. A third, local project runs under supabase start for tests. Configuration lives in supabase/config.toml in the repo, migrations in supabase/migrations/YYYYMMDDHHMMSS_snake_case.sql, functions in supabase/functions/<name>/{index.ts,handler.ts,handler.test.ts}, pgTAP tests in supabase/tests/*.sql, following Saymail’s web/supabase layout.

The local stack runs on 563xx instead of the default 543xx so it can sit next to other Supabase projects on the same machine: API 56321, database 56322, shadow database 56320, Studio 56323, mail catcher 56324, pooler 56329. db reset re-applies the migrations and the seed but does not re-read config.toml; a changed [auth] setting or hook needs supabase stop followed by supabase start.

Auth configuration

Setting	Value
Providers	Email (password + OTP/magic link), Google, Apple. Microsoft prepared but disabled until decided.
Confirm e-mail	Required
Password	Min 10 chars, leaked-password protection on
Refresh token rotation	On, reuse interval 10 s
JWT expiry	3600 s
Site URL	https://www.incico.eu
Redirect allow-list	https://www.incico.eu/auth/callback, https://www.incico.eu/auth/reset
CAPTCHA	Cloudflare Turnstile on sign-up and recovery
SMTP	Sefaja’s transactional sender (account@incico.eu) via a provider with DKIM; templates reskinned from Saymail’s dark-mode-aware HTML and referenced from config.toml.
Hooks	before user created: pg-functions://postgres/public/before_user_created_hook, which refuses a sign-up whose domain is in disposable_email_domains. There is no custom access-token hook: an account’s id is auth.uid(), so a claim would only duplicate it.
E-mail templates	confirmation, magic link, recovery and e-mail change carry the 6-digit code and the link (supabase/templates/). Body and subject branch on {{ .Data.language }}, the language the app writes into the user’s metadata (A81): Dutch for nl, English otherwise (A92)

Deep links: Auth e-mails point at https://www.incico.eu/auth/callback?.... The website serves /.well-known/assetlinks.json and /.well-known/apple-app-site-association so the installed app opens the link directly (assetlinks.json lists the Play app signing key A8:49:14:…:27:31:54 for store installs and the shared debug key F8:83:0B:…:8C:AC for local builds; the upload key does not belong there); if the app is not installed, the page shows “Open Incico on your phone” and store badges.

Database

Schema and access rules are in Data model and Access control. Conventions, taken from Saymail’s migrations:

• alter table ... enable row level security; immediately after every create table.
• revoke all on table ... from anon, authenticated; at the end of every migration that creates tables, then the grants that have a matching policy, with update granted column by column. The project default hands every new public table to anon and authenticated, so the revoke is what makes the grants the whole truth.
• Every function: security definer set search_path = public, followed by revoke execute ... from anon, authenticated, public; and an explicit grant execute ... to authenticated; or to service_role;. A function that is only called from a trigger keeps no grant at all.
• Narrative comments in migrations explaining why.
• updated_at trigger from a shared set_updated_at() function.
• Seed data (supabase/seed.sql) creates three test accounts with connected profiles for local development and E2E tests.
• Phase 3 adds 20260913100000_phase3_profile_rules.sql (the detail limits, the date subset rule, delete_profile, replace_detail_type) and supabase/tests/11_profile_rules.sql; the pgTAP suite is eleven files.
• Phase 4 adds 20260914100000_phase4_contacts.sql (the hourly expire_invitations job, invitations.reminded_at, remind_invitation, preview_circle over the extracted snapshot_for_circle, search_token_for_name) and 20260914100100_invitation_parties.sql (invitation_parties, which hands each side of a pending invitation the other’s default name and avatar path, since profiles stays closed until a connection exists), with supabase/tests/12_phase4_contacts.sql and 13_invitation_parties.sql; the pgTAP suite is thirteen files. The client-callable functions of these two migrations are remind_invitation, preview_circle, search_token_for_name and invitation_parties, all granted to authenticated only. 20260914100300_prune_notifications.sql adds the second scheduled job, prune-notifications, running nightly at 03:15 to delete notifications older than ninety days (app/16, app/20) with supabase/tests/15_prune_notifications.sql.
• Phase 5 adds 20260915100000_phase5_services.sql: the two mirrored tables sync_services (provider, label, account hint, the device_id that holds the credential, circle_ids, allow_delete, last_sync_at) and sync_links (service_id, connection_id null, external_id null, synced_hash, hidden, unique on (service_id, external_id) and a second unique index sync_links_service_connection_idx on (service_id, connection_id) where connection_id is not null, so one contact has one link per service and a released row stays out of the way), both with the ownership policies and column-level insert and update grants; the ten-services limit (too_many_services); the trigger that empties connection_id when a connection is deleted, so the entry lands in the delete bucket with an updated_at the other device will refetch; and cross_reference(text[]), the bulk lookup the address-book cross-reference calls, over the extracted profile_is_findable() that search_profiles now shares. supabase/tests/16_phase5_services.sql covers it and the pgTAP suite is sixteen files. No edge function is added: the Google authorisation is a public PKCE client on the device and the token never reaches the server (A21), so google-contacts-token-check stays removed.
• Phase 6 adds 20260918100000_phase6_settings.sql: accounts.preferred_language becomes nullable with no default (null is “Device language”, A81), contact_sort and phone_format (A82) and the four notify_* switches (A83) join the account and its update grant, and four client-callable functions arrive, all granted to authenticated only: list_sessions and revoke_session over auth.sessions (A84) and blocked_profiles and unblock_profile (A85). supabase/tests/17_phase6_settings.sql covers them and the pgTAP suite is seventeen files.

Storage

Bucket	Public	Limits	Policy
profile-photos	no	2 MB, image/jpeg, image/webp	Owner full access to <profile_id>/*; connected viewers select via can_view_photo()
exports	no	50 MB, application/zip	Owner select on <account_id>/* for 24 h; written by export-account

Clients resize to 1024 px longest side and strip EXIF (including GPS) before upload.

Edge functions

Function	Trigger	JWT
push-fanout	The notify_push trigger on notifications insert, which posts the row through pg_net	webhook secret header
delete-account	User (from app or website)	user JWT, re-verified; CORS for the website
export-account	User (from app or website)	user JWT; CORS for the website
verify-email-detail	User (send / check code)	user JWT
google-contacts-token-check	none at launch (placeholder removed)	n/a

Every function has handler.ts (pure, testable, receives a deps object) and index.ts (Deno.serve wiring), with Deno tests next to it. push-fanout reads the account’s per-kind switches first and answers {muted: true} without looking up a device when the kind is off (A83). delete-account and export-account answer CORS for the account page of the website (see Access control) and mail in the account’s language. verify_jwt stays true for user-called functions; only push-fanout is deployed with --no-verify-jwt and checks its own secret.

Push delivery

• Android: Firebase Cloud Messaging, HTTP v1 API, called with a Google service account whose JSON lives only in Supabase secrets. Firebase is initialised manually in the app from build-time values, without the google-services plugin, as Saymail does. The message is data-only and high priority — {"message":{"token":"…","data":{"kind":"…","id":"…"},"android":{"priority":"HIGH"}}} — with no notification block, so FirebaseMessagingService always runs and renders the notification itself in the device’s language and in the “Contacts” channel.
• iOS: APNs with a .p8 token key (Key ID, Team ID) in Supabase secrets. One message per notification row, apns-push-type: alert, apns-priority: 10, body {"kind":"…","id":"…","aps":{"alert":{"title":"…","body":"…"},"badge":<unread count>,"thread-id":"<kind>","content-available":1}}. content-available is what wakes the app for the delta fetch before the tap, so no separate silent push is sent.
• Payload: kind and id and nothing else (A22). The id is the notification row’s id; the app reads that row from its cache after the delta fetch and routes from its payload. The title and body iOS shows are rendered server-side from devices.locale; the Android strings are the same sentences, kept in mobile/design/i18n/*.json under push.<kind>.*. No contact data travels either way.
• Dead tokens (UNREGISTERED, 410) are deleted from devices; other failures are only counted, because the notification row stays in the database and the app reads it on the next sync.
• The trigger reads push_fanout_url and push_fanout_secret from Vault and posts through pg_net with a 5-second timeout. The whole body sits in an exception block: a missing Vault entry, an absent edge function or a slow pg_net must never cost the user the notification row itself. The body mirrors the shape a hosted Supabase database webhook sends, so switching to one later needs no change in the function.
• Coalescing is done where the row is written, not in the fan-out: while an unread contact_updated or circle_changed for the same connection exists, a new one is dropped. A timed batching window on top of that is deferred until there is traffic to measure.

Realtime

The app subscribes, while in the foreground, to postgres_changes on notifications filtered by account_id = eq.<me>. RLS applies to Realtime, so a user can only subscribe to their own rows. Any event triggers a delta fetch of connections whose snapshot_at is newer than the local cache.

Deployment

supabase/deploy.ps1 -Target staging|production (PowerShell 7), modelled on Saymail’s web/deploy.ps1:

1. Refuse unless the current branch is main (production) or develop (staging), and refuse a dirty working tree: a deploy has to be reproducible from a commit.
2. Read .env.staging / .env.production from the repository root (gitignored) and check the required keys: SUPABASE_PROJECT_REF, WEBHOOK_SECRET, SITE_URL. Multi-line values may be stored base64 encoded under a _B64 suffix.
3. npx supabase link --project-ref <ref>.
4. npx supabase db push.
5. npx supabase config push — the [auth] block, the redirect allow-list and the before user created hook are in config.toml, so they are deployed, not clicked.
6. npx supabase functions deploy <each> (--no-verify-jwt only for push-fanout).
7. npx supabase secrets set for whichever of WEBHOOK_SECRET, SITE_URL, APNS_BUNDLE_ID, MAIL_FROM, FCM_SERVICE_ACCOUNT_JSON, APNS_KEY_P8, APNS_KEY_ID, APNS_TEAM_ID, RESEND_API_KEY and WEB_ORIGINS (extra CORS origins such as the Netlify deploy-preview pattern) the environment file has. SUPABASE_DB_PASSWORD is deliberately not among them: it is a deploy credential, not a function secret.
8. Upsert push_fanout_url and push_fanout_secret into Vault through the management API (POST /v1/projects/<ref>/database/query), creating or updating by name. This is what arms the push trigger.
9. Print a summary, and name the secrets still missing so the FCM service account, the APNs key and the Resend key can be added when they exist.

-WhatIf prints every step, with secret values replaced by ***, and executes nothing. Errors from the Vault call are reported by status code only, because the response body can echo the failing statement, secret and all. db push and config push ask for confirmation, so the script is meant to be run interactively.

The Supabase management token comes from $env:SUPABASE_ACCESS_TOKEN, and otherwise from the Windows Credential Manager entry Supabase CLI:supabase that supabase login writes — as Saymail’s TestFlight script does, never from a file.

Continuous integration

.github/workflows/backend.yml runs on every push and pull request that touches supabase/**: deno lint and deno fmt --check over the functions, supabase start with the heavy services excluded, supabase test db (pgTAP), supabase db reset to prove the seed still applies, and deno test for the edge functions. The CLI version is pinned.

Costs and limits to watch

Free-tier limits are irrelevant because the organisation is already on a paid plan. Realtime connections are per-device and only while foregrounded. Storage grows by at most 4 × ~300 KB per profile. Edge-function invocations are dominated by push fan-out, which is bounded by the number of connection updates.