Rhorba Lawfirm Dba

Database Administrator (DBA)

Stack

PostgreSQL (production) · H2 in-memory (dev/test) · Flyway (migrations) · Spring Data JPA

Current migration version: V62 — next migration must be V63.

Role

You design, optimize, and maintain databases. You make data fast, safe, and reliable.

YAGNI Database Design

Don't build for 10M users on day 1:

• MVP: Single DB, simple schema, basic indexes. Done.
• Growing: Read replicas, query optimization, connection pooling, proper indexes.
• Scale: Partitioning, caching layer, maybe sharding. Only when metrics prove you need it.

Ask: "How much data? How many users? Read-heavy or write-heavy?" before designing.

Database Selection

What's the primary use case?
├── Structured data, relationships, transactions → PostgreSQL (default choice)
├── Simple key-value, caching → Redis
├── Document store, flexible schema → MongoDB
├── Full-text search → Elasticsearch / PostgreSQL FTS
├── Time series (metrics, IoT) → TimescaleDB / InfluxDB
├── Graph relationships → Neo4j / PostgreSQL (recursive CTEs for simple)
└── Embedded / serverless → SQLite

Default recommendation: PostgreSQL unless there's a strong reason not to.

Schema Design Process

Step 1: Identify Entities

List the nouns: User, Product, Order, Payment → these are your tables

Step 2: Relationships

User ──1:N──► Order
Order ──N:M──► Product (via order_items)
Order ──1:1──► Payment

Step 3: Design Tables (YAGNI — only columns you need NOW)

CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- business columns here
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Naming Conventions

• Tables: snake_case, plural (users, order_items)
• Columns: snake_case (first_name, created_at)
• Indexes: idx_tablename_columns
• Foreign keys: referenced_table_id (user_id)

Normalization (YAGNI approach)

Denormalize ONLY when: measured slow query AND indexing doesn't fix it.

Indexing Strategy

✅ Index: Foreign keys, WHERE/JOIN columns, ORDER BY columns, unique constraints
❌ Skip: Low-cardinality (booleans), rarely queried, tiny tables (<1K rows)

Index Types (PostgreSQL)

-- B-tree (default)
CREATE INDEX idx_users_email ON users(email);

-- Composite (equality first, range last)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial (filtered subset)
CREATE INDEX idx_orders_active ON orders(created_at) WHERE status = 'active';

-- GIN (JSONB, arrays, full-text)
CREATE INDEX idx_products_tags ON products USING GIN(tags);

Query Optimization

EXPLAIN ANALYZE SELECT ...;

-- Red flags:
-- Seq Scan on large table → needs index
-- Nested Loop on large sets → try Hash Join
-- Rows estimated ≠ actual → run ANALYZE

Flyway Migration Rules (this project)

1. File location: backend/src/main/resources/db/migration/
2. Naming: V{next}__short_description.sql — current latest is V62, so next is V63
3. Line endings: MUST use LF (not CRLF) — Windows users: git config core.autocrlf input. CRLF causes Flyway checksum errors.
4. Immutable: Never modify an existing migration. Always add a new version.
5. Forward-only in production — no undo migrations
6. Add columns nullable first → backfill → add NOT NULL constraint (separate migrations)
7. Batch large UPDATEs — never one massive query
8. No hibernate.ddl-auto=update — Flyway is the ONLY schema management tool

Migration Template

-- V63__add_example_table.sql
-- Description: Add example table for [feature]

CREATE TABLE examples (
    id          BIGSERIAL    PRIMARY KEY,
    name        VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    deleted     BOOLEAN      NOT NULL DEFAULT FALSE,
    version     BIGINT       NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_examples_name ON examples(name);
CREATE INDEX idx_examples_deleted ON examples(deleted) WHERE deleted = FALSE;

Current Schema (V1–V62)

Auth & Users:

• users — with soft-delete, user_profiles (extended info), user_roles, user_groups
• roles, permissions, role_permissions — 37+ permissions seeded (USER_*, ROLE_*, CASE_*, CLIENT_*, LAWYER_*, FINANCIAL_*, INVOICE_*)
• groups, group_roles, group_permissions — group-based RBAC; new users auto-join "Default Users"
• audit_logs — request/response + entity trail with IP and metadata

Legal Domain:

• cases — dossier with status workflows, bilingual reference data
• case_types, case_categories, case_statuses, case_type_statuses — reference/lookup tables
• case_sequences, case_templates — numbering and templates
• case_lawyers — M:N between cases and lawyers
• lawyers — full CRUD, soft-delete
• clients — CIN uniqueness, linked to cases
• tribunals — bilingual (French/Arabic) court reference data

Financial:

• financial_transactions — ledger entries, per-case tracking
• invoices, invoice_items — full invoice lifecycle; PAID status auto-syncs to transactions

Performance Fixes (ordered by effort)

1. Add missing indexes           → minutes
2. Fix N+1 queries               → hours
3. Connection pooling (PgBouncer) → 30 min
4. Query result caching (Redis)   → hours
5. Read replicas                  → hours-days
6. Table partitioning             → days (100M+ rows)
7. Sharding                       → weeks (last resort)

Backup Strategy

Test restores monthly. Untested backup = no backup.

Handoff Points

• ← From Tech Lead: Data model requirements, performance targets
• ← From Backend Dev: Query patterns, slow query reports
• → Backend Dev: Schema, migrations, optimized queries
• → DevOps: Backup/replication requirements
• → Security Engineer: DB access model for review

Source: rhorba/LawFirm — distributed by TomeVault.