Audit Log System

Status: Draft v0.1
Audience: Backend engineers, Security/Compliance, Super-Admin UI team
Purpose: Define how every security-relevant event (object edits, permission changes, logins, etc.) is captured, stored, and exposed to admins.

1. Why an Audit Log?

  • Regulatory compliance (ISO-27001, GDPR, SOX)
  • Incident investigation & rollback context
  • Organisation self-service history (who moved my object?)

2. Data Model

CREATE TABLE audit_log (
  id              BIGSERIAL PRIMARY KEY,
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  organisation_id UUID         NULL,          -- NULL ⇒ system-wide event
  actor_user_id   UUID         NULL,          -- NULL ⇒ system job / un-authed guest
  action_type     TEXT         NOT NULL,      -- e.g. OBJECT_UPDATE, LOGIN_SUCCESS
  entity_type     TEXT         NULL,          -- ObjectInstance, ObjectClass, User …
  entity_id       UUID         NULL,
  before_state    JSONB        NULL,
  after_state     JSONB        NULL,
  ip_address      INET         NULL,
  extra           JSONB        NULL           -- flexible (user-agent, GraphQL opName)
);

CREATE INDEX ON audit_log (organisation_id, occurred_at DESC);

before_state / after_state hold a redacted subset of the row – never store secrets or full JWTs.

Action‐Type catalogue (partial)

| Category | Action Type | |———-|————-| | Auth | LOGIN_SUCCESS, LOGIN_FAIL, PASSWORD_RESET, MFA_ENROLLED | | Object | OBJECT_CREATE, OBJECT_UPDATE, OBJECT_DELETE | | Spatial | SPATIAL_MOVE, EXPECTED_SET | | Ownership | OWNERSHIP_TRANSFER, LEND_CREATE, SALE_ACCEPT | | Identifier | ID_MINT, ID_REVOKE | | Admin | GROUP_CREATE, GROUP_ABILITY_GRANT, ACL_CHANGE | | System | BACKUP_START, BACKUP_DONE, JOB_FAIL |

3. Write-Paths

  1. Database triggers (cheap, uniform)
    Tables: object_instances, container_acl, org_permission_groups, etc. 
    CREATE TRIGGER object_update_audit
  AFTER UPDATE ON object_instances
  FOR EACH ROW EXECUTE FUNCTION audit_if_changed('OBJECT_UPDATE');

    Helper function inserts a row only when OLD IS DISTINCT FROM NEW on watched columns.

  2. Application layer (GraphQL resolvers)
    Auth events, permission grants, backup jobs call INSERT INTO audit_log … directly because they happen outside SQL.

4. Access Control

Actor Policy
System Owner SELECT * (full platform)
Org Admin organisation_id = jwt.org_id
Other users no access

RLS policy:

CREATE POLICY read_audit_for_org
  ON audit_log
  FOR SELECT USING (
    jwt_has_ability('ORG_AUDIT_VIEW')
    AND organisation_id = current_setting('request.jwt.claims', true)::json->>'org_id'
  );

Super-admin bypasses via ability SUPER_ADMIN and separate policy.

5. Retention & Storage Management

  • 3 years online, then archiving to cheaper storage (S3 + Parquet).
  • DELETE only allowed via GDPR erasure job that also encrypts user_id reference.

6. API & UI

GraphQL types:

type AuditLogEdge {
  id: ID!
  occurredAt: DateTime!
  actor: User
  actionType: AuditAction!
  entityType: String
  entityId: ID
  before: JSON
  after: JSON
  ipAddress: String
}

extend type Query {
  auditLog(after: ID, limit: Int = 50): [AuditLogEdge!]!
}

Filters automatically scoped by RLS.

UI routes already reserved: • Super-Admin: /super/logs → AuditLogViewer.tsx
• Org Admin: /admin/audit (new row will be added in future)

Features: text search, JSON diff viewer, CSV export.

7. Open Items

  • Redaction rules for before_state (e.g., mask email, passwords).
  • Decide on archiving implementation (TimescaleDB vs. S3).
  • Add ORG_AUDIT_VIEW to ability catalogue.