Complete Data Model with Laravel Migrations

Status: Final Version: 1.0

---

Purpose

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

---

Core Migrations

1. Tenants & Organizations

Schema::create('tenants', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('slug')->unique();
    $table->string('status')->default('active'); // active, suspended
    $table->timestamps();
});

Schema::create('organisations', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->onDelete('cascade');
    $table->string('name');
    $table->string('code')->unique();
    $table->enum('consolidation_approach', ['financial_control', 'operational_control', 'equity_share'])->default('operational_control');
    $table->string('fiscal_year_end', 5); // MM-DD
    $table->timestamps();
    $table->softDeletes();

    $table->index(['tenant_id', 'id']);
    $table->unique(['tenant_id', 'code']);
});

Schema::create('sites', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->onDelete('cascade');
    $table->foreignId('organisation_id')->constrained()->onDelete('cascade');
    $table->string('name');
    $table->string('code');
    $table->text('boundary_description')->nullable();
    $table->boolean('partial_facility')->default(false);
    $table->decimal('site_area_sqm', 12, 2)->nullable();
    $table->boolean('included_in_reporting')->default(true);
    $table->jsonb('metadata')->nullable();
    $table->timestamps();
    $table->softDeletes();

    $table->index(['tenant_id', 'organisation_id']);
    $table->unique(['tenant_id', 'code']);
});

2. Reporting Periods

Schema::create('reporting_periods', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->onDelete('cascade');
    $table->foreignId('organisation_id')->constrained()->onDelete('cascade');
    $table->string('name'); // e.g., "FY2025"
    $table->enum('period_type', ['annual', 'quarterly', 'custom']);
    $table->date('start_date');
    $table->date('end_date');
    $table->integer('fiscal_year');
    $table->boolean('is_baseline')->default(false);
    $table->enum('state', ['OPEN', 'IN_REVIEW', 'APPROVED', 'LOCKED'])->default('OPEN');
    $table->timestamp('locked_at')->nullable();
    $table->foreignId('locked_by_user_id')->nullable()->constrained('users');
    $table->string('content_hash', 64)->nullable(); // SHA-256
    $table->integer('restatement_count')->default(0);
    $table->timestamps();

    $table->index(['tenant_id', 'organisation_id', 'fiscal_year']);
    $table->index('state');
    $table->unique(['tenant_id', 'organisation_id', 'name']);
});

3. Frameworks & Metrics

Schema::create('frameworks', function (Blueprint $table) {
    $table->id();
    $table->string('code', 50)->unique();
    $table->string('name');
    $table->string('version', 50);
    $table->date('effective_date');
    $table->date('sunset_date')->nullable();
    $table->enum('status', ['draft', 'active', 'deprecated'])->default('active');
    $table->timestamps();

    $table->index('status');
});

Schema::create('metric_definitions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->onDelete('cascade');
    $table->string('metric_id', 100);
    $table->string('name');
    $table->text('description')->nullable();
    $table->foreignId('framework_id')->nullable()->constrained();
    $table->foreignId('disclosure_id')->nullable()->constrained('gri_disclosures');
    $table->boolean('is_custom_kpi')->default(false);
    $table->enum('data_type', ['numeric', 'boolean', 'text', 'date', 'enum']);
    $table->string('unit', 50)->nullable();
    $table->enum('collection_frequency', ['monthly', 'quarterly', 'annually', 'ad_hoc'])->nullable();
    $table->enum('dimensionality', ['site', 'business_unit', 'organisation', 'project']);
    $table->boolean('is_mandatory')->default(false);
    $table->jsonb('validation_rules')->nullable();
    $table->jsonb('allowed_evidence_types')->nullable();
    $table->enum('aggregation_method', ['sum', 'weighted_average', 'count', 'calculated', 'none']);
    $table->enum('sensitivity_classification', ['public', 'internal', 'confidential', 'pii'])->default('internal');
    $table->boolean('contains_pii')->default(false);
    $table->jsonb('metadata')->nullable();
    $table->timestamps();
    $table->timestamp('deprecated_at')->nullable();

    $table->index(['tenant_id', 'metric_id']);
    $table->index('disclosure_id');
    $table->index('sensitivity_classification');
    $table->unique(['tenant_id', 'metric_id']);
});

4. Submissions & Evidence

Schema::create('metric_submissions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->onDelete('cascade');
    $table->uuid('submission_uuid')->unique();
    $table->foreignId('reporting_period_id')->constrained()->onDelete('cascade');
    $table->foreignId('site_id')->constrained()->onDelete('cascade');
    $table->foreignId('metric_definition_id')->constrained()->onDelete('cascade');
    $table->jsonb('raw_data'); // Submitted data
    $table->jsonb('processed_data')->nullable(); // Normalized data
    $table->enum('state', ['RECEIVED', 'VALIDATED', 'REJECTED', 'PROCESSED', 'APPROVED', 'LOCKED'])->default('RECEIVED');
    $table->foreignId('submitted_by_user_id')->constrained('users');
    $table->timestamp('submitted_at');
    $table->foreignId('approved_by_user_id')->nullable()->constrained('users');
    $table->timestamp('approved_at')->nullable();
    $table->integer('version')->default(1);
    $table->timestamps();

    $table->index(['tenant_id', 'reporting_period_id', 'state']);
    $table->index(['tenant_id', 'submission_uuid']);
    $table->unique(['tenant_id', 'submission_uuid']);
});

Schema::create('evidence', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->onDelete('cascade');
    $table->string('filename');
    $table->string('filepath');
    $table->bigInteger('file_size');
    $table->string('mime_type');
    $table->string('content_hash', 64); // SHA-256
    $table->string('evidence_type');
    $table->text('description')->nullable();
    $table->foreignId('uploaded_by_user_id')->constrained('users');
    $table->string('uploaded_from_ip', 45);
    $table->timestamp('uploaded_at');
    $table->boolean('legal_hold')->default(false);
    $table->date('retention_until')->nullable();
    $table->timestamps();

    $table->index(['tenant_id', 'evidence_type']);
    $table->index('content_hash');
});

Schema::create('metric_evidence', function (Blueprint $table) {
    $table->id();
    $table->foreignId('metric_submission_id')->constrained()->onDelete('cascade');
    $table->foreignId('evidence_id')->constrained()->onDelete('cascade');
    $table->timestamps();

    $table->unique(['metric_submission_id', 'evidence_id']);
});

5. Audit Logs (Immutable)

Schema::create('audit_logs', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tenant_id')->constrained()->onDelete('cascade');
    $table->foreignId('actor_user_id')->nullable()->constrained('users');
    $table->string('action'); // e.g., 'submission.approved', 'period.locked'
    $table->string('entity_type');
    $table->unsignedBigInteger('entity_id');
    $table->jsonb('before_state')->nullable();
    $table->jsonb('after_state')->nullable();
    $table->text('justification')->nullable();
    $table->string('ip_address', 45);
    $table->timestamp('created_at'); // No updated_at (immutable)

    $table->index(['tenant_id', 'created_at']);
    $table->index(['entity_type', 'entity_id']);
    $table->index('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'

---

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

---

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

---

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

• Backend Domain Models
• Reporting Concepts

---

Change Log

Version	Date	Author	Changes
1.0	2026-01-03	Senior Product Architect	Complete data model with migrations