Database Guide — Membership One

Overview

Membership One uses PostgreSQL 18 with Flyway for schema migrations. The database schema follows a multi-tenant model where all business tables contain an entity_id foreign key referencing the organization table.

Connection Details

Note: Local port is 5433 (not 5432) to avoid conflict with the Cash360 testdb running on 5432.

Schema Overview

Phase 0 — Seed Tables

Phase 1 — Core Business Tables

Entity-Relationship Diagram

Flyway Migrations

Migration Naming Convention

V{PPP}__{description}.sql

• PPP = Phase-based prefix (3 digits)
• Phase 0: V000-V099
• Phase 1: V100-V199
• Phase 2: V200-V299

Current Migrations

Migration Location

Each module has its own migration directory:

membership-{module}/src/main/resources/db/migration/V{nnn}__{name}.sql

All migrations are discovered via classpath scanning by Flyway.

Patterns

JSONB Columns

Used for extensible data. JPA mapping:

@JdbcTypeCode(SqlTypes.JSON)
@Column(columnDefinition = "jsonb")
@Builder.Default
private String settings = "{}";

SQL DDL:

settings JSONB DEFAULT '{}'::jsonb

Sequences

Every table uses a dedicated sequence with allocation size 50:

CREATE SEQUENCE IF NOT EXISTS member_seq START WITH 1 INCREMENT BY 50;

JPA entity:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "member_gen")
@SequenceGenerator(name = "member_gen", sequenceName = "member_seq", allocationSize = 50)
private Long id;

Note: BaseEntity uses GenerationType.SEQUENCE without explicit generator — Hibernate auto-detects the sequence name from the table name.

Optimistic Locking

Every entity has @Version via BaseEntity:

@Version
private Long version;

DDL includes:

version BIGINT NOT NULL DEFAULT 0

Audit Columns

Every table includes:

created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

Managed by JPA auditing (@CreatedDate, @LastModifiedDate in BaseEntity).

Foreign Keys and Indexes

• FK naming: fk_{table}_{reference} (e.g., fk_contract_member)
• Index naming: idx_{table}_{columns} (e.g., idx_member_entity_status)
• Unique constraint naming: uq_{table}_{columns}
• PK naming: pk_{table}

Multi-Tenant Data Isolation

All business tables contain entity_id BIGINT NOT NULL with FK to organization(id).

Application-level enforcement: - JWT token contains tenantId (= entityId) - TenantContext (ThreadLocal) stores current tenant per request - All repository queries MUST include entityId filter - Service layer validates entityId on every read/write operation

There is no database-level row-level security (RLS) yet. Tenant isolation is enforced purely at the application layer.

Docker Setup

cd backend
docker compose up -d          # Start PostgreSQL, Redis, RabbitMQ
docker compose down           # Stop and remove containers
docker compose down -v        # Also remove volumes (reset data)

Flyway runs automatically on application startup.

Useful Queries

-- Count rows per table
SELECT relname, reltuples::int FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY reltuples DESC;

-- Check migration status
SELECT * FROM flyway_schema_history ORDER BY installed_rank;

-- List all indexes
SELECT tablename, indexname FROM pg_indexes
WHERE schemaname = 'public' ORDER BY tablename;

-- Verify foreign keys
SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint WHERE contype = 'f';