Skip to main content

Database

The API uses PostgreSQL 16 with Flyway for schema migrations. All migrations live in src/main/resources/db/migration/ and run automatically on startup.

Connection​

SettingDevelopment default
Hostlocalhost
Port5432
Databaseassociationapp
Userassociationapp
Passwordassociationapp

Configure via environment variables:

DB_HOST=localhost
DB_NAME=associationapp
DB_USER=associationapp
DB_PASSWORD=your-password

Migration History​

33 migrations (V1–V33) build the full schema incrementally.

MigrationWhat it creates
V1System roles & authorities
V2Users table (consolidated)
V3User sessions, password reset tokens, email verification, activity log
V4Organisations table
V5Organisation roles & authorities
V6Organisation users & invitations
V7Organisation branding & assets
V8Organisation email templates & email log
V9Membership types & membership lifecycle
V10Events, event registrations, event sessions
V11Payments & invoices
V12News articles & comments
V13Documents & notifications
V14Performance indexes
V15Default super admin account
V16Default system authorities
V17Event responses
V18–V19Document folders
V20Member directory configuration
V21Organisation messages
V22System settings (encrypted)
V23Connectors, sync jobs, field mappings
V24–V26Organisation storage configuration
V27–V29Platform plans, subscriptions, platform invoices
V30Additional indexes
V31Organisation legal pages
V32–V33Integration hub categories & Microsoft Dynamics config

Key Tables​

Users​

user
  id                    UUID PRIMARY KEY
  email                 VARCHAR UNIQUE NOT NULL
  username              VARCHAR UNIQUE
  password_hash         VARCHAR NOT NULL
  first_name            VARCHAR
  last_name             VARCHAR
  phone                 VARCHAR
  date_of_birth         DATE
  job_title             VARCHAR
  company               VARCHAR
  -- address fields
  avatar_url            VARCHAR
  timezone              VARCHAR
  locale                VARCHAR
  account_status        ENUM (PENDING_VERIFICATION, ACTIVE, SUSPENDED, DEACTIVATED, BANNED)
  email_verified        BOOLEAN DEFAULT FALSE
  mfa_enabled           BOOLEAN DEFAULT FALSE
  last_login_at         TIMESTAMP
  system_role_id        UUID REFERENCES system_role
  default_organization_id  UUID REFERENCES organization
  metadata              JSONB
  created_at            TIMESTAMP
  updated_at            TIMESTAMP
  deleted_at            TIMESTAMP   -- soft delete

Organisations​

organization
  id                    UUID PRIMARY KEY
  name                  VARCHAR NOT NULL
  slug                  VARCHAR UNIQUE NOT NULL      -- URL slug
  subdomain             VARCHAR UNIQUE               -- custom subdomain
  description           TEXT
  logo_url              VARCHAR
  website_url           VARCHAR
  email                 VARCHAR
  phone                 VARCHAR
  -- address fields
  org_type              VARCHAR
  status                ENUM (ACTIVE, SUSPENDED, INACTIVE)
  allow_join_requests   BOOLEAN DEFAULT FALSE
  join_request_message  TEXT
  settings              JSONB
  created_by_user_id    UUID REFERENCES user
  created_at            TIMESTAMP
  updated_at            TIMESTAMP
  deleted_at            TIMESTAMP

Organisation Users (Membership)​

organization_user
  id                    UUID PRIMARY KEY
  organization_id       UUID REFERENCES organization
  user_id               UUID REFERENCES user
  status                ENUM (PENDING, ACTIVE, SUSPENDED, INACTIVE, REJECTED)
  joined_at             TIMESTAMP
  created_at            TIMESTAMP
  updated_at            TIMESTAMP

Events​

