adrs/035-prism-journal-tables.md

ADR 035: Centralize Journal Tables in Prism Schema

Status: Accepted
Date: 2025-10-26
Updated: 2026-01-08 (db-manager consolidation)
Deciders: Architecture Team
Related: ADR 034 (GUID Primary Keys), ADR 036 (Database Project Separation)

Context

Journal tables (audit logs) track the complete history of changes to entities across the platform. Originally, we considered two approaches:

Co-located journals: Each component's journal tables live in its own schema
- Example: identity.users_journal alongside identity.users
Centralized journals: All journal tables live in the Prism schema
- Example: prism.identity_users_journal for identity.users

Why This Decision Matters

Prism's Role: Prism is the foundational component containing platform infrastructure:
- platform_types - Type registry for all entities
- passports - Global unique ID registry
- JournalTable - Base class for all journals
Cross-Cutting Concerns: Audit/history is platform infrastructure, not business logic
Query Patterns: Common queries span components ("show all changes by user X")

Decision

All journal tables will reside in the prism schema, regardless of which component's data they track.

Naming Convention

prism.{table}_journal

Examples:

prism.users_journal (tracks identity.users)
prism.items_journal (tracks catalog.items)
prism.workflow_runs_journal (tracks workflow.workflow_runs)
prism.attribute_definitions_journal (tracks prism.attribute_definitions)

Technical Implementation

1. Generic Journal Writer Function

Create a reusable PostgreSQL function in Prism (current implementation):

CREATE OR REPLACE FUNCTION prism.journal_writer() RETURNS TRIGGER AS $$
DECLARE
    actor_id    uuid;
    last_seq    int;
    base_reg    regclass := TG_RELID;
    base_schema text := TG_TABLE_SCHEMA;
    base_table  text := TG_TABLE_NAME;
    base_fqn    text := base_reg::text;
    action_time timestamptz := now();
    pk_name TEXT := CASE
        WHEN base_table = 'passports' THEN 'global_id'
        ELSE 'id'
    END;
    log_fqn TEXT := format('prism.%I_journal', base_table);
BEGIN
    actor_id := NULLIF(current_setting('app.user_id', true), '')::uuid;

    IF actor_id IS NULL THEN
        RAISE EXCEPTION 'app.user_id must be set to a valid user id when modifying %', TG_TABLE_NAME;
    END IF;

    IF TG_OP IN ('INSERT','UPDATE') THEN
        -- close the active entry for the entity (if any)
        EXECUTE format($q$
            UPDATE
                %s
            SET
                valid_to = $2,
                invalidated_by_user_id = $3
            WHERE
                %I = ($1).%2$I
            AND
                valid_to = ''infinity''
            RETURNING
                seq_id
        $q$, log_fqn, pk_name)
        INTO
            last_seq
        USING
            NEW, action_time, actor_id;

        IF last_seq IS NULL THEN
            last_seq := 0;
        END IF;

        EXECUTE format($q$
            INSERT INTO
                %s
            SELECT
                (jsonb_populate_record(NULL::%s, to_jsonb($1))).*,
                $2::int,
                $3::timestamptz,
                'infinity'::timestamptz,
                $4::uuid,
                NULL::uuid
        $q$, log_fqn, base_fqn
        )
        USING
            NEW, (last_seq + 1), action_time, actor_id;

        RETURN NEW;
    ELSE  -- DELETE
        EXECUTE format($q$
            UPDATE
                %s
            SET
                valid_to = $2,
                invalidated_by_user_id = $3
            WHERE
                %I = ($1).%2$I
            AND
                valid_to = ''infinity''
        $q$, log_fqn, pk_name)
        USING OLD, action_time, actor_id;
        RETURN OLD;
    END IF;

END;
$$ LANGUAGE plpgsql;

2. Source Table Triggers (Automated)

Triggers are created automatically by the prism.ensure_journal_triggers() function. No manual trigger SQL is required in migrations.

How it works:

Registry Table: prism.journal_config stores which source tables have journal tables
Registration Function: prism.register_journal_table(schema, table, trigger_name) registers a table
Trigger Creation: prism.ensure_journal_triggers() creates missing triggers by scanning the registry

Migration Flow:

-- Journal tables are registered in the AddJournalTriggerAutomation migration
-- Triggers are created automatically - NO MANUAL SQL NEEDED

