Skip to content

Complete Data Model with Flyway Migrations

Status: Final Version: 2.0 (Spring Boot Kotlin)

Purpose

Provide exact database schema, Flyway migrations, indexes, constraints, and soft delete strategy for all ESG platform entities.

Core Migrations

1. Tenants & Organizations

File: V1__create_tenants_and_organisations.sql

CREATE TABLE tenants (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    status VARCHAR(255) NOT NULL DEFAULT 'active', -- active, suspended
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE organisations (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(255) NOT NULL UNIQUE,
    consolidation_approach VARCHAR(50) NOT NULL DEFAULT 'operational_control'
        CHECK (consolidation_approach IN ('financial_control', 'operational_control', 'equity_share')),
    fiscal_year_end VARCHAR(5) NOT NULL, -- MM-DD
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    CONSTRAINT fk_organisations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE INDEX idx_organisations_tenant_id ON organisations(tenant_id, id);
CREATE UNIQUE INDEX idx_organisations_tenant_code ON organisations(tenant_id, code);

CREATE TABLE sites (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    organisation_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(255) NOT NULL,
    boundary_description TEXT,
    partial_facility BOOLEAN NOT NULL DEFAULT false,
    site_area_sqm DECIMAL(12, 2),
    included_in_reporting BOOLEAN NOT NULL DEFAULT true,
    metadata JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    CONSTRAINT fk_sites_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_sites_organisation FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE
);

CREATE INDEX idx_sites_tenant_org ON sites(tenant_id, organisation_id);
CREATE UNIQUE INDEX idx_sites_tenant_code ON sites(tenant_id, code);

2. Reporting Periods

File: V2__create_reporting_periods.sql

CREATE TABLE reporting_periods (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    organisation_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL, -- e.g., "FY2025"
    period_type VARCHAR(50) NOT NULL CHECK (period_type IN ('annual', 'quarterly', 'custom')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    fiscal_year INTEGER NOT NULL,
    is_baseline BOOLEAN NOT NULL DEFAULT false,
    state VARCHAR(50) NOT NULL DEFAULT 'OPEN' CHECK (state IN ('OPEN', 'IN_REVIEW', 'APPROVED', 'LOCKED')),
    locked_at TIMESTAMP,
    locked_by_user_id BIGINT,
    content_hash VARCHAR(64), -- SHA-256
    restatement_count INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_reporting_periods_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_reporting_periods_organisation FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE,
    CONSTRAINT fk_reporting_periods_locked_by FOREIGN KEY (locked_by_user_id) REFERENCES users(id)
);

CREATE INDEX idx_reporting_periods_tenant_org_year ON reporting_periods(tenant_id, organisation_id, fiscal_year);
CREATE INDEX idx_reporting_periods_state ON reporting_periods(state);
CREATE UNIQUE INDEX idx_reporting_periods_tenant_org_name ON reporting_periods(tenant_id, organisation_id, name);

3. Frameworks & Metrics

File: V3__create_frameworks_and_metrics.sql

CREATE TABLE frameworks (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    version VARCHAR(50) NOT NULL,
    effective_date DATE NOT NULL,
    sunset_date DATE,
    status VARCHAR(50) NOT NULL DEFAULT 'active' CHECK (status IN ('draft', 'active', 'deprecated')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_frameworks_status ON frameworks(status);

CREATE TABLE metric_definitions (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    metric_id VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    framework_id BIGINT,
    disclosure_id BIGINT,
    is_custom_kpi BOOLEAN NOT NULL DEFAULT false,
    data_type VARCHAR(50) NOT NULL CHECK (data_type IN ('numeric', 'boolean', 'text', 'date', 'enum')),
    unit VARCHAR(50),
    collection_frequency VARCHAR(50) CHECK (collection_frequency IN ('monthly', 'quarterly', 'annually', 'ad_hoc')),
    dimensionality VARCHAR(50) NOT NULL CHECK (dimensionality IN ('site', 'business_unit', 'organisation', 'project')),
    is_mandatory BOOLEAN NOT NULL DEFAULT false,
    validation_rules JSONB,
    allowed_evidence_types JSONB,
    aggregation_method VARCHAR(50) NOT NULL CHECK (aggregation_method IN ('sum', 'weighted_average', 'count', 'calculated', 'none')),
    sensitivity_classification VARCHAR(50) NOT NULL DEFAULT 'internal' CHECK (sensitivity_classification IN ('public', 'internal', 'confidential', 'pii')),
    contains_pii BOOLEAN NOT NULL DEFAULT false,
    metadata JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deprecated_at TIMESTAMP,
    CONSTRAINT fk_metric_definitions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_metric_definitions_framework FOREIGN KEY (framework_id) REFERENCES frameworks(id),
    CONSTRAINT fk_metric_definitions_disclosure FOREIGN KEY (disclosure_id) REFERENCES gri_disclosures(id)
);

CREATE INDEX idx_metric_definitions_tenant_metric ON metric_definitions(tenant_id, metric_id);
CREATE INDEX idx_metric_definitions_disclosure ON metric_definitions(disclosure_id);
CREATE INDEX idx_metric_definitions_sensitivity ON metric_definitions(sensitivity_classification);
CREATE UNIQUE INDEX idx_metric_definitions_tenant_metric_unique ON metric_definitions(tenant_id, metric_id);

4. Submissions & Evidence

File: V4__create_submissions_and_evidence.sql

CREATE TABLE metric_submissions (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    submission_uuid UUID NOT NULL UNIQUE,
    reporting_period_id BIGINT NOT NULL,
    site_id BIGINT NOT NULL,
    metric_definition_id BIGINT NOT NULL,
    raw_data JSONB NOT NULL, -- Submitted data
    processed_data JSONB, -- Normalized data
    state VARCHAR(50) NOT NULL DEFAULT 'RECEIVED' CHECK (state IN ('RECEIVED', 'VALIDATED', 'REJECTED', 'PROCESSED', 'APPROVED', 'LOCKED')),
    submitted_by_user_id BIGINT NOT NULL,
    submitted_at TIMESTAMP NOT NULL,
    approved_by_user_id BIGINT,
    approved_at TIMESTAMP,
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_metric_submissions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_metric_submissions_period FOREIGN KEY (reporting_period_id) REFERENCES reporting_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_metric_submissions_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    CONSTRAINT fk_metric_submissions_metric FOREIGN KEY (metric_definition_id) REFERENCES metric_definitions(id) ON DELETE CASCADE,
    CONSTRAINT fk_metric_submissions_submitted_by FOREIGN KEY (submitted_by_user_id) REFERENCES users(id),
    CONSTRAINT fk_metric_submissions_approved_by FOREIGN KEY (approved_by_user_id) REFERENCES users(id)
);

CREATE INDEX idx_metric_submissions_tenant_period_state ON metric_submissions(tenant_id, reporting_period_id, state);
CREATE INDEX idx_metric_submissions_tenant_uuid ON metric_submissions(tenant_id, submission_uuid);
CREATE UNIQUE INDEX idx_metric_submissions_tenant_uuid_unique ON metric_submissions(tenant_id, submission_uuid);

CREATE TABLE evidence (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    filepath VARCHAR(500) NOT NULL,
    file_size BIGINT NOT NULL,
    mime_type VARCHAR(255) NOT NULL,
    content_hash VARCHAR(64) NOT NULL, -- SHA-256
    evidence_type VARCHAR(100) NOT NULL,
    description TEXT,
    uploaded_by_user_id BIGINT NOT NULL,
    uploaded_from_ip VARCHAR(45) NOT NULL,
    uploaded_at TIMESTAMP NOT NULL,
    legal_hold BOOLEAN NOT NULL DEFAULT false,
    retention_until DATE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_evidence_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_evidence_uploaded_by FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id)
);

CREATE INDEX idx_evidence_tenant_type ON evidence(tenant_id, evidence_type);
CREATE INDEX idx_evidence_content_hash ON evidence(content_hash);

CREATE TABLE metric_evidence (
    id BIGSERIAL PRIMARY KEY,
    metric_submission_id BIGINT NOT NULL,
    evidence_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_metric_evidence_submission FOREIGN KEY (metric_submission_id) REFERENCES metric_submissions(id) ON DELETE CASCADE,
    CONSTRAINT fk_metric_evidence_evidence FOREIGN KEY (evidence_id) REFERENCES evidence(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_metric_evidence_submission_evidence ON metric_evidence(metric_submission_id, evidence_id);

5. Stakeholder Engagement & Grievances

File: V5__create_stakeholder_engagement.sql

-- Stakeholder Categories (Master List)
CREATE TABLE stakeholder_categories (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_stakeholder_categories_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE INDEX idx_stakeholder_categories_tenant ON stakeholder_categories(tenant_id);
CREATE UNIQUE INDEX idx_stakeholder_categories_tenant_name ON stakeholder_categories(tenant_id, name);

-- Engagement Platforms (Master List)
CREATE TABLE engagement_platforms (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_engagement_platforms_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE INDEX idx_engagement_platforms_tenant ON engagement_platforms(tenant_id);
CREATE UNIQUE INDEX idx_engagement_platforms_tenant_name ON engagement_platforms(tenant_id, name);

-- Stakeholder Engagements
CREATE TABLE stakeholder_engagements (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    reporting_period_id BIGINT NOT NULL,
    organisation_id BIGINT NOT NULL,
    site_id BIGINT,
    initial_date DATE NOT NULL,
    stakeholder_name VARCHAR(500) NOT NULL, -- Group/organisation name, not individuals
    purpose TEXT NOT NULL,
    outcome TEXT,
    owner VARCHAR(255),
    status VARCHAR(50) NOT NULL DEFAULT 'Open' CHECK (status IN ('Open', 'WIP', 'Closed')),
    status_date DATE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_stakeholder_engagements_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_stakeholder_engagements_period FOREIGN KEY (reporting_period_id) REFERENCES reporting_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_stakeholder_engagements_organisation FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE,
    CONSTRAINT fk_stakeholder_engagements_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE SET NULL
);

CREATE INDEX idx_stakeholder_engagements_tenant_period ON stakeholder_engagements(tenant_id, reporting_period_id);
CREATE INDEX idx_stakeholder_engagements_status ON stakeholder_engagements(status);
CREATE INDEX idx_stakeholder_engagements_organisation ON stakeholder_engagements(organisation_id);

-- Junction table: Engagements to Categories (many-to-many)
CREATE TABLE engagement_categories (
    engagement_id BIGINT NOT NULL,
    category_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (engagement_id, category_id),
    CONSTRAINT fk_engagement_categories_engagement FOREIGN KEY (engagement_id) REFERENCES stakeholder_engagements(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_categories_category FOREIGN KEY (category_id) REFERENCES stakeholder_categories(id) ON DELETE CASCADE
);

CREATE INDEX idx_engagement_categories_category ON engagement_categories(category_id);

-- Junction table: Engagements to Platforms (many-to-many)
CREATE TABLE engagement_platforms_used (
    engagement_id BIGINT NOT NULL,
    platform_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (engagement_id, platform_id),
    CONSTRAINT fk_engagement_platforms_used_engagement FOREIGN KEY (engagement_id) REFERENCES stakeholder_engagements(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_platforms_used_platform FOREIGN KEY (platform_id) REFERENCES engagement_platforms(id) ON DELETE CASCADE
);

CREATE INDEX idx_engagement_platforms_used_platform ON engagement_platforms_used(platform_id);

-- Grievances
CREATE TABLE grievances (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    reporting_period_id BIGINT NOT NULL,
    organisation_id BIGINT NOT NULL,
    site_id BIGINT,
    date_reported DATE NOT NULL,
    is_internal BOOLEAN NOT NULL DEFAULT false,
    stakeholder_group VARCHAR(255) NOT NULL, -- General group, NOT individual names (PII protection)
    nature TEXT NOT NULL,
    intervention TEXT,
    resolution_status VARCHAR(50) NOT NULL DEFAULT 'Open' CHECK (resolution_status IN ('Open', 'WIP', 'Closed')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_grievances_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_grievances_period FOREIGN KEY (reporting_period_id) REFERENCES reporting_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_grievances_organisation FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE,
    CONSTRAINT fk_grievances_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE SET NULL
);

CREATE INDEX idx_grievances_tenant_period ON grievances(tenant_id, reporting_period_id);
CREATE INDEX idx_grievances_status ON grievances(resolution_status);
CREATE INDEX idx_grievances_organisation ON grievances(organisation_id);
CREATE INDEX idx_grievances_internal ON grievances(is_internal);

-- Evidence linkages for stakeholder engagements
CREATE TABLE engagement_evidence (
    id BIGSERIAL PRIMARY KEY,
    engagement_id BIGINT NOT NULL,
    evidence_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_engagement_evidence_engagement FOREIGN KEY (engagement_id) REFERENCES stakeholder_engagements(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_evidence_evidence FOREIGN KEY (evidence_id) REFERENCES evidence(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_engagement_evidence_unique ON engagement_evidence(engagement_id, evidence_id);

-- Evidence linkages for grievances
CREATE TABLE grievance_evidence (
    id BIGSERIAL PRIMARY KEY,
    grievance_id BIGINT NOT NULL,
    evidence_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_grievance_evidence_grievance FOREIGN KEY (grievance_id) REFERENCES grievances(id) ON DELETE CASCADE,
    CONSTRAINT fk_grievance_evidence_evidence FOREIGN KEY (evidence_id) REFERENCES evidence(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_grievance_evidence_unique ON grievance_evidence(grievance_id, evidence_id);

6. Community Investment & Development

File: V6__create_community_investment.sql

CREATE TABLE community_investment_activities (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    reporting_period_id BIGINT NOT NULL,
    organisation_id BIGINT NOT NULL,
    site_id BIGINT,
    description TEXT NOT NULL,
    pillar VARCHAR(255) NOT NULL CHECK (pillar IN ('Education & Sports', 'Social Empowerment', 'Community Development', 'Donations & Sponsorship', 'Other')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL, -- Can be same as start_date for single-day events
    budget DECIMAL(19, 2),
    actual DECIMAL(19, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL, -- ISO 4217 code
    beneficiaries TEXT,
    state VARCHAR(50) NOT NULL DEFAULT 'RECEIVED' CHECK (state IN ('RECEIVED', 'APPROVED', 'REJECTED')),
    reviewed_by_user_id BIGINT,
    reviewed_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ci_activities_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_ci_activities_period FOREIGN KEY (reporting_period_id) REFERENCES reporting_periods(id) ON DELETE CASCADE,
    CONSTRAINT fk_ci_activities_organisation FOREIGN KEY (organisation_id) REFERENCES organisations(id) ON DELETE CASCADE,
    CONSTRAINT fk_ci_activities_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE SET NULL,
    CONSTRAINT fk_ci_activities_reviewer FOREIGN KEY (reviewed_by_user_id) REFERENCES users(id)
);

CREATE INDEX idx_ci_activities_tenant_period ON community_investment_activities(tenant_id, reporting_period_id);
CREATE INDEX idx_ci_activities_pillar ON community_investment_activities(pillar);
CREATE INDEX idx_ci_activities_dates ON community_investment_activities(start_date, end_date);
CREATE INDEX idx_ci_activities_state ON community_investment_activities(state);

-- Evidence linkages for community investment activities
CREATE TABLE community_investment_evidence (
    id BIGSERIAL PRIMARY KEY,
    activity_id BIGINT NOT NULL,
    evidence_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ci_evidence_activity FOREIGN KEY (activity_id) REFERENCES community_investment_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_ci_evidence_evidence FOREIGN KEY (evidence_id) REFERENCES evidence(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_ci_evidence_unique ON community_investment_evidence(activity_id, evidence_id);

7. Audit Logs (Immutable)

File: V7__create_audit_logs.sql

CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    actor_user_id BIGINT,
    action VARCHAR(255) NOT NULL, -- e.g., 'submission.approved', 'period.locked'
    entity_type VARCHAR(255) NOT NULL,
    entity_id BIGINT NOT NULL,
    before_state JSONB,
    after_state JSONB,
    justification TEXT,
    ip_address VARCHAR(45) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- No updated_at (immutable)
    CONSTRAINT fk_audit_logs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_audit_logs_actor FOREIGN KEY (actor_user_id) REFERENCES users(id)
);

CREATE INDEX idx_audit_logs_tenant_created ON audit_logs(tenant_id, created_at);
CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);

Index Strategy

Table Index Justification Query Pattern
metric_submissions (tenant_id, reporting_period_id, state) Review queue queries WHERE tenant_id = ? AND reporting_period_id = ? AND state = 'VALIDATED'
metric_submissions (tenant_id, submission_uuid) Idempotency checks WHERE tenant_id = ? AND submission_uuid = ?
evidence content_hash Duplicate detection WHERE content_hash = ?
audit_logs (tenant_id, created_at) Audit log retrieval WHERE tenant_id = ? AND created_at >= ?
reporting_periods state Dashboard queries WHERE state = 'OPEN'
stakeholder_engagements (tenant_id, reporting_period_id) Engagement report queries WHERE tenant_id = ? AND reporting_period_id = ?
stakeholder_engagements status Filter by engagement status WHERE status = 'Open'
grievances (tenant_id, reporting_period_id) Grievance report queries WHERE tenant_id = ? AND reporting_period_id = ?
grievances resolution_status Filter by resolution status WHERE resolution_status = 'Open'
grievances is_internal Filter internal/external WHERE is_internal = true
community_investment_activities (tenant_id, reporting_period_id) Report generation WHERE tenant_id = ? AND reporting_period_id = ?

Unique Constraints

Table Constraint Purpose
metric_submissions (tenant_id, submission_uuid) Idempotency enforcement
organisations (tenant_id, code) Unique org codes per tenant
sites (tenant_id, code) Unique site codes per tenant
metric_definitions (tenant_id, metric_id) Unique metric IDs per tenant
stakeholder_categories (tenant_id, name) Unique category names per tenant
engagement_platforms (tenant_id, name) Unique platform names per tenant
engagement_evidence (engagement_id, evidence_id) Prevent duplicate evidence links
grievance_evidence (grievance_id, evidence_id) Prevent duplicate evidence links
community_investment_evidence (activity_id, evidence_id) Prevent duplicate evidence links

Soft Delete Strategy

Table Soft Delete Rationale
organisations, sites, users ✅ Yes Preserve historical references
metric_submissions, evidence ❌ No Immutable, never delete
audit_logs ❌ No Regulatory requirement (immutable)
reporting_periods ❌ No Never delete periods
stakeholder_engagements, grievances, community_investment_activities ❌ No Immutable audit trail for GRI compliance
stakeholder_categories, engagement_platforms ✅ Yes (is_active flag) Soft deactivation for master lists

Human Capital Dimensions (GRI 405-1, GRI 401)

Overview

Human capital metrics (employee demographics, employment type, turnover) require dimensional disaggregation by gender, employment level, employment type, age group, and local community status. These dimensions are stored in the raw_data and processed_data JSONB fields of metric_submissions.

Dimension Definitions

Employment Level

Purpose: Categorize employees by hierarchical level for GRI 405-1 diversity reporting.

Allowed Values: - Executive - C-suite and executive leadership - Salaried Staff (Non-NEC) - Salaried employees not covered by National Employment Conditions - Waged Staff (NEC) - Waged employees covered by National Employment Conditions

Validation: - Enum constraint (must match one of the allowed values) - Required for all GRI 405-1 headcount metrics

Gender

Purpose: Gender disaggregation for diversity and equal opportunity reporting (GRI 405-1, GRI 401 recruitment).

Allowed Values: - Male - Female

Validation: - Enum constraint (must match one of the allowed values) - Required for GRI 405-1 and GRI 401 recruitment metrics - Sum of Male + Female must equal Total headcount

PII Considerations: - Store aggregated counts only, not individual employee gender - Do not store non-binary/other gender data in v1 (GRI 405-1 standard limitation) - Apply minimum aggregation threshold of 5 employees to prevent individual identification

Local Community Status

Purpose: Track employees hired from local community (GRI 405-1, stakeholder engagement).

Allowed Values: - Yes - Employee is from local community - No - Employee is not from local community

Definition: "Local community" defined as residing within 50km radius of site or as defined by tenant policy.

Validation: - Boolean/enum constraint - Local community count must be ≤ Total headcount - Can overlay with gender dimensions (not mutually exclusive)

PII Considerations: - Store aggregated counts only - Do not store individual employee community status flags

Employment Type

Purpose: Categorize employees by contract type for GRI 401 employment reporting.

Allowed Values: - Permanent - Permanent contract employees - Fixed Term - Fixed-term contract employees - Casual - Casual/daily workers (no formal contract)

Validation: - Enum constraint (must match one of the allowed values) - Required for GRI 401 employment type metrics

Age Group

Purpose: Age distribution reporting for GRI 405-1 compliance.

Allowed Values: - Under 30 - Employees under 30 years old - Aged 30-50 - Employees aged 30-50 years - Over 50 - Employees over 50 years old

Validation: - Enum constraint (must match one of the allowed values) - Sum of all age groups must equal Total headcount - Monthly totals must be validated for consistency

PII Considerations: - Store only age group counts, not individual ages or birthdates - Age groups sufficiently broad to prevent identification

JSONB Schema for Dimensional Submissions

GRI 405-1 Headcount Submission (Quarterly)

{
  "metric_id": "GRI_405_1_EXECUTIVE_HEADCOUNT",
  "reporting_period_quarter": "Q1",
  "reporting_period_year": 2025,
  "dimensions": {
    "employment_level": "Executive",
    "gender": {
      "Male": 7,
      "Female": 0
    },
    "local_community": {
      "Yes": 0,
      "No": 7
    }
  },
  "totals": {
    "total_headcount": 7,
    "from_local_community": 0
  },
  "collection_date": "2025-03-31",
  "notes": "End of Q1 snapshot"
}

Storage Location: metric_submissions.raw_data (as submitted by collector)

Validation Rules: - dimensions.gender.Male + dimensions.gender.Female = totals.total_headcount - dimensions.local_community.Yes = totals.from_local_community - totals.from_local_community <= totals.total_headcount - All counts must be non-negative integers

GRI 401 Employment Type Submission (Monthly)

{
  "metric_id": "GRI_401_1_EMPLOYMENT_TYPE",
  "reporting_period_month": "January",
  "reporting_period_year": 2025,
  "dimensions": {
    "employment_type": {
      "Permanent": 417,
      "Fixed Term": 204
    }
  },
  "totals": {
    "total_employees": 621
  },
  "collection_date": "2025-01-31",
  "notes": "End of month snapshot"
}

GRI 401 Recruitment by Gender Submission (Monthly)

{
  "metric_id": "GRI_401_1_NEW_HIRES_BY_GENDER",
  "reporting_period_month": "April",
  "reporting_period_year": 2025,
  "dimensions": {
    "gender": {
      "Male": 12,
      "Female": 3
    },
    "employment_type": "Permanent"
  },
  "totals": {
    "total_new_hires": 15
  },
  "collection_date": "2025-04-30",
  "notes": "New permanent staff hires in April"
}

Validation Rules: - dimensions.gender.Male + dimensions.gender.Female = totals.total_new_hires - All counts must be non-negative integers - New hires count should not exceed total employees for the period

Processed Data Schema

After validation, the processed_data JSONB field stores normalized dimensions for reporting:

{
  "metric_id": "GRI_405_1_EXECUTIVE_HEADCOUNT",
  "quarter": "Q1",
  "fiscal_year": 2025,
  "employment_level": "Executive",
  "male_count": 7,
  "female_count": 0,
  "total_count": 7,
  "local_community_count": 0,
  "percent_male": 100.0,
  "percent_female": 0.0,
  "percent_local_community": 0.0,
  "validation_status": "PASSED",
  "processing_timestamp": "2025-04-05T10:30:00Z"
}

Querying Dimensional Data

Example: Retrieve all Executive headcount for Q1 2025

SELECT
  ms.id,
  ms.submission_uuid,
  ms.processed_data->>'employment_level' AS employment_level,
  (ms.processed_data->>'male_count')::INTEGER AS male_count,
  (ms.processed_data->>'female_count')::INTEGER AS female_count,
  (ms.processed_data->>'total_count')::INTEGER AS total_count,
  (ms.processed_data->>'local_community_count')::INTEGER AS local_community_count,
  (ms.processed_data->>'percent_male')::NUMERIC AS percent_male,
  (ms.processed_data->>'percent_female')::NUMERIC AS percent_female,
  (ms.processed_data->>'percent_local_community')::NUMERIC AS percent_local_community
FROM metric_submissions ms
JOIN metric_definitions md ON ms.metric_definition_id = md.id
WHERE md.metric_id = 'GRI_405_1_EXECUTIVE_HEADCOUNT'
  AND ms.processed_data->>'quarter' = 'Q1'
  AND ms.processed_data->>'fiscal_year' = '2025'
  AND ms.state = 'APPROVED';

Example: Aggregate site-level data to organisation-level

SELECT
  rp.fiscal_year,
  ms.processed_data->>'quarter' AS quarter,
  ms.processed_data->>'employment_level' AS employment_level,
  SUM((ms.processed_data->>'male_count')::INTEGER) AS total_male,
  SUM((ms.processed_data->>'female_count')::INTEGER) AS total_female,
  SUM((ms.processed_data->>'total_count')::INTEGER) AS total_headcount,
  SUM((ms.processed_data->>'local_community_count')::INTEGER) AS total_local_community,
  ROUND(
    100.0 * SUM((ms.processed_data->>'male_count')::INTEGER) /
    NULLIF(SUM((ms.processed_data->>'total_count')::INTEGER), 0),
    2
  ) AS percent_male,
  ROUND(
    100.0 * SUM((ms.processed_data->>'female_count')::INTEGER) /
    NULLIF(SUM((ms.processed_data->>'total_count')::INTEGER), 0),
    2
  ) AS percent_female
FROM metric_submissions ms
JOIN metric_definitions md ON ms.metric_definition_id = md.id
JOIN reporting_periods rp ON ms.reporting_period_id = rp.id
WHERE md.metric_id LIKE 'GRI_405_1_%_HEADCOUNT'
  AND rp.fiscal_year = 2025
  AND ms.state = 'APPROVED'
GROUP BY rp.fiscal_year, ms.processed_data->>'quarter', ms.processed_data->>'employment_level'
ORDER BY quarter, employment_level;

Relationships for HR Metrics

Human capital metric submissions follow the standard metric_submissions table structure with relationships to:

  1. Organisation (organisation_id, required):
  2. All GRI 405-1 and GRI 401 metrics collected at organisation level
  3. Aggregates from site-level data where applicable (Salaried/Waged staff)

  4. Executive headcount is organisation-level only (no site breakdown)

  5. Site (site_id, optional):

  6. Used for site-specific HR metrics (e.g., Salaried Staff, Waged Staff demographics)
  7. Executive metrics do not use site_id (organisation-level only)

  8. Aggregation: Multiple site submissions for same metric + period = organisation total

  9. Reporting Period (reporting_period_id, required):

  10. GRI 405-1 demographics: Quarterly periods (Q1, Q2, Q3, Q4)
  11. GRI 401 employment type/turnover: Monthly periods (Jan-Dec)

  12. Period determines collection frequency and aggregation window

  13. Evidence (via metric_evidence pivot table):

  14. Required evidence types for HR metrics:
    • HR_REGISTER: Primary evidence for all headcount metrics
    • PAYROLL_REPORT: Alternative/supporting evidence for employee counts
    • RECRUITMENT_REPORT: Required for new hire metrics
    • EXIT_REPORT: Required for departure metrics
    • BOARD_REPORT: For executive-level headcount
  15. Minimum 1 evidence file required per submission (enforced by validation rules)

  16. Evidence files stored separately with checksum verification

  17. Metric Definition (metric_definition_id, required):

  18. Links to metric catalog entry defining collection rules, dimensions, and validation
  19. Example metric_ids: GRI_405_1_EXECUTIVE_HEADCOUNT, GRI_401_PERMANENT_EMPLOYEES_MONTHLY
  20. Metric definition specifies which dimensions are required in raw_data

Relationship Constraints: - HR metrics must have exactly one organisation_id (never null) - HR metrics may have zero or one site_id (null for org-level metrics like Executive headcount) - HR metrics must have exactly one reporting_period_id matching collection frequency - HR metrics must have at least one evidence link (enforced at validation stage)

Query Pattern for HR Metrics: 

-- Retrieve all GRI 405-1 demographics for organisation X, Q1 2025
SELECT
  ms.id,
  md.metric_id,
  o.name AS organisation_name,
  s.name AS site_name,
  rp.period_start,
  rp.period_end,
  ms.processed_data,
  COUNT(me.evidence_id) AS evidence_count
FROM metric_submissions ms
JOIN metric_definitions md ON ms.metric_definition_id = md.id
JOIN organisations o ON ms.organisation_id = o.id
LEFT JOIN sites s ON ms.site_id = s.id
JOIN reporting_periods rp ON ms.reporting_period_id = rp.id
LEFT JOIN metric_evidence me ON ms.id = me.metric_submission_id
WHERE md.metric_id LIKE 'GRI_405_1_%'
  AND o.id = :organisationId
  AND rp.period_type = 'QUARTERLY'
  AND rp.period_start >= '2025-01-01'
  AND rp.period_end <= '2025-03-31'
  AND ms.state = 'APPROVED'
GROUP BY ms.id, md.metric_id, o.name, s.name, rp.period_start, rp.period_end, ms.processed_data;

Implementation Notes

  1. Dimension Validation: Implemented in ValidationService using rules from metric_definitions.validation_rules
  2. Data Transformation: Raw dimension data transformed to processed format by MetricProcessingService
  3. PII Protection: Only aggregated counts stored; no individual employee records in metric submissions
  4. Percentage Calculations: Derived at report generation time from counts, not stored separately
  5. Aggregation: Site-level dimensional data aggregates to organisation-level using SUM for counts

Acceptance Criteria

  • All migrations create tables with exact schema
  • Indexes match query patterns from acceptance criteria
  • Unique constraints enforce idempotency and business rules
  • Soft delete only on user-facing entities (orgs, sites, users)
  • Audit logs table has NO updated_at column (immutable)
  • Foreign key cascades configured (CASCADE where appropriate)

Cross-References

Change Log

Version Date Author Changes
1.3 2026-01-20 Ralph Agent (TASK-H-003) Added Community Investment activities and evidence entities
1.2 2026-01-17 Ralph Agent (TASK-004) Added Relationships for HR Metrics section documenting foreign key relationships to organisation, site, reporting period, and evidence with query patterns
1.1 2026-01-17 Ralph Agent (TASK-002) Added Human Capital Dimensions section with JSONB schemas for GRI 405-1 and GRI 401 dimensional data storage
1.0 2026-01-03 Senior Product Architect Complete data model with migrations