Database Schema & Migrations

Briefy utilizes a relational database schema managed through versioned SQL migrations. The system is built on PostgreSQL (with extensions like pgvector for AI features) and uses Flyway to handle schema evolution. The schema is organized around core domains: user authentication, source ingestion, knowledge graph (topics), and the AI briefing generation pipeline.

The migration strategy follows a strict naming convention: V<timestamp>__<snake_case_description>.sql. Developers use a utility script to ensure unique timestamps and consistent formatting for new schema changes.

Sources: AGENTS.md:18-18, scripts/genDDLFile.sh:11-13

Migration Management

The project standardizes schema changes using Flyway migrations located in the backend resource directory. The genDDLFile.sh script automates the creation of these files.

Workflow and Conventions

• Location: apps/api/src/main/resources/db/migration
• Naming: VYYYYMMDDHHMMSS__description.sql
• Script Utility: The scripts/genDDLFile.sh script generates the file with the current UTC timestamp to prevent version collisions.

Sources: scripts/genDDLFile.sh:1-20, AGENTS.md:18-18

Core Data Model

The schema is divided into functional modules that support the application's lifecycle from user login to AI-generated briefings.

User & Authentication

This module handles user identities, roles, and session persistence.

The users table serves as the primary root for ownership across all other entities via user_id foreign keys.
Sources: apps/api/src/main/resources/db/migration/V20260205110000__auth_and_user_ownership.sql:7-33

Knowledge Graph: Sources & Topics

Sources represent ingested URLs and their extracted content, while Topics provide a structured way to categorize and link these sources.

Sources: apps/api/src/main/resources/db/migration/V20260204200000__source_full_schema.sql:4-24, apps/api/src/main/resources/db/migration/V20260211104500__topics_slice2.sql:7-13

Briefing Generation Pipeline

This module manages the multi-step AI orchestration process, including personas and execution jobs.

Entity Definitions

Briefing System Tables

Detailed configuration for the AI briefing orchestration engine.

• agent_personas: Stores AI persona configurations used during synthesis (e.g., The Skeptic, The Scientist).
• briefing_plan_steps: Defines the specific sequence of tasks assigned to personas for a briefing.
• briefing_generation_jobs: Implements a reliable queue for background processing with fields for attempts, lock_owner, and next_attempt_at.

Sources: apps/api/src/main/resources/db/migration/V20260219130000__briefing_phase3_vertical_slice.sql:20-85

Indexing Strategy

The schema uses specific indices to optimize filtering and ensure data integrity:

• Unique Constraints: uq_topics_user_id_name_normalized ensures per-user topic uniqueness.
• Filtering Indices: idx_sources_status and idx_briefings_user_status_updated optimize list views.
• Search Optimization: idx_users_email for authentication lookups.

Sources: apps/api/src/main/resources/db/migration/V20260211104500__topics_slice2.sql:35-37, apps/api/src/main/resources/db/migration/V20260219130000__briefing_phase3_vertical_slice.sql:18-19

Summary

The Briefy database schema is designed for multi-user isolation and structured knowledge management. It evolves from simple placeholders to complex structures supporting AI personas, vector-based search (via pgvector migrations mentioned in history), and automated content extraction workflows. The migration system ensures that every change is tracked and repeatable across development and production environments.