backend/migrations/007_reports_audit.sqlSQL
CREATE TABLE IF NOT EXISTS reports (
id CHAR(36) PRIMARY KEY,
tenant_id CHAR(36) NOT NULL,
job_id CHAR(36) NULL,
status VARCHAR(32) NOT NULL DEFAULT 'draft',
current_version INT NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
deleted_at DATETIME NULL,
UNIQUE KEY reports_tenant_id (tenant_id, id),
INDEX reports_tenant_updated (tenant_id, updated_at),
CONSTRAINT reports_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id),
CONSTRAINT reports_job_fk FOREIGN KEY (job_id) REFERENCES jobs(id)
);
CREATE TABLE IF NOT EXISTS report_versions (
id CHAR(36) PRIMARY KEY,
report_id CHAR(36) NOT NULL,
tenant_id CHAR(36) NOT NULL,
version INT NOT NULL,
payload_json LONGTEXT NOT NULL,
created_by CHAR(36) NULL,
created_at DATETIME NOT NULL,
UNIQUE KEY report_versions_report_version (report_id, version),
UNIQUE KEY report_versions_report_tenant_version (report_id, tenant_id, version),
CONSTRAINT report_versions_report_tenant_fk FOREIGN KEY (tenant_id, report_id) REFERENCES reports(tenant_id, id)
);
CREATE TABLE IF NOT EXISTS report_audit_events (
id CHAR(36) PRIMARY KEY,
report_id CHAR(36) NOT NULL,
tenant_id CHAR(36) NOT NULL,
event_type VARCHAR(40) NOT NULL,
version INT NULL,
actor_user_id CHAR(36) NULL,
metadata_json LONGTEXT NULL,
created_at DATETIME NOT NULL,
INDEX report_audit_tenant_created (tenant_id, created_at),
CONSTRAINT report_audit_report_tenant_fk FOREIGN KEY (tenant_id, report_id) REFERENCES reports(tenant_id, id),
CONSTRAINT report_audit_version_fk FOREIGN KEY (report_id, tenant_id, version) REFERENCES report_versions(report_id, tenant_id, version)
);