Audit Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-18 Companion: Service Template · Naming

1. ID Prefix Registry

Prefix	Entity
aud_	AuditEntry
exp_	AuditExport

2. TypeScript Interfaces

// domain/audit-entry.ts
export type AuditEntryId = Branded<string, 'AuditEntryId'>;
export type AuditExportId = Branded<string, 'AuditExportId'>;
export type ChainHash = Branded<string, 'ChainHash'>;

export type ActorType = 'USER' | 'SERVICE_ACCOUNT' | 'SYSTEM';
export type AuditAction = 'CREATE' | 'UPDATE' | 'DELETE' | 'READ' | 'EVALUATE' | 'EXPORT';
export type AuditOutcome = 'SUCCESS' | 'FAILURE' | 'PARTIAL';
export type ExportFormat = 'ndjson' | 'csv';
export type ExportStatus = 'queued' | 'processing' | 'completed' | 'failed';

export interface AuditEntry {
  readonly id: AuditEntryId;
  readonly tenantId: string | null;
  readonly eventType: string;
  readonly actorId: string | null;
  readonly actorType: ActorType;
  readonly resourceType: string;
  readonly resourceId: string;
  readonly action: AuditAction;
  readonly outcome: AuditOutcome;
  readonly sourceService: string;
  readonly sourceEventId: string;         // unique — enables dedup
  readonly nodeId: string | null;
  readonly metadata: Record<string, unknown>;
  readonly chainHash: ChainHash;
  readonly occurredAt: Date;
  readonly recordedAt: Date;
}

export interface AuditExport {
  readonly id: AuditExportId;
  readonly tenantId: string | null;
  readonly requestedBy: string;           // UserId
  status: ExportStatus;
  readonly filters: AuditQueryFilters;
  readonly format: ExportFormat;
  fileUrl: string | null;
  recordCount: number | null;
  readonly createdAt: Date;
  completedAt: Date | null;
}

export interface AuditQueryFilters {
  readonly tenantId?: string;
  readonly actorId?: string;
  readonly eventType?: string;
  readonly resourceType?: string;
  readonly resourceId?: string;
  readonly outcome?: AuditOutcome;
  readonly dateFrom?: Date;
  readonly dateTo?: Date;
}

3. PostgreSQL Schema

-- ============================================================
-- audit_entries: INSERT ONLY
-- PostgreSQL role audit_app: GRANT INSERT, SELECT; REVOKE UPDATE, DELETE
-- ============================================================
CREATE TABLE audit_entries (
    id              VARCHAR(36) PRIMARY KEY,    -- ULID aud_ prefix
    tenant_id       UUID,                       -- NULL = platform-level
    event_type      VARCHAR(80)  NOT NULL,
    actor_id        UUID,
    actor_type      VARCHAR(20)  NOT NULL CHECK (actor_type IN ('USER','SERVICE_ACCOUNT','SYSTEM')),
    resource_type   VARCHAR(80)  NOT NULL,
    resource_id     VARCHAR(255) NOT NULL,
    action          VARCHAR(40)  NOT NULL,
    outcome         VARCHAR(20)  NOT NULL DEFAULT 'SUCCESS',
    source_service  VARCHAR(80)  NOT NULL,
    source_event_id VARCHAR(255) NOT NULL,      -- CloudEvents id — globally unique
    node_id         UUID,
    metadata        JSONB,
    chain_hash      CHAR(64)     NOT NULL,      -- SHA-256 hex
    occurred_at     TIMESTAMPTZ  NOT NULL,
    recorded_at     TIMESTAMPTZ  NOT NULL DEFAULT now()
)
PARTITION BY RANGE (recorded_at);              -- Monthly declarative partitioning

-- Unique constraint for idempotent dedup
ALTER TABLE audit_entries ADD CONSTRAINT uq_audit_source_event UNIQUE (source_event_id);

-- Indexes for common query patterns
CREATE INDEX ix_audit_entries_tenant_occurred   ON audit_entries(tenant_id, occurred_at DESC);
CREATE INDEX ix_audit_entries_actor             ON audit_entries(actor_id, occurred_at DESC);
CREATE INDEX ix_audit_entries_resource          ON audit_entries(resource_type, resource_id, occurred_at DESC);
CREATE INDEX ix_audit_entries_event_type        ON audit_entries(event_type, occurred_at DESC);

-- Monthly partitions (created via migration or automated partition manager)
-- Example:
CREATE TABLE audit_entries_2026_04
    PARTITION OF audit_entries
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- ============================================================
-- audit_exports
-- ============================================================
CREATE TABLE audit_exports (
    id              VARCHAR(36)  PRIMARY KEY,   -- ULID exp_ prefix
    tenant_id       UUID,
    requested_by    UUID         NOT NULL,
    status          VARCHAR(20)  NOT NULL DEFAULT 'queued'
                        CHECK (status IN ('queued','processing','completed','failed')),
    filters         JSONB        NOT NULL,
    format          VARCHAR(10)  NOT NULL CHECK (format IN ('ndjson','csv')),
    file_url        TEXT,
    record_count    INTEGER,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT now(),
    completed_at    TIMESTAMPTZ
);

CREATE INDEX ix_audit_exports_status ON audit_exports(status) WHERE status IN ('queued','processing');
CREATE INDEX ix_audit_exports_tenant ON audit_exports(tenant_id, created_at DESC);

-- ============================================================
-- outbox (standard platform outbox for event publishing)
-- ============================================================
CREATE TABLE outbox (
    id              BIGSERIAL    PRIMARY KEY,
    aggregate_type  VARCHAR(80)  NOT NULL,
    aggregate_id    VARCHAR(36)  NOT NULL,
    event_type      VARCHAR(120) NOT NULL,
    payload         JSONB        NOT NULL,
    published_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX ix_outbox_unpublished ON outbox(id) WHERE published_at IS NULL;

-- ============================================================
-- Role constraints
-- ============================================================
-- GRANT INSERT, SELECT ON audit_entries TO audit_app;
-- REVOKE UPDATE ON audit_entries FROM audit_app;
-- REVOKE DELETE ON audit_entries FROM audit_app;

4. RLS Policies

-- Tenant Admin RLS: only see own-tenant entries
ALTER TABLE audit_entries ENABLE ROW LEVEL SECURITY;

CREATE POLICY audit_entries_tenant_isolation ON audit_entries
    FOR SELECT
    USING (
        current_setting('app.tenant_id', true) = tenant_id::text
        OR current_setting('app.role', true) = 'SUPER_ADMIN'
    );

5. Retention

Table	Retention policy
audit_entries	Minimum 7 years; partitions older than retention window moved to cold storage
audit_exports	1 year (metadata); exported files subject to object-storage lifecycle policy
outbox	Purged after published_at + 7 days