Pattern: Journal Tables for Entity Auditing

This pattern documents how to create and configure journal tables for entity auditing across the Acsis platform, following ADR 035: Prism Journal Tables.

Overview

Journal tables provide a complete audit trail of all changes to entities. Every insert or update to a source entity creates a new journal record, preserving the full history of the entity over time.

Key Architectural Decision: All journal tables reside in the prism schema, regardless of which component's data they track. This centralizes audit/history as platform infrastructure.

When to Use This Pattern

Add journal tables when:

• Entity changes need to be audited for compliance or business reasons
• Historical data reconstruction is required
• You need to answer "what did this entity look like at time T?"
• Cross-component change tracking is needed

Most domain entities should have journals. Reference data entities (statuses, types) typically do not.

Prerequisites

• Read ADR 035: Prism Journal Tables
• Understand the source entity structure
• Prism component is referenced and migrated in your environment
• Source entity follows ADR 034: GUID Primary Keys

The JournalTable Base Class

All journal entities inherit from Acsis.Dynaplex.JournalTable:

[NoReorder]
public abstract class JournalTable
{
    [Column("seq_id", Order = 100)]
    public int SeqId { get; set; }

    [Column("valid_from", Order = 101)]
    public DateTime ValidFrom { get; set; }

    [Column("valid_to", Order = 102)]
    public DateTime ValidTo { get; set; }

    [Column("initiated_by_user_id", Order = 103)]
    public Guid InitiatedByUserId { get; set; }

    [Column("invalidated_by_user_id", Order = 104)]
    public Guid? InvalidatedByUserId { get; set; }

    [Column("initiated_by_permission_id", Order = 105)]
    public Guid? InitiatedByPermissionId { get; set; }

    [Column("invalidated_by_permission_id", Order = 106)]
    public Guid? InvalidatedByPermissionId { get; set; }
}

Properties Explained:

• Id - The source entity's primary key (Guid) - inherited from source entity via source generator
• SeqId - Sequential version number (auto-incremented)
• ValidFrom - When this version became active
• ValidTo - When this version was superseded (infinity if current)
• InitiatedByUserId - User who created this version
• InvalidatedByUserId - User who superseded this version
• InitiatedByPermissionId - Permission that authorized creation of this version (for RBAC audit trails)
• InvalidatedByPermissionId - Permission that authorized superseding this version

Note: Properties use Order values 100-106 to allow derived classes to use 0-99 for their entity-specific properties.

Composite Primary Key: (Id, SeqId) allows multiple versions of the same entity.

Naming Conventions

Physical Database Tables

All journal tables use this naming pattern:

prism.{table}_journal

Examples:

• prism.users_journal (for identity.users)
• prism.items_journal (for catalog.items)
• prism.attribute_definitions_journal (for prism.attribute_definitions)

Journal Entity Class Files

Journal entity classes live in the source component's .Database project alongside the source entity:

engines/{component}/src/Acsis.Dynaplex.Engines.{Component}.Database/
  ├── User.cs               ← Source entity
  ├── UserLog.cs            ← Journal entity class (partial, properties auto-generated)

Naming: {Entity}Log.cs (e.g., UserLog, AttributeDefinitionLog)

Rationale: Keeping journal entities with source entities makes refactoring easier, co-locates related code, and enables the source generator to access the source entity type.

Step-by-Step Process

Phase 1: Create Journal Entity Class (Using Source Generator)

Journal entities use a Roslyn source generator that automatically copies properties from the source entity. You only need to create a minimal shell class.

1. Create the file in source component

Location: engines/{component}/src/Acsis.Dynaplex.Engines.{Component}.Database/{Entity}Log.cs

2. Apply the source generator template

Template Structure:

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

namespace Acsis.Dynaplex.Engines.{Component}.Database;

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

    public const string TABLE_NAME = "{table}_journal";

}

Key Requirements:

• [JournalFor(typeof(SourceEntity))] - triggers the source generator
• partial class - allows generated properties to be added
• Inherit from JournalTable - provides base journal properties
• [Table(TABLE_NAME, Schema = "prism")] - places table in prism schema
• Only define TABLE_NAME - all other properties are auto-generated

3. Source Generator Behavior

The JournalEntityGenerator automatically:

• ✅ Copies all value properties (primitives, strings, DateTimes, Guids, enums)
• ✅ Copies foreign key properties (UserId, TenantId, etc.)
• ✅ Preserves [Column] attributes with Order values
• ✅ Preserves [StringLength], [MaxLength], [Required], [Precision] attributes
• ✅ Uses fully qualified type names (handles cross-namespace types)
• ✅ Preserves nullable annotations (string?, Guid?, etc.)
• ❌ Excludes navigation properties (virtual ICollection<...>, virtual Entity)
• ❌ Excludes [JsonIgnore], [NotMapped], [InverseProperty] properties
• ❌ Excludes base class properties (SeqId, ValidFrom, ValidTo, etc.)

Example - UserLog.cs:

Source Entity (User.cs):

[Table("users")]
public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("id", Order = 0)]
    public Guid Id { get; set; }

    [Column("username", Order = 1)]
    public string Username { get; set; } = null!;

    [Column("email", Order = 2)]
    public string? Email { get; set; }

    [Column("tenant_id", Order = 3)]
    public Guid TenantId { get; set; }

    // Navigation - automatically excluded
    [JsonIgnore]
    public virtual Tenant Tenant { get; set; } = null!;
}

Journal Entity (UserLog.cs) - what you write:

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";

}

Generated Code (UserLog.g.cs) - created by source generator:

// <auto-generated/>
partial class UserLog
{
    [Column("id", Order = 0)]
    public global::System.Guid Id { get; set; }

    [Column("username", Order = 1)]
    public string Username { get; set; } = default!;

    [Column("email", Order = 2)]
    public string? Email { get; set; }

    [Column("tenant_id", Order = 3)]
    public global::System.Guid TenantId { get; set; }
}

4. Viewing Generated Code

To inspect generated code, add these settings to your Database .csproj:

<PropertyGroup>
    <EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>
    <CompilerGeneratedFilesOutputPath>Generated</CompilerGeneratedFilesOutputPath>
</PropertyGroup>

<ItemGroup>
    <Compile Remove="Generated/**/*.cs" />
</ItemGroup>

Generated files appear at: Generated/Acsis.Dynaplex.Strata.SourceGenerators/Acsis.Dynaplex.Strata.SourceGenerators.JournalEntityGenerator/{Entity}Log.g.cs

Phase 2: Configure in DbContext

1. Add DbSet to source component's DbContext

public partial class {Component}Db : DbContext
{
    // ... other DbSets

    public virtual DbSet<{Entity}Log> {Entity}Logs { get; set; }
}

2. Configure composite key in OnModelCreating

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // ... other configurations

    modelBuilder.Entity<{Entity}Log>(entity =>
    {
        entity.HasKey(e => new { e.Id, e.SeqId })
            .HasName("pk__{component}_{table}_journal");
    });
}

Example - PrismDb.cs:

modelBuilder.Entity<AttributeDefinitionLog>(entity =>
{
    entity.HasKey(e => new { e.Id, e.SeqId })
        .HasName("pk__prism_attribute_definitions_journal");
});

Phase 3: Register for Automatic Trigger Creation

Triggers are created automatically by the Prism journal automation system. No manual trigger SQL is required.

1. Register the table in Prism's AddJournalTriggerAutomation migration

Add a registration call to engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/Migrations/20260108060000_AddJournalTriggerAutomation.cs:

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

Example - registering a new catalog entity:

-- Add to the appropriate component section in the migration
SELECT prism.register_journal_table('catalog', 'new_entities', 'trg_new_entities_journal');

How the automation works:

1. prism.journal_config table stores registry of source tables with journal tables
2. prism.register_journal_table() adds entries to the registry
3. prism.ensure_journal_triggers() creates triggers for all registered tables (idempotent)

Benefits:

• No manual trigger SQL - triggers are created automatically
• Migration consolidation resilient - registry survives consolidation, triggers can be recreated
• Idempotent - safe to run multiple times
• Discoverable - query prism.journal_config to see all registered tables

2. Register trigger in DbContext

modelBuilder.Entity<{Entity}>(entity =>
{
    entity.ToTable(tb => tb.HasTrigger("trg_{table}_journal"));
});

Example:

modelBuilder.Entity<NewEntity>(entity =>
{
    entity.ToTable(tb => tb.HasTrigger("trg_new_entities_journal"));
});

Phase 4: Migration Orchestration

Ensure Prism migrates before your component:

In Aspire AppHost (Program.cs):

var prismMigrator = builder.AddProject<Prism_DbMigrator>("prism-migrator")
    .WaitFor(database);