-- Example of what the automation does internally:
-- (you don't write this - the automation handles it)
CREATE TRIGGER trg_users_journal
AFTER INSERT OR UPDATE OR DELETE ON identity.users
FOR EACH ROW EXECUTE FUNCTION prism.journal_writer();

Benefits:

Idempotent: Safe to run multiple times - existing triggers won't be recreated
Migration Consolidation Resilient: After consolidation, call ensure_journal_triggers() to recreate all triggers
Consistent: Single approach across all components
Discoverable: Query prism.journal_config to see all registered journal tables

3. EF Core Configuration

Prism provides HasJournal() extension for other components in Prism.Abstractions:

// In Acsis.Dynaplex.Engines.Prism.Abstractions
public static class PrismDbModelBuilderExtensions
{
    public static void HasJournal<T>(this EntityTypeBuilder<T> builder)
        where T : class
    {
        var tableName = builder.Metadata.GetTableName();
        var triggerName = $"trg_{tableName}_journal";
        builder.ToTable(t => t.HasTrigger(triggerName));
    }
}

// In component DbContext (e.g., Acsis.Dynaplex.Engines.Identity.Database)
using Acsis.Dynaplex.Engines.Prism.Abstractions;

modelBuilder.Entity<User>().HasJournal();
modelBuilder.Entity<Role>().HasJournal();

4. Journal Entity Classes with Source Generator

Journal entities live in their source component's Database project and use a Roslyn source generator to automatically copy properties from the source entity:

engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/
  ├── User.cs
  ├── UserLog.cs  ← Journal entity class (partial, properties auto-generated)

Source Generator Pattern:

using System.ComponentModel.DataAnnotations.Schema;
using Acsis.Dynaplex;

namespace Acsis.Dynaplex.Engines.Identity.Database;

/// <summary>
/// Journal entity for <see cref="User"/>. Properties are auto-generated.
/// </summary>
[JournalFor(typeof(User))]
[Table(TABLE_NAME, Schema = "prism")]
public partial class UserLog : JournalTable {

    public const string TABLE_NAME = "users_journal";

}

Key Points:

Apply [JournalFor(typeof(SourceEntity))] attribute to trigger source generation
Class must be partial to allow generated properties to be added
Inherit from JournalTable (provides SeqId, ValidFrom, ValidTo, InitiatedByUserId, InvalidatedByUserId, InitiatedByPermissionId, InvalidatedByPermissionId)
Only define TABLE_NAME constant - all entity properties are auto-generated
The source generator copies all value properties, preserving [Column] attributes and nullable annotations
Navigation properties and [JsonIgnore]/[NotMapped] properties are automatically excluded
Permission columns (InitiatedByPermissionId, InvalidatedByPermissionId) enable RBAC audit trails by tracking which permission authorized each change

Source Generator Location: strata/source-generators/src/Acsis.Dynaplex.Strata.SourceGenerators/JournalEntityGenerator.cs

Rationale: The journal entity is tightly coupled to the source entity structure. Keeping them together:

Makes refactoring easier (change both in one PR)
Co-locates related code
Doesn't pollute Prism with domain knowledge
Source generator ensures journal always matches source entity structure

5. Migration Orchestration

The centralized db-manager component ensures Prism migrates first:

// In db-manager - migrations run in dependency order
var dbManager = builder.AddDynaplexDbManager()
    .WithReference(database)
    .WaitFor(database);

// All services wait for db-manager (which handles ALL migrations)
var identityService = builder.AddProject<Identity>("identity")
    .WaitFor(dbManager);  // db-manager ensures Prism schema ready first

Note (2026-01-08): The original per-component DbMigrator approach has been replaced by a centralized db-manager component. The db-manager uses EngineManifest to determine migration order, always running Prism first.

6. Journal Table Ownership

Each component's Database project owns the journal table migrations for its entities, even though the physical tables are created in the prism schema.

Composite Keys

All journal tables use composite primary key:

modelBuilder.Entity<UserLog>().HasKey(ul => new { ul.Id, ul.SeqId });

Where:

Id - The source entity's primary key (Guid)
SeqId - Sequential version number (int, auto-incremented)

Consequences

Positive

✅ Centralized Audit: Single schema for all historical data
✅ Cross-Component Queries: Easy to query "all changes in last hour" across platform
✅ Clear Separation: Reinforces Prism as foundational infrastructure
✅ Single Generic Function: One journal_writer function handles all components
✅ No Performance Overhead: Cross-schema references in PostgreSQL are zero-cost
✅ Easier Evolution: Change journaling system in one place
✅ Consistent Structure: All journals follow same pattern via JournalTable base class

Negative

⚠️ Component Dependency: All components depend on Prism schema existing
⚠️ Migration Coordination: Prism must migrate before other components
⚠️ Namespace Clutter: Prism schema contains tables for other components
⚠️ Conceptual Indirection: Journal tables physically separated from source tables
⚠️ Actor Requirement: app.user_id must be set for write operations or journaling will reject the write

Mitigations

Dependency Management:

Explicitly document Prism as foundational (already true for PlatformTypes, Passports)
The centralized db-manager enforces correct migration order via EngineManifest
Clear error messages if Prism not available

Namespace Organization:

Use naming convention to clearly identify source component

Consider database views in source schemas that query Prism journals:

CREATE VIEW identity.users_journal AS
SELECT * FROM prism.identity_users_journal;

Alternatives Considered

Alternative 1: Co-Located Journals

Keep each component's journals in its own schema:

identity.users_journal
catalog.items_journal

Rejected because:

Harder to query across components
Duplicates journaling infrastructure per component
Loses architectural clarity of Prism's role
Doesn't align with PlatformTypes/Passports already being in Prism

Alternative 2: Separate Audit Component

Create a new audit component for all journals:

audit.identity_users_journal
audit.catalog_items_journal

Rejected because:

Prism already serves this foundational role
Would fragment platform infrastructure across two components
Passports and PlatformTypes conceptually belong with journals (all track entities)

Alternative 3: Hybrid Approach

Journal entity classes in Prism, but physical tables co-located:

Rejected because:

Breaks encapsulation (Prism would know about all domain entities)
Makes refactoring harder (change entity → must update Prism)
Doesn't solve cross-component query problem

Implementation Checklist

When creating a new journal table using the source generator:

Entity Class:

Create {Entity}Log.cs in source component's Database project
Apply [JournalFor(typeof(SourceEntity))] attribute
Make class partial to allow generated properties
Inherit from JournalTable
Add [Table(TABLE_NAME, Schema = "prism")] attribute
Define TABLE_NAME constant: "{table}_journal"
Do NOT manually copy properties - source generator handles this

DbContext Configuration:

Add DbSet<{Entity}Log> to component's DbContext
Configure composite key: (Id, SeqId) in OnModelCreating
Call .HasJournal() on source entity in DbContext

Trigger Registration (Automated):

Add table registration to AddJournalTriggerAutomation migration in Prism:

SELECT prism.register_journal_table('{schema}', '{table}', 'trg_{table}_journal');

Triggers are created automatically by ensure_journal_triggers() - NO manual SQL needed
Ensure Prism migrations run before source component migrations
Backfill existing entities with at least one initial journal row

Verification:

Build project to verify source generator produces {Entity}Log.g.cs
Check Generated/ folder for generated file (if EmitCompilerGeneratedFiles=true)
Query prism.journal_config to verify table is registered

References

Future Considerations

Event Sourcing: Journal tables could evolve into event store for CQRS
Temporal Tables: PostgreSQL 17+ may offer native temporal table support
Performance: If Prism schema grows large, consider partitioning by component
Retention Policies: Implement tenant-specific retention windows and archival/purging strategies
Tenant Controls: Allow per-tenant journaling enablement/disablement by entity or component

Coverage Scope (Approved)

These entities require journaling, by component.

Catalog

Item
Item Category
Item Status
Item Type

Events

Event

Identity

Group
Permission
Role
Tenant
User
User Property *
User Security *

IoT

Device

Printing

Label Template
Label Template Field
Printer

Prism

Passport
Identifier
Attribute Definition
Attribute Assignment
Attribute Option
Attribute Option Collection
Attribute Value

Spatial

Address
Azure Address *
Country *
Location
Location Category
Movement
Movement Property
Region

System Environment

Organization *
Scheduler *
Tenant Vocabulary *
Vocab Term *

Transport

Delivery
Shipment
Shipment Item Allocation

Workflow

Workflow
Workflow Category
Workflow Event
Workflow Run

Note: Legacy Integer ID Entities

Entities marked with * use legacy long, int, or short primary keys instead of Guids. The journal system handles these correctly - the journal_writer() function uses dynamic SQL that works with any ID type.

Entity	Component	ID Type
User Property	Identity	long
User Security	Identity	long
Azure Address	Spatial	long
Country	Spatial	short
Organization	System Environment	long
Scheduler	System Environment	int
Tenant Vocabulary	System Environment	int
Vocab Term	System Environment	int

These entities will be migrated to Guid primary keys per ADR 034 as part of ongoing modernization efforts.

Documentation

ADR 035: Centralize Journal Tables in Prism Schema

Context

Why This Decision Matters

Decision

Naming Convention

Technical Implementation

Composite Keys

Consequences

Positive

Negative

Mitigations

Alternatives Considered

Alternative 1: Co-Located Journals

Alternative 2: Separate Audit Component

Alternative 3: Hybrid Approach

Implementation Checklist

References

Future Considerations

Coverage Scope (Approved)

Catalog

Events

Identity

IoT

Printing

Prism

Spatial

System Environment

Transport

Workflow

Note: Legacy Integer ID Entities