Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The SecondLayer project utilizes a distributed database architecture consisting of PostgreSQL for structured data, Redis for caching, and Qdrant for vector embeddings. The system is designed as a monorepo where each major service (Backend, RADA, OpenReyestr) manages its own database schema and migrations to support Ukrainian legal tech operations, including court case analysis, legislative tracking, and business registry lookups.
The database layer is central to the project's "Triple Transport System," providing the persistent storage necessary for AI-powered legal document analysis and semantic search. Each environment (Local, Dev, Stage, Prod) maintains distinct databases to ensure isolation and reliability.
Sources: CLAUDE.md:1-50, deployment/LOCAL_DEVELOPMENT.md:1-20
Database Architecture & Core Components
The project employs multiple database technologies to handle different data requirements. PostgreSQL serves as the primary relational store, while Qdrant handles high-dimensional vector data for semantic search.
Multi-Service Database Distribution
Each service in the monorepo is allocated specific ports and dedicated databases to prevent resource contention.
| Service | Primary DB | Port | Purpose |
|---|---|---|---|
| mcp_backend | PostgreSQL | 5432 | Primary court cases and legal documents |
| mcp_rada | PostgreSQL | 5433 | Parliament data (deputies, bills, voting) |
| mcp_openreyestr | PostgreSQL | 5435 | State Register (entities, beneficiaries) |
Sources: CLAUDE.md:110-125, deployment/LOCAL_DEVELOPMENT.md:70-85
Relational Schema Overview (PostgreSQL)
The PostgreSQL schema is partitioned into several key modules based on functional requirements:
- Legal Data Management: Stores court cases, legislation texts, and semantic sections.
- Time & Billing (Phase 1): Manages billable hours, hourly rates, and automated invoicing.
- Dynamic Template System: Stores LLM-generated prompt templates and classification logs.
- Operational Data: Tracks API costs, audit logs, and user sessions.
Sources: IMPLEMENTATION_PHASES.md:15-35, mcp_backend/src/services/template-system/README.md:150-170
The diagram above illustrates the Entity Relationship for the Time Tracking & Billing module introduced in Migration 037.
Sources: IMPLEMENTATION_PHASES.md:37-55
Migrations & Schema Evolution
The project uses a sequential, file-based migration system located within each service's src/migrations/ directory. Migrations are written in SQL for schema changes and TypeScript for complex logic.
Migration Management
Migrations are executed automatically during local development via a dedicated migrate-local Docker service. In production environments, they are triggered as part of the deployment pipeline.
- mcp_backend: Contains migrations for core features like Template Systems (027) and Time/Billing (037).
- mcp_rada: Manages schemas for deputies, bills, and voting records (001-003).
- mcp_openreyestr: Handles large-scale imports of legal entities and debtors.
Sources: CLAUDE.md:135-145, deployment/LOCAL_DEVELOPMENT.md:15-25, mcp_rada/IMPLEMENTATION_STATUS.md:15-25
Core Tables Summary (Migration 037)
| Table | Description | Key Constraints |
|---|---|---|
time_entries | Billable hours with status workflow | Status: draft, submitted, approved, invoiced |
active_timers | Running timers per user per matter | One per user per matter |
user_billing_rates | Hourly rates with effective dates | Linked to user_id |
matter_invoices | Invoice headers (format: INV-YYYY-NNNN) | Unique invoice_number |
invoice_payments | Records of full or partial payments | Linked to invoice_id |
Sources: IMPLEMENTATION_PHASES.md:40-55
Automated Initialization & Security
To prevent "password authentication failed" errors during deployment, the project includes an automated initialization sequence for PostgreSQL.
This flowchart shows the database initialization process used to ensure consistent environments across local and remote deployments.
Sources: deployment/CREDENTIALS_SETUP.md:70-90, deployment/QUICK_START.md:1-20
Database Initialization Components
- init-postgres.sh: A shell script that runs on container startup to validate and create the application user (
POSTGRES_USER) and database (POSTGRES_DB). - init-db.sql: A template file containing the base SQL structure required for the initial setup of the environment.
- check-credentials.sh: A pre-deployment script that validates the presence of superuser and application passwords in the
.envfile.
Sources: deployment/CREDENTIALS_SETUP.md:50-65, deployment/QUICK_START.md:25-40
Specialized Service Schemas
RADA Parliament Schema
The RADA service tracks the legislative process through specialized tables:
deputies: Profile data for parliament members.bills: Tracking for legislative drafts (cache TTL: 1 day).voting_records: Historical data on deputy votes.legislation: Full-text storage for laws (cache TTL: 30 days).
Sources: mcp_rada/IMPLEMENTATION_STATUS.md:75-100
Dynamic Template System Schema (Migration 027)
This system utilizes several tables to support self-learning AI capabilities:
question_classifications: Audit trail of LLM-determined intents.template_generations: Storage for templates pending admin approval.template_usage_metrics: Daily aggregated stats for template ROI analysis.
Sources: mcp_backend/src/services/template-system/README.md:150-175
Summary
The Database Schema & Migrations infrastructure of SecondLayer provides a robust foundation for legal data processing. By leveraging a multi-database approach (Relational, Vector, and Key-Value) and an automated, idempotent migration system, the project ensures high data integrity and scalability. The separation of concerns between services allows for independent evolution of the Backend, RADA, and Registry schemas while maintaining a unified deployment workflow.
Sources: CLAUDE.md:100-110, deployment/LOCAL_DEVELOPMENT.md:210-220