# AquaArchive Backend Schema Document (BSD)
**Version:** 1.0 Draft | **Date:** 2026-10-28 | **Status:** Logical overview, not migration approval

## 1. Authority and current gate
The executable schema is authoritative in:
- `database/migrations/0001_initial_schema.sql`
- `database/migrations/0002_rls_and_security.sql`
- `database/migrations/0003_functions_and_rpc.sql`
- Rollback: `database/rollback/0001_down_migrations.sql` (destructive; not a routine recovery path)

**Staging posture: HOLD.** This document authorizes no remote connection, migration, rollback, reset, or deployment. Verify every name and definition against the current SQL before relying on it.

## 2. Data principles
- Use internal UUID keys where defined in migrations.
- Keep client UUID, temporary physical tag, and permanent artifact code separate.
- Allocate permanent codes server-side.
- Isolate exact coordinates from general site metadata.
- Preserve archaeological provenance and original evidence relationships.
- Treat database and object-storage backups as separate recovery concerns.

## 3. Logical entity inventory
This is a conceptual list from project design notes, not a guarantee that each table exists in the latest schema.

| Entity/table family | Purpose / controls |
|---|---|
| `projects` | Project configuration and access boundary |
| `project_memberships` | User/project role assignments |
| `sites` | General site/zone metadata, no exact coordinates |
| `site_coordinates` | Restricted exact GPS/depth; intended admin/PI access |
| `artifacts` | Artifact metadata, client ID, temporary tag, permanent code |
| `artifact_photos` | Evidence metadata, category, checksum and verification |
| Field-session entities | Context for field activity; confirm exact names |
| Report/snapshot entities | Draft/official output metadata and source period |
| `audit_events` | Traceable important actions with safe access policies |
| `backup_objects` | Backup inventory/state tracking; not proof of recovery |

## 4. Identifier model
1. `client_record_id`: stable client UUID for offline idempotency.
2. `temporary_field_tag`: physical field label, e.g. `TEMP-D1-0042`; final format must match approved ADR and SQL.
3. Permanent artifact code: e.g. `OR27-S01-A0001`, allocated only by the trusted server/database path.
Repeated idempotent creation must return the existing record/code rather than advance the sequence.

## 5. Coordinate isolation
- General `sites` records contain only general site/zone information.
- Exact values belong in `site_coordinates`.
- Test RLS/API behavior with authorized and unauthorized roles.
- Exclude exact coordinates and restricted notes from general views, IndexedDB caches, service-worker caches, analytics, and logs.
- Verify effective policy behavior, not just the presence of policy SQL.

## 6. Photo metadata
Verify the actual schema for artifact/session relationship, object key, original/derivative flag, MIME type, size, SHA-256, uploader, timestamps, view category, and verification status. A stored checksum alone does not prove the binary exists or is recoverable. Ordinary application permissions should not overwrite original evidence.

## 7. RLS and privileged functions
- RLS on all exposed application tables.
- Project membership/role policies and negative authorization tests.
- Least-privilege grants and explicit function execution grants.
- For every `SECURITY DEFINER` function, verify owner, grants, qualified references, input checks, and `pg_proc.proconfig`/`search_path`.
- Do not use caller-controlled GUCs as proof of target identity.
- Health checks must be read-only.

Project notes identify `allocate_artifact_permanent_id` and `create_artifact_idempotent`; confirm signatures, owners, parameters, return types, and grants in `0003_functions_and_rpc.sql`. Do not call a live allocator as a test because it may mutate state.

## 8. Migration controls
Project notes say migrations live under `database/migrations/` and there is no `supabase/migrations/` directory. Any `supabase db push` instructions must be reviewed for conflict with the actual layout. Use one reviewed canonical route. Execute at most one migration per approval gate, inspect state read-only after each, and never automatically rerun or rollback after a failure. No migration is authorized while HOLD remains.

## 9. Schema verification checklist
- [ ] Table/column inventory matches all migration files.
- [ ] Keys, foreign keys, checks, unique constraints and indexes reviewed.
- [ ] RLS enabled and effective on exposed tables.
- [ ] Coordinate isolation tested for unauthorized roles.
- [ ] `SECURITY DEFINER` owner/grants/search_path verified.
- [ ] ID allocation and idempotency tested in isolated local test infrastructure.
- [ ] Storage policies and original immutability verified.
- [ ] Health checks are read-only and accurate.
- [ ] Backup covers database plus original photos and official PDFs.
- [ ] Isolated restore drill completed and recorded.

## 10. Backup/recovery
Recovery scope includes schema/data, roles/grants as applicable, object inventory and checksums, original photos, official reports, and independent R2 replication. Restore only into disposable isolated targets during a drill. Until evidence exists, recoverability is **NOT VERIFIED**.