Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The database system manages persistent storage for user profiles, authentication data, and application state. Thoth uses PostgreSQL as the primary relational database and Flyway to manage versioned schema migrations.
The architecture separates infrastructure concerns (managed via Docker) from application-level data access (managed via repositories). User data is strictly scoped, ensuring that every user-scoped row carries a user ID and every query filters by it.
Sources: apps/api/spec/auth.md:28, docker-compose.yml:3-30
Database Infrastructure
The project runs a containerized PostgreSQL instance and a migration runner. The API interacts with the database using the pg client driver.
Sources: apps/api/package.json:38, docker-compose.yml:3-30
Database Services
- PostgreSQL: Serves as the primary data store using version 18.4. It persists data to the
pgdatavolume. - Flyway: Executes SQL migrations stored in
apps/api/sql. It waits for the database to be healthy before attempting migrations.
The diagram shows the relationship between the database engine, the migration runner, and the API application within the Docker environment.
Sources: docker-compose.yml:3-56
Schema Modules
Bio Module
The Bio module stores personal information for site owners. It enforces unique constraints to ensure each user owns only one bio and each handle is unique across the platform.
Sources: apps/api/spec/bio.md:58-62, apps/api/test/modules/bio/infrastructure/bio.pg-repository.test.ts:64-70
| Field | Type | Constraint | Description |
|---|---|---|---|
id | UUID | Primary Key | Unique identifier for the bio record. |
user_id | String | Unique | Links the bio to a specific authenticated user. |
handle | String | Unique | URL-friendly slug (max 60 chars, lowercase, hyphens). |
name | String | Not Null | The owner's display name (max 100 chars). |
headline | String | Not Null | Professional headline (max 200 chars). |
about | String | Not Null | Detailed biography (max 5000 chars). |
created_at | Timestamp | Not Null | Record creation time. |
updated_at | Timestamp | Not Null | Last modification time. |
| Sources: apps/api/spec/bio.md:12-16, apps/api/test/modules/bio/infrastructure/bio.pg-repository.test.ts:10-19 |
Authentication Module
The authentication schema is managed by Better Auth. It handles user identities, sessions, and OAuth federated login details.
Sources: apps/api/spec/auth.md:37-40
The ER diagram illustrates the relationship between core authentication entities and the user-specific bio profile.
Sources: apps/api/spec/auth.md:37-40, apps/api/test/modules/bio/infrastructure/bio.pg-repository.test.ts:10-19
Data Access & Integrity
The PostgresBioRepository implements the persistence logic. It uses specific SQL patterns to maintain integrity:
- Atomic Inserts: Uses
ON CONFLICT (user_id) DO NOTHINGto prevent multiple bios for the same user. - Constraint Mapping: The repository catches Postgres error code
23505(Unique Violation) and identifies the specific constraint (e.g.,bio_handle_key) to return domain-specific errors likeHandleTaken. - User Scoping: Every update or insert requires a
userId(Principal) to ensure users can only modify their own data.
Sources: apps/api/test/modules/bio/infrastructure/bio.pg-repository.test.ts:68, 102, 110, apps/api/spec/auth.md:28-30
Configuration Parameters
| Environment Variable | Source | Description |
|---|---|---|
POSTGRES_DB | docker-compose.yml | Name of the database (default: thoth). |
DATABASE_URL | docker-compose.yml | Connection string used by the API to reach Postgres. |
FLYWAY_URL | docker-compose.yml | JDBC connection string for migration execution. |
| Sources: docker-compose.yml:8, 22, 45 |
Summary
The Thoth database system provides a robust foundation for multi-tenant data storage. By utilizing Flyway for versioned migrations and containerized PostgreSQL, the project ensures consistent environments across development and production. The schema enforces strict ownership and uniqueness rules at the database level, which are surfaced as domain-specific exceptions in the application layer.