Files
faunosteo/docs/development/BACKEND_ARCHITECTURE.md
bchanot b0cce2cd8c docs(docs): add SaaS implementation plan and architecture docs (step 0)
Eight documents under docs/development/ confronting the approved business
architecture with the audited code: 27-step plan, repository migration map,
domain map, frontend/backend architecture, migration and test strategy,
open questions. Reference document placed at the repository root.
2026-10-08 21:41:39 +02:00

199 lines
11 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# Backend architecture (Supabase strategy)
Status: Step 0 deliverable, 2026-10-08. Conceptual only. No migration, table,
policy or function is created by this document. Table and function names
follow `faunosteo-business-architecture.md` §4–§20.
## 1. Components and what runs where
```text
Browser (apps/app, anon key + user JWT)
│ tables via RLS, RPC (security definer), Storage signed URLs
▼
Supabase (self-hosted on Scaleway at launch, §31)
├─ Auth (GoTrue): email/password, Google, Apple, OTP, phone
├─ PostgreSQL: schemas public / private / audit, RLS, functions, pg_cron
├─ Storage: private buckets, storage.objects policies
├─ Edge Functions (Deno): public endpoints, webhooks, signed-URL issuance
└─ Realtime (optional): report sync state
Worker (apps/worker, service key, same host)
└─ jobs table in PostgreSQL, no Redis
public-web (anon key, SSR)
└─ public views / RPCs only
```
The browser never holds a service-role key, admin credential, storage master
key or third-party refresh token (§17.1).
## 2. Auth
| Need | Mechanism | Note |
|---|---|---|
| Email/password, magic link/OTP | GoTrue built-in | strong password policy configured server-side |
| Google, Apple | GoTrue OAuth providers | Apple requires a paid developer account and a services id: dependency to confirm |
| Phone verification | GoTrue phone OTP needs an SMS provider (Twilio, MessageBird, Vonage) | cost and EU processing to confirm; see `OPEN_QUESTIONS.md` Q7 |
| Email verification | GoTrue, mandatory before professional features | |
| MFA | GoTrue TOTP; mandatory for owner/admin/moderator/support roles, enforced by RLS helper `has_mfa()` on admin tables | |
| Roles | `profile_roles` table, never JWT custom claims as the only source (claims can lag) | helpers read the table |
| Impersonation | dedicated RPC issuing a short-lived scoped session, audited, banner in UI (§17.5) | after MVP core |
`profiles.id = auth.users.id`, created by a trigger on `auth.users` insert.
## 3. PostgreSQL layout
| Schema | Content | Exposure |
|---|---|---|
| `public` | tables exposed to PostgREST, all with RLS enabled, separate SELECT/INSERT/UPDATE/DELETE policies | browser, public-web, worker |
| `private` | security-definer helper functions, internal tables (counters, job queue, route cache), triggers | not exposed to PostgREST |
| `audit` | `audit_logs`, append-only, insert via `private.log_event()` | read by admins through a view |
Conventions fixed now so every step follows them:
- UUID primary keys (`gen_random_uuid()`), `created_at` / `updated_at` with a shared trigger, soft delete `deleted_at` where the architecture names it.
- Enums as PostgreSQL `enum` types when the value set is closed by the business (report status, appointment status), `text` + CHECK when the set is expected to grow (document types).
- JSONB documents (`reports.current_document`, `anatomical_sheet_versions.content_jsonb`) carry their own `schema_version` and are validated by the matching Zod schema at the application boundary and by a CHECK on `schema_version` in SQL. SQL does not re-implement Zod.
- Every table exposed to the browser: RLS enabled, no `USING (true)` policy for `authenticated`, no anonymous policy on core tables (§17.4).
## 4. RLS strategy
### 4.1 Helpers
Security-definer functions in `private`, `STABLE`, `search_path` pinned,
`SET row_security = off` inside where they read tables that have policies
referencing them (prevents recursion), one index per access path they use:
```text
private.is_platform_owner(uid) private.is_platform_admin(uid)
private.is_platform_moderator(uid) private.is_support(uid)
private.is_org_member(uid, org) private.is_org_manager(uid, org)
private.is_org_practitioner(uid, org)
private.can_view_client(uid, client) private.can_view_animal(uid, animal)
private.can_view_report(uid, report) private.can_edit_report(uid, report)
private.can_use_sheet(uid, sheet) private.can_fork_sheet(uid, sheet)
private.has_active_entitlement(uid, capability text)
```
Each helper ships with pgTAP tests in the same migration step: positive case,
negative case, expired grant, revoked grant, wrong organization.
### 4.2 Policy rules per domain (summary of §17.3)
| Table family | SELECT | INSERT/UPDATE/DELETE |
|---|---|---|
| `profiles` | own row; admins via view | own row; roles only through RPC |
| `organizations`, `organization_members` | members | managers/owners through RPC (`add_member`, `remove_member`, `set_role`) so that "secretary cannot self-grant practitioner" is one place |
| `clients`, `client_contacts`, `client_consents` | `can_view_client` | create by the practitioner who starts the relationship; core identity edits restricted (open question Q6) |
| `animals` | `can_view_animal` | practitioner with an active relationship or grant |
| `reports` | `can_view_report` | `can_edit_report` for drafts; completion and numbering only via RPC |
| `report_revisions` | same as parent report | insert via RPC/trigger only |
| `anatomical_sheets` | public metadata through a view; full row for creator, grantee, or follower of a public sheet | creator only; publish via RPC |
| `anatomical_sheet_versions` | `can_use_sheet` on the parent | insert draft by creator; published rows immutable (trigger raises on UPDATE of `content_jsonb` when `status = published`) |
| `practitioner_follows`, `sheet_usage_grants` | involved parties | own follows; grants by sheet creator or org manager |
| `appointments` and agenda tables | owner scope; public availability through RPC only | booking through RPC with row locks |
| `plans`, `subscriptions`, `entitlement_grants` | own / org manager | Stripe webhook (service) and admin RPC only |
| `audit.audit_logs` | admins | append-only via `private.log_event` |
| `storage.objects` | per bucket policy calling the owning domain's helper | upload paths validated against `assets` rows |
### 4.3 Public endpoints
Public profile, sheet discovery, availability and booking are security-definer
RPCs or narrow views returning approved columns (§17.4). The anon role gets
EXECUTE on those functions and nothing else.
## 5. Storage
Buckets, all private: `avatars`, `logos`, `professional-documents`,
`client-files`, `animal-files`, `report-files`, `anatomical-sheets`, `imports`.
Object key: `<resource-type>/<resource-id>/<asset-id>/<safe-filename>`. The
`assets` table is authoritative; `storage.objects` policies call the owning
helper (`can_view_report` for `report-files/...`). Reads go through signed
URLs issued by an RPC/Edge Function after the same check.
SVG pipeline (§15.3): upload lands in a quarantine prefix with
`assets.sanitization_status = pending`; a server sanitizer (Edge Function or
worker) rewrites the file (strip `<script>`, `<foreignObject>`, `on*`,
`javascript:`/external hrefs, `<use>` to external documents, CSS `url()`),
stores the clean bytes under the final key and sets `status = clean`. Policies
only allow reading `clean` assets to anyone other than the uploader. The
current 10-line client sanitizer stays as a pre-check in the editor, not as
the boundary.
Archive (§15.4, §16): S3-compatible cold storage with lifecycle rules, no
Object Lock by default. Out of MVP core; designed at the data-lifecycle step.
## 6. Edge Functions vs PostgreSQL functions vs worker
Decision rule for each server-side behaviour:
| Put it in | When |
|---|---|
| PostgreSQL function / trigger (RPC) | Needs transactional guarantees with the data: report numbering, completion + quota, booking slot lock, grant evaluation, version publication, GC preconditions |
| Edge Function | Needs HTTP from outside or secrets: Stripe webhooks, public widget endpoints, signed-URL issuance, calendar OAuth callbacks, email send trigger, SVG sanitization (small files), import preflight (small files) |
| Worker | Long or scheduled: reminders, expiry sweeps (unless `pg_cron`), calendar sync, route cache, long imports, archive, GC sweeps, server PDF (later) |
Self-hosted note: Edge Functions on a self-hosted stack run in the
`edge-runtime` container and must be deployed by hand. The first steps lean
on PostgreSQL functions, which the CLI migrates and tests natively. See
`OPEN_QUESTIONS.md` Q2 on VPS sizing.
## 7. Migrations
- Supabase CLI, `supabase/migrations/<timestamp>_<name>.sql`, forward-only,
one or more per implementation step, reviewed in the step's PR.
- A migration never edits an applied migration; corrections are new migrations.
- Each migration that adds a table also adds: RLS enable, policies, indexes
listed in §32 of the architecture, pgTAP tests, and regenerated types.
- `supabase db reset` must succeed from zero on CI, then `supabase test db`,
then the app test suite (§36).
- Seed data (`supabase/seed/`): species list, plans, the official equine sheet
v1 produced by the importer from the two SVGs and the catalog. Seeds are
idempotent and versioned like migrations.
- Production changes follow expand/contract when a column is renamed or
dropped (the `deprecation-and-migration` skill applies).
## 8. SQL tests
pgTAP under `supabase/tests/`, run by `supabase test db`:
- helper function unit tests (one file per helper);
- per-table RLS tests: as user A, as user B, as org member, as anon, as
service; each verifies both the allowed rows and the denied rows;
- transactional rules: numbering under concurrency (two sessions), booking
double-request, completion beyond quota, publish-then-update raises;
- immutability and GC preconditions for sheet versions.
Representative cross-tenant cases are a release gate (§30).
## 9. Generated types
`supabase gen types typescript --local > packages/supabase/src/database.types.ts`
after every migration. CI regenerates and fails on diff. Zod schemas in
`packages/domain` remain the contract for JSONB documents; generated types
cover columns and RPC signatures.
## 10. What must stay server-side (brief §19, architecture principles 6–13)
1. Authorization: every read/write of client, animal, report, sheet, grant.
2. Report numbering, completion, free-plan quota, 5-day free edit window.
3. Sheet version immutability, publication, deprecation, garbage collection.
4. Explicit `annotation → finding zone` mapping validation on publish (no fuzzy at runtime).
5. Booking slot evaluation, pending-request limits, expiry, race protection.
6. Entitlement computation and Stripe state.
7. Referral eligibility and caps.
8. Client candidate matching and sharing consent flows.
9. SVG sanitization and signed URL issuance.
10. Import preflight, quota check and atomic commit.
11. Audit logging, impersonation, moderation.
12. Archive and deletion workflows.
## 11. Observability and operations (minimum for MVP)
- Structured logs without clinical data or secrets; request correlation id
propagated to `audit_logs`.
- Daily DB backup + monthly retained copy; restore rehearsed before the first
paying cohort (§31, §38).
- Dependency audit (`pnpm audit`) and secret scan (gitleaks hook already in
`.githooks`) in CI.
- Security headers at the reverse proxy and in nginx (hardening step).