Database Model

PostgreSQL is the system of record. Identifiers are UUIDs; timestamps are timestamptz in UTC. Migrations are transactional and tracked in schema_migrations.

They are applied in lexical filename order, not by version arithmetic, and every file whose filename is not already a row is applied — so a migration numbered below an already-applied one is still picked up. schema_migrations has no checksum column, so a migration edited after it was applied is undetectable. Down files exist for all 91 up migrations, and migrate down fails before rolling anything back if one is missing; it has never been run against production.

Tables

There are 91 up migrations today (highest number 0094), covering all 52 modules. The Animal-passport core is the introduction, not the inventory:

• animal, animal_ownership, animal_delegate, animal_emergency_profile, animal_photo
• audit_event (append-only; UPDATE/DELETE blocked by trigger)
• outbox_event (transactional outbox; partial index on unpublished rows)

Row-level security

73 tables carry ENABLE plus FORCE ROW LEVEL SECURITY, across five policy shapes (0072, 0073, 0076). tenant_id is the scoping column on 65 tables (0071).

This is load-bearing rather than housekeeping: a connection holding SUPERUSER or BYPASSRLS bypasses every one of those policies unconditionally — the database looks protected and is not. That was the real state of this deployment before EPIC-52, and it is why the application no longer connects as the table owner. See ADR-013, ADR-017, ADR-018, ADR-026 and Multi-tenancy.

Notable constraints & indexes

• animal.chip_number unique where non-empty.
• At most one primary photo per animal (partial unique index).
• FK indexes on delegates/photos; audit indexed by object and actor.

Retention & partitioning

High-volume tables (future GPS positions, audit) are time-partitioned when needed. Retention/export/delete workflows are part of the privacy milestone.