event
  id                    UUID PRIMARY KEY
  organization_id       UUID REFERENCES organization
  title                 VARCHAR NOT NULL
  slug                  VARCHAR
  description           TEXT
  event_type            VARCHAR        -- IN_PERSON, VIRTUAL, HYBRID
  category              VARCHAR
  start_date            TIMESTAMP
  end_date              TIMESTAMP
  location_type         VARCHAR
  venue_name            VARCHAR
  -- address fields
  registration_required BOOLEAN
  max_attendees         INTEGER
  waiting_list_enabled  BOOLEAN
  is_free               BOOLEAN
  base_price            DECIMAL
  currency              VARCHAR
  member_price          DECIMAL
  early_bird_price      DECIMAL
  cover_image_url       VARCHAR
  status                ENUM (DRAFT, PUBLISHED, CANCELLED, COMPLETED)
  created_at            TIMESTAMP
  updated_at            TIMESTAMP
  deleted_at            TIMESTAMP

Event Registrations​

event_registration
  id                    UUID PRIMARY KEY
  event_id              UUID REFERENCES event
  user_id               UUID REFERENCES user
  organization_id       UUID REFERENCES organization
  status                ENUM (PENDING, CONFIRMED, WAITLISTED, CANCELLED)
  registered_at         TIMESTAMP
  cancelled_at          TIMESTAMP
  created_at            TIMESTAMP
  updated_at            TIMESTAMP

News Articles​

news_article
  id                    UUID PRIMARY KEY
  organization_id       UUID REFERENCES organization
  title                 VARCHAR NOT NULL
  slug                  VARCHAR
  content               TEXT
  excerpt               TEXT
  cover_image_url       VARCHAR
  author_id             UUID REFERENCES user
  status                ENUM (DRAFT, PUBLISHED, ARCHIVED)
  members_only          BOOLEAN
  published_at          TIMESTAMP
  scheduled_at          TIMESTAMP
  view_count            INTEGER DEFAULT 0
  tags                  JSONB
  created_at            TIMESTAMP
  updated_at            TIMESTAMP
  deleted_at            TIMESTAMP

Documents​

document
  id                    UUID PRIMARY KEY
  organization_id       UUID REFERENCES organization
  folder_id             UUID REFERENCES document_folder
  name                  VARCHAR NOT NULL
  file_url              VARCHAR
  file_size             BIGINT
  mime_type             VARCHAR
  access               ENUM (PUBLIC, MEMBERS_ONLY, PRIVATE)
  uploaded_by           UUID REFERENCES user
  created_at            TIMESTAMP
  updated_at            TIMESTAMP
  deleted_at            TIMESTAMP

Payments​

payment
  id                    UUID PRIMARY KEY
  organization_id       UUID REFERENCES organization
  user_id               UUID REFERENCES user
  amount                DECIMAL NOT NULL
  currency              VARCHAR
  status                ENUM (PENDING, COMPLETED, FAILED, REFUNDED)
  reference_type        VARCHAR      -- EVENT, MEMBERSHIP, etc.
  reference_id          UUID
  created_at            TIMESTAMP
  updated_at            TIMESTAMP

Common Patterns​

Soft Deletes​

All major entities use soft deletes. Records are never physically removed — a deleted_at timestamp is set instead. Queries automatically filter out deleted records via Spring Data JPA annotations.

Multi-Tenancy​

Every table that contains organisation data has an organization_id foreign key. Application-layer code automatically injects the organizationId from the JWT claims into all queries.

UUIDs​

All primary keys are UUIDs generated by the application (not the database). This is safe for distributed systems and prevents sequential ID enumeration.

Audit Timestamps​

Every table includes created_at and updated_at timestamps, automatically managed by Hibernate.

Email Log Retention​

Email logs are retained for 90 days for audit trail and deliverability debugging.

Accessing the Database​

# Via Docker
docker-compose exec postgres psql -U associationapp -d associationapp

# Via psql (native)
psql -h localhost -U associationapp -d associationapp

# Useful queries
\dt                            -- list all tables
\d user                        -- describe user table
SELECT count(*) FROM event;    -- count events