var {component}Migrator = builder.AddProject<{Component}_DbMigrator>("{component}-migrator")
    .WaitFor(prismMigrator);  // ← Ensures Prism schema and functions exist

var {component}Service = builder.AddProject<{Component}>("{component}")
    .WaitFor({component}Migrator);

The Generic Journal Writer Function

Prism provides a reusable PostgreSQL function that all journals use:

Location: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Abstractions/Database/Routines/journal_writer.sql

Function Signature:

CREATE FUNCTION prism.journal_writer(
    source_schema text,
    source_table text
) RETURNS trigger

How It Works:

1. Trigger fires on INSERT/UPDATE to source table
2. Constructs journal table name: prism.{source_schema}_{source_table}_journal
3. Inserts NEW row data into journal table
4. Adds SeqId (next value from sequence)
5. Sets ValidFrom to current timestamp
6. Sets InitiatedByUserId to current user

Error Handling: Failures are logged as warnings but don't fail the source transaction.

Common Patterns

Pattern: Entity with Passport (Using Source Generator)

// Source Entity
[Table("users")]
public class User
{
    [Key]
    [Column("id", Order = 0)]
    public Guid Id { get; set; }

    [Column("platform_type_id", Order = 1)]
    public short PlatformTypeId { get; set; } = PTIDS.USER;

    [Column("tenant_id", Order = 2)]
    public Guid TenantId { get; set; }

    [Column("username", Order = 3)]
    public string Username { get; set; } = null!;

    [JsonIgnore]
    public virtual Tenant Tenant { get; set; } = null!;
}

// Journal Entity - only the shell, properties auto-generated
[JournalFor(typeof(User))]
[Table(TABLE_NAME, Schema = "prism")]
public partial class UserLog : JournalTable {

    public const string TABLE_NAME = "users_journal";

}

Pattern: Entity without Passport (Using Source Generator)

// Source Entity
[Table("permissions")]
public class Permission
{
    [Key]
    [Column("id", Order = 0)]
    public Guid Id { get; set; }

    [Column("tenant_id", Order = 1)]
    public Guid TenantId { get; set; }

    [Column("full_name", Order = 2)]
    public string FullName { get; set; } = null!;
}

// Journal Entity - only the shell, properties auto-generated
[JournalFor(typeof(Permission))]
[Table(TABLE_NAME, Schema = "prism")]
public partial class PermissionLog : JournalTable {

    public const string TABLE_NAME = "permissions_journal";

}

Pattern: Reference Data (No Journal)

Small, rarely-changing lookup tables typically don't need journals:

[Table("user_statuses")]
public class UserStatus
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; } = null!;
}

// NO UserStatusLog needed

Completed Examples

Prism Component

The Prism component contains the canonical examples:

Journal Entities Created:

• ✅ AttributeDefinitionLog.cs - Tracks attribute definition changes
• ✅ AttributeOptionLog.cs - Tracks attribute option changes
• ✅ AttributeOptionCollectionLog.cs - Tracks collection changes
• ✅ AttributeValueLog.cs - Tracks attribute values
• ✅ IdentifierLog.cs - Tracks identifier changes
• ✅ PassportLog.cs - Tracks passport changes

Database Configuration:

• Composite keys configured in PrismDb.cs
• Triggers registered with .HasTrigger()
• All tables in prism schema

Querying Journal Data

Get Current Version

var current = await context.Users
    .Where(u => u.Id == userId)
    .FirstOrDefaultAsync();

Get All Versions

var history = await context.UserLogs
    .Where(ul => ul.Id == userId)
    .OrderBy(ul => ul.SeqId)
    .ToListAsync();

Get Version at Specific Time

var snapshot = await context.UserLogs
    .Where(ul => ul.Id == userId)
    .Where(ul => ul.ValidFrom <= targetDate)
    .Where(ul => ul.ValidTo == null || ul.ValidTo > targetDate)
    .FirstOrDefaultAsync();

Get Recent Changes Across Components

// All changes in last 24 hours
var changes = await context.UserLogs
    .Where(ul => ul.ValidFrom >= DateTime.UtcNow.AddDays(-1))
    .OrderByDescending(ul => ul.ValidFrom)
    .ToListAsync();

Troubleshooting

Issue: "Table prism.

Cause: Prism migration didn't run before component migration

Solution:

1. Check Aspire AppHost orchestration
2. Ensure component migrator has .WaitFor(prismMigrator)
3. Verify Prism migrations executed successfully

Issue: "Function prism.journal_writer does not exist"

Cause: Prism's journal_writer function not deployed

Solution:

1. Check Prism migration status
2. Verify journal_writer.sql is in Prism.Abstractions
3. Ensure SQL file is marked as EmbeddedResource in .csproj
4. Re-run Prism migrations

Issue: "Cannot insert duplicate key in object prism.

Cause: Composite key constraint violation (Id, SeqId)

Solution: Ensure composite key is configured:

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

Issue: "Column order is incorrect in migration"

Cause: Missing [NoReorder] attribute or Order values out of sequence

Solution:

1. Add [NoReorder] to journal class
2. Verify Order starts at 6 (JournalTable uses 0-5)
3. Ensure no gaps in Order sequence

Issue: "Navigation property in journal entity"

Cause: Copied navigation property from source entity

Solution: Use the source generator pattern instead of manually copying properties. Navigation properties are automatically excluded.

Issue: "Source generator not producing output"

Cause: Multiple possible causes

Solutions:

1. Ensure class is marked partial
2. Ensure [JournalFor(typeof(SourceEntity))] attribute is applied
3. Check for duplicate source generator references in .csproj
4. Verify source entity type is accessible from journal entity file
5. Build project and check compiler output for generator errors

Issue: "Type not found in generated code"

Cause: Type from different namespace not properly qualified

Solution: The source generator uses fully qualified type names (e.g., global::Acsis.Dynaplex.Engines.Catalog.Abstractions.Primitives.EnforcedUniquenessLevel). If you see this error, ensure the type's assembly is referenced.

Issue: "Nullable annotation missing in generated code"

Cause: Older version of source generator

Solution: The source generator preserves nullable annotations. If string? properties are generated as string, update the source generator.

Issue: "Properties not appearing in generated file"

Cause: Property may be excluded by source generator rules

Solution: Check if property has any of these attributes (which cause exclusion):

• [JsonIgnore]
• [NotMapped]
• [InverseProperty]

Or if property type is:

• ICollection<T> or List<T>
• A class with [Table] attribute (navigation property)

Checklist: Adding Journal to Entity

Use this checklist for each entity:

Planning:

• Entity is a domain entity (not reference data)
• Entity follows GUID primary key pattern
• Prism component is referenced
• Source entity is in .Database project (same as where journal will be)

Entity Class (Using Source Generator):

• Created {Entity}Log.cs in source component .Database/ project
• Added [JournalFor(typeof(SourceEntity))] attribute
• Made class partial
• Inherited from JournalTable
• Added [Table(TABLE_NAME, Schema = "prism")]
• Defined TABLE_NAME constant: "{table}_journal"
• Added XML doc: /// Journal entity for <see cref="{Entity}"/>. Properties are auto-generated.
• DO NOT manually copy properties - source generator handles this

DbContext Configuration:

• Added DbSet<{Entity}Log> to component DbContext
• Configured composite key (Id, SeqId) in OnModelCreating
• Named primary key constraint pk__{component}_{table}_journal

Trigger Registration (Automated):

• Added registration to AddJournalTriggerAutomation migration in Prism:

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

• Triggers created automatically by ensure_journal_triggers() - NO manual SQL needed
• Registered trigger with .HasTrigger() in DbContext

Migration:

• Component migrator waits for Prism migrator in Aspire AppHost
• Build succeeds
• Migration generates correctly

Verification:

• Build project to confirm source generator produces {Entity}Log.g.cs
• Check Generated/ folder for generated file (if EmitCompilerGeneratedFiles=true)
• Insert to source table creates journal record
• Update to source table creates new journal record
• Composite key allows multiple versions
• Query by (Id, SeqId) works
• Temporal queries work (ValidFrom, ValidTo)

References

• ADR 035: Prism Journal Tables
• ADR 034: GUID Primary Keys
• PostgreSQL: Triggers
• EF Core: Database Triggers
• Temporal Queries Pattern

Future Enhancements

• Soft Deletes: Add Deleted boolean and populate journal on DELETE
• Change Tracking: Add ChangeType column (INSERT, UPDATE, DELETE)
• User Context: Enhance InitiatedByUserId with session/request context
• Partitioning: Partition journal tables by time range for performance
• Retention Policies: Implement automatic archival of old journal data
• Event Sourcing: Evolve journals into full event store