Documentation

fsds/prism-journal-system-implementation-plan.md

Journal System Implementation Plan

IMMEDIATE FIX REQUIRED: Identifier.Id Migration

Problem

The migration 20251209215907_ChangeIdentifierIdToGuid.cs fails with:

cannot alter type of a column used by a view or rule

Root Cause

Views in bbu schema reference prism.identifiers table:

  • bbu.bbu_oblpn_shipments (directly references prism.identifiers)
  • bbu.bbu_oblpn_components (directly references prism.identifiers)
  • bbu.basket_dwell_detail (directly references prism.identifiers)
  • Plus dependent views: bbu_oblpn_status_daily, bbu_oblpn_load_progress, bbu_oblpn_component_mix, bbu_oblpn_location_backlog, basket_dwell_by_timeframe, basket_location_map

Solution

Update the Prism migration to:

  1. Drop all BBU analytics views in reverse dependency order
  2. Drop identity and change column type
  3. The BBU seeder will recreate views on next startup (via SupersetAnalyticsViewsSql.CreateScripts)

File to Modify

engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/Migrations/20251209215907_ChangeIdentifierIdToGuid.cs

Updated Migration SQL

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        -- Drop BBU analytics views that depend on prism.identifiers
        -- (Must be in reverse dependency order)
        DROP VIEW IF EXISTS bbu.location_return_performance;
        DROP VIEW IF EXISTS bbu.basket_location_map;
        DROP VIEW IF EXISTS bbu.basket_dwell_by_timeframe;
        DROP VIEW IF EXISTS bbu.basket_dwell_detail;
        DROP VIEW IF EXISTS bbu.basket_dwell_by_location;
        DROP VIEW IF EXISTS bbu.bbu_oblpn_location_backlog;
        DROP VIEW IF EXISTS bbu.bbu_oblpn_component_mix;
        DROP VIEW IF EXISTS bbu.bbu_oblpn_load_progress;
        DROP VIEW IF EXISTS bbu.bbu_oblpn_status_daily;
        DROP VIEW IF EXISTS bbu.bbu_oracle_ingestion_daily_summary;
        DROP VIEW IF EXISTS bbu.bbu_oblpn_components;
        DROP VIEW IF EXISTS bbu.bbu_oblpn_shipments;
        DROP VIEW IF EXISTS bbu.bbu_oracle_ingestion_runs;

        -- Drop the identity property first (required before type change)
        ALTER TABLE prism.identifiers ALTER COLUMN id DROP IDENTITY IF EXISTS;

        -- Change the type to uuid, generating new GUIDs for existing rows
        ALTER TABLE prism.identifiers ALTER COLUMN id TYPE uuid USING gen_random_uuid();
    ");
}

Note: The BBU seeder (SupersetAnalyticsSeeder) will recreate all views on next application startup.

Executive Summary

The journal system is partially implemented. ADR 035 defines a solid architecture where all journal tables reside in the prism schema, but only the Prism component has journal tables. There are approximately 40+ domain entities across other components that need journal tables but don't have them.

User Decisions

  • Approach: Phased - fix infrastructure first, then component-by-component
  • Operational Data: Skip high-volume tables (TagReadProcessing, CameraReadProcessing, etc.)
  • Analyzers: Fix them to use correct JournalForAttribute and JournalTable
  • SQL Deployment: Via EF Core migrations using migrationBuilder.Sql()
  • Schema Attribute: Add explicit Schema = "prism" to all journal entity Table attributes
  • JournalForAttribute: Use [JournalFor(typeof(SourceEntity))] with partial class to enable source generator
  • Naming Convention: Use simplified {table}_journal naming (e.g., users_journal not identity_users_journal)
  • Base Class Location: Move JournalTable from Prism.Database to Prism.Abstractions
  • Temporal Functions: Deploy *_at.sql functions alongside journal infrastructure
  • Migration Ownership: Each component creates its own journal tables (in prism schema) via its own migrations

Current State Analysis

What's Working

  1. ADR 035 - Well-documented architecture for centralized journals in prism schema
  2. JournalTable base class - strata/core/src/Acsis.Dynaplex/JournalTable.cs
  3. Source Generator - JournalEntityGenerator automatically copies properties from source entities
  4. journal_writer.sql - Generic PostgreSQL trigger function exists
  5. Prism journal entities - PassportLog, IdentifierLog, AttributeDefinitionLog, AttributeAssignmentLog, AttributeOptionLog, AttributeOptionCollectionLog, AttributeValueLog
  6. Component journal entities - Identity (5), Catalog (3), Printing (3), Events (1), IoT (1)
  7. Temporal query functions - *_at.sql functions for point-in-time queries
  8. Slash command - /generate-journal-entity updated for source generator pattern

What's Broken/Incomplete

  1. Roslyn analyzers are outdated - Looking for AuditOfAttribute and JournalRowBase which don't exist
  2. No triggers deployed - The journal_writer function exists but no triggers call it
  3. ~40 missing journal tables - Critical domain entities have no audit trail
  4. Schema attribute missing - Existing journal entities don't have Schema = "prism" in Table attribute
  5. Documentation inconsistency - Docs say Order starts at 6, but JournalTable uses 100-106

Gap Analysis by Component

HIGH PRIORITY (Security/Compliance Critical)

Component Entity Table Status
identity User users ❌ Missing
identity Role roles ❌ Missing
identity Tenant tenants ❌ Missing
identity Group groups ❌ Missing

HIGH PRIORITY (Core Business Operations)

Component Entity Table Status
catalog Item items ❌ Missing
spatial Location locations ❌ Missing
spatial Movement movements ❌ Missing
transport Shipment shipments ❌ Missing

MEDIUM PRIORITY (Important Configuration)

Component Entity Table Status
catalog ItemType item_types ❌ Missing
catalog ItemStatus item_statuses ❌ Missing
spatial Address addresses ❌ Missing
spatial Region regions ❌ Missing
transport Delivery deliveries ❌ Missing
workflow Workflow workflows ❌ Missing
workflow WorkflowRun workflow_runs ❌ Missing
printing LabelTemplate label_templates ❌ Missing
printing Printer printers ❌ Missing
iot Device devices ❌ Missing
bbu Customer customers ❌ Missing
events Event events ❌ Missing

EXCLUDED (High Volume/Operational - User Decision)

These tables are intentionally excluded from journaling per user decision:

  • bbu.TagReadProcessing, CameraReadProcessing, MovementDeduplication
  • iot.DeviceConnectionEvent
  • printing.PrintJob

Implementation Approach (Phased)

Phase 1: Fix Infrastructure

  1. Move JournalTable to Abstractions - Enable other components to reference it
  2. Fix Roslyn analyzers - Update to use JournalForAttribute and JournalTable
  3. Fix existing Prism journal entities - Add Schema = "prism" to Table attributes
  4. Deploy journal_writer() function - Currently not deployed!
  5. Deploy temporal query functions - passports_at, identifiers_at, etc.
  6. Add triggers for existing journal tables - 7 tables in Prism need triggers

Phase 2: Identity Component (Security Critical)

  1. Create UserLog, RoleLog, TenantLog, GroupLog
  2. Configure in IdentityDb context
  3. Create and deploy triggers
  4. Add EF Core migrations

Phase 3: Catalog Component (COMPLETED)

Journal Entity Files (using source generator):

  • engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemLog.cs ✅
  • engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemTypeLog.cs ✅
  • engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemCategoryLog.cs ✅

Phase 4: Printing Component (COMPLETED)

Journal Entity Files (using source generator):

  • engines/printing/src/Acsis.Dynaplex.Engines.Printing.Database/PrinterLog.cs ✅
  • engines/printing/src/Acsis.Dynaplex.Engines.Printing.Database/LabelTemplateLog.cs ✅
  • engines/printing/src/Acsis.Dynaplex.Engines.Printing.Database/LabelTemplateFieldLog.cs ✅

Phase 5: Events Component (COMPLETED)

Journal Entity Files (using source generator):

  • engines/events/src/Acsis.Dynaplex.Engines.Events.Database/EventLog.cs ✅

Phase 6: IoT Component (COMPLETED)

Journal Entity Files (using source generator):

  • engines/iot/src/Acsis.Dynaplex.Engines.Iot.Database/DeviceLog.cs ✅

Phase 7: Remaining Components (TODO)

Spatial:

  • LocationLog, LocationCategoryLog, MovementLog, MovementPropertyLog, AddressLog, RegionLog

Transport:

  • ShipmentLog, DeliveryLog, ShipmentItemAllocationLog

Workflow:

  • WorkflowLog, WorkflowCategoryLog, WorkflowEventLog, WorkflowRunLog

BBU:

  • CustomerLog

System Environment:

  • OrganizationLog, SchedulerLog, TenantVocabularyLog, VocabTermLog

Note: Some entities use legacy integer IDs (long/int/short) instead of Guids. The journal system handles these correctly. See ADR-035 for the complete list.

Technical Tasks Per Entity

For each journal entity, the following work is required:

1. Create Journal Entity Class (Using Source Generator)

engines/{component}/src/Acsis.Dynaplex.Engines.{Component}.Database/{Entity}Log.cs
  • Apply [JournalFor(typeof(SourceEntity))] attribute
  • Make class partial
  • Inherit from JournalTable
  • Add [Table(TABLE_NAME, Schema = "prism")]
  • Define TABLE_NAME constant: "{table}_journal" (e.g., "users_journal")
  • DO NOT manually copy properties - source generator handles this

2. Configure in DbContext

  • Add DbSet<{Entity}Log>
  • Configure composite key (Id, SeqId)
  • Register trigger with .HasTrigger()

3. Create Trigger SQL

CREATE TRIGGER tr_{table}_journal
AFTER INSERT OR UPDATE ON {schema}.{table}
FOR EACH ROW EXECUTE FUNCTION prism.journal_writer();

4. Add Migration

  • Generate EF Core migration for new journal table
  • Include trigger creation in migration

Files to Modify/Create

Phase 1: Infrastructure Fixes

1. Move JournalTable base class:

  • FROM: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/JournalTable.cs
  • TO: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Abstractions/Database/JournalTable.cs
  • Update namespace from Acsis.Dynaplex.Engines.Prism.Database to Acsis.Dynaplex.Engines.Prism.Abstractions.Database
  • Update all references in Prism.Database journal entities

2. Roslyn Analyzers (FIX):

  • strata/analyzers/src/Acsis.RoslynAnalyzers/JournalEntityClassAnalyzer.cs
    • Change AuditGen.AuditOfAttribute → Acsis.Dynaplex.JournalForAttribute
    • Change JournalRowBase → JournalTable
    • Update skip list: SeqId, ValidFrom, ValidTo, InitiatedByUserId, InvalidatedByUserId, InitiatedByPermissionId, InvalidatedByPermissionId
  • strata/analyzers/src/Acsis.RoslynAnalyzers/JournalEntityCodeFixProvider.cs
    • Same fixes as analyzer

3. Prism Journal Entities (FIX Schema attribute + namespace):

  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/PassportLog.cs
  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/IdentifierLog.cs
  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/AttributeDefinitionLog.cs
  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/AttributeAssignmentLog.cs
  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/AttributeOptionLog.cs
  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/AttributeOptionCollectionLog.cs
  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/AttributeValueLog.cs

4. Prism Migration (NEW - deploy infrastructure):

  • engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/Migrations/YYYYMMDD_AddJournalInfrastructure.cs
    • Deploy prism.journal_writer() function (from Routines/journal_writer.sql)
    • Deploy temporal functions: passports_at(), identifiers_at(), attribute_definitions_at(),
      attribute_options_at(), attribute_options_collections_at(), attribute_values_at()
    • Create triggers for all 7 existing journal tables:
      • tr_passports_journal, tr_identifiers_journal, tr_attribute_definitions_journal,
        tr_attribute_assignments_journal, tr_attribute_options_journal,
        tr_attribute_option_collections_journal, tr_attribute_values_journal

Phase 2: Identity Component (COMPLETED)

Journal Entity Files (using source generator):

  • engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/UserLog.cs ✅
  • engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/RoleLog.cs ✅
  • engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/TenantLog.cs ✅
  • engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/GroupLog.cs ✅
  • engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/PermissionLog.cs ✅

DbContext Modifications:

  • engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/IdentityDb.cs
    • Add DbSets for journal entities
    • Configure composite keys
    • Register triggers

New Migration:

  • engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/Migrations/
    • Create journal tables in prism schema
    • Create triggers

Subsequent Phases (Catalog, Spatial, Transport, etc.)

Follow same pattern as Identity - create journal entities, modify DbContext, add migration.

Execution Notes

  1. Use source generator pattern - Create minimal partial class with [JournalFor(typeof(Entity))]
  2. Use /generate-journal-entity slash command for guided creation
  3. Naming: Use {table}_journal format (e.g., users_journal, not identity_users_journal)
  4. Location: Journal entities go in .Database project alongside source entities
  5. SQL triggers go in migrations using migrationBuilder.Sql()
  6. Test each phase before moving to next
  7. One PR per phase for easier review

Source Generator Reference

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

Template:

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

}

To view generated code, add to .csproj:

<PropertyGroup>
    <EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>
    <CompilerGeneratedFilesOutputPath>Generated</CompilerGeneratedFilesOutputPath>
</PropertyGroup>
<ItemGroup>
    <Compile Remove="Generated/**/*.cs" />
</ItemGroup>

Backfill Strategy

After journal tables and triggers are created, existing entities need initial journal entries. This is a one-time migration to establish the audit trail baseline.

Backfill Parameters

  • Sentinel Date: '2025-12-01 00:00:00+00'::timestamptz for entities without timestamps
  • System User ID: '00000000-0000-0000-0000-000000000000'::uuid (nil GUID)
  • Initial seq_id: 1
  • valid_to: 'infinity'::timestamptz

Entities WITH Existing Timestamps

These entities have create_date or created_at columns - use actual creation time for valid_from:

Component Entity Timestamp Column
Identity User create_date
Identity Permission created_at
Identity UserProperty create_date
Identity UserSecurity create_date
Workflow Workflow create_date
Workflow WorkflowCategory create_date
Workflow WorkflowEvent create_date
Workflow WorkflowRun create_date
IoT Device created_at
System Environment Scheduler create_date
System Environment TenantVocabulary created_date

Backfill SQL Pattern (with existing timestamp):

INSERT INTO prism.users_journal
SELECT
    (jsonb_populate_record(NULL::identity.users, to_jsonb(u))).*,
    1::int,                                           -- seq_id
    u.create_date,                                    -- valid_from (use actual timestamp)
    'infinity'::timestamptz,                          -- valid_to
    '00000000-0000-0000-0000-000000000000'::uuid,    -- initiated_by_user_id
    NULL::uuid,                                       -- invalidated_by_user_id
    NULL::uuid,                                       -- initiated_by_permission_id
    NULL::uuid                                        -- invalidated_by_permission_id
FROM identity.users u
WHERE NOT EXISTS (
    SELECT 1 FROM prism.users_journal j WHERE j.id = u.id
);

Entities WITHOUT Timestamps

These entities will use the sentinel date 2025-12-01:

Component Entities
Identity Group, Role, Tenant
Catalog Item, ItemCategory, ItemType, ItemStatus
Spatial Location, LocationCategory, Address, Region, Movement, MovementProperty, AzureAddress*, Country*
Prism Passport**, Identifier, AttributeDefinition, AttributeAssignment, AttributeOption, AttributeOptionCollection, AttributeValue
Transport Shipment, Delivery, ShipmentItemAllocation
Events Event
Printing Printer, LabelTemplate, LabelTemplateField
System Environment Organization*, VocabTerm*

*Uses legacy integer ID (long/int/short) - journal system handles this correctly.
**Passport uses global_id instead of id - see special case below.

Backfill SQL Pattern (sentinel date):

INSERT INTO prism.items_journal
SELECT
    (jsonb_populate_record(NULL::catalog.items, to_jsonb(i))).*,
    1::int,                                           -- seq_id
    '2025-12-01 00:00:00+00'::timestamptz,           -- valid_from (sentinel)
    'infinity'::timestamptz,                          -- valid_to
    '00000000-0000-0000-0000-000000000000'::uuid,    -- initiated_by_user_id
    NULL::uuid,                                       -- invalidated_by_user_id
    NULL::uuid,                                       -- initiated_by_permission_id
    NULL::uuid                                        -- invalidated_by_permission_id
FROM catalog.items i
WHERE NOT EXISTS (
    SELECT 1 FROM prism.items_journal j WHERE j.id = i.id
);

Special Case: Passport (global_id)

Passport uses global_id as its primary key instead of id:

INSERT INTO prism.passports_journal
SELECT
    (jsonb_populate_record(NULL::prism.passports, to_jsonb(p))).*,
    1::int,
    '2025-12-01 00:00:00+00'::timestamptz,
    'infinity'::timestamptz,
    '00000000-0000-0000-0000-000000000000'::uuid,
    NULL::uuid,
    NULL::uuid,
    NULL::uuid
FROM prism.passports p
WHERE NOT EXISTS (
    SELECT 1 FROM prism.passports_journal j WHERE j.global_id = p.global_id
);

Special Case: Legacy Integer ID Entities

Entities with long, int, or short primary keys work the same way - the journal_writer() function handles any ID type via dynamic SQL:

-- Example: Organization (long id)
INSERT INTO prism.organizations_journal
SELECT
    (jsonb_populate_record(NULL::system_environment.organizations, to_jsonb(o))).*,
    1::int,
    '2025-12-01 00:00:00+00'::timestamptz,
    'infinity'::timestamptz,
    '00000000-0000-0000-0000-000000000000'::uuid,
    NULL::uuid,
    NULL::uuid,
    NULL::uuid
FROM system_environment.organizations o
WHERE NOT EXISTS (
    SELECT 1 FROM prism.organizations_journal j WHERE j.id = o.id
);

-- Example: VocabTerm (int id)
INSERT INTO prism.vocab_terms_journal
SELECT
    (jsonb_populate_record(NULL::system_environment.vocab_terms, to_jsonb(v))).*,
    1::int,
    '2025-12-01 00:00:00+00'::timestamptz,
    'infinity'::timestamptz,
    '00000000-0000-0000-0000-000000000000'::uuid,
    NULL::uuid,
    NULL::uuid,
    NULL::uuid
FROM system_environment.vocab_terms v
WHERE NOT EXISTS (
    SELECT 1 FROM prism.vocab_terms_journal j WHERE j.id = v.id
);

Backfill Execution

Backfill SQL should be added to each component's journal migration, immediately after creating the journal table and trigger. This ensures:

  1. Backfill runs at the right time (journal table exists, source data exists)
  2. Each component owns its own backfill logic
  3. Idempotent via NOT EXISTS clause

Validation

After backfill, verify with:

-- Count should match source table
SELECT
    (SELECT COUNT(*) FROM identity.users) as source_count,
    (SELECT COUNT(*) FROM prism.users_journal WHERE seq_id = 1) as journal_count;

Testing Approach

The journal system requires comprehensive testing across multiple levels to ensure correctness, data integrity, and temporal query accuracy.

Test Categories

1. Trigger Behavior Tests

These tests verify the core journal_writer() function works correctly for all operations.

INSERT Trigger Test:

-- Setup: Set the user context
SET app.user_id = '11111111-1111-1111-1111-111111111111';

-- Insert a new entity
INSERT INTO identity.users (id, email, username, create_date)
VALUES ('22222222-2222-2222-2222-222222222222', 'test@example.com', 'testuser', NOW());

-- Verify journal entry was created
SELECT * FROM prism.users_journal
WHERE id = '22222222-2222-2222-2222-222222222222';

-- Assertions:
-- 1. seq_id = 1 (first version)
-- 2. valid_from = approximate NOW()
-- 3. valid_to = 'infinity'
-- 4. initiated_by_user_id = '11111111-1111-1111-1111-111111111111'
-- 5. invalidated_by_user_id IS NULL
-- 6. initiated_by_permission_id = value from app.permission_id (or NULL if not set)
-- 7. invalidated_by_permission_id IS NULL
-- 8. All entity columns match source

UPDATE Trigger Test:

-- Update the entity
UPDATE identity.users
SET email = 'updated@example.com'
WHERE id = '22222222-2222-2222-2222-222222222222';

-- Verify: Should now have 2 journal entries
SELECT seq_id, email, valid_from, valid_to, initiated_by_user_id, invalidated_by_user_id
FROM prism.users_journal
WHERE id = '22222222-2222-2222-2222-222222222222'
ORDER BY seq_id;

-- Assertions for seq_id=1 (old version):
-- 1. valid_to = approximate NOW() (no longer infinity)
-- 2. invalidated_by_user_id = '11111111-1111-1111-1111-111111111111'
-- 3. email = 'test@example.com' (original value)

-- Assertions for seq_id=2 (new version):
-- 1. valid_to = 'infinity'
-- 2. invalidated_by_user_id IS NULL
-- 3. email = 'updated@example.com' (new value)

DELETE Trigger Test:

-- Delete the entity
DELETE FROM identity.users
WHERE id = '22222222-2222-2222-2222-222222222222';

-- Verify: Last journal entry should be closed
SELECT seq_id, valid_from, valid_to, invalidated_by_user_id
FROM prism.users_journal
WHERE id = '22222222-2222-2222-2222-222222222222'
ORDER BY seq_id;

-- Assertions for highest seq_id:
-- 1. valid_to = approximate NOW() (no longer infinity)
-- 2. invalidated_by_user_id = '11111111-1111-1111-1111-111111111111'

2. User Context Enforcement Test

-- Clear user context
RESET app.user_id;

-- Attempt to insert (should FAIL)
INSERT INTO identity.users (id, email, username, create_date)
VALUES ('33333333-3333-3333-3333-333333333333', 'fail@example.com', 'failuser', NOW());

-- Expected error: "app.user_id must be set to a valid user id when modifying users"

3. Legacy Integer ID Tests

-- Test entities with long/int/short primary keys
SET app.user_id = '11111111-1111-1111-1111-111111111111';

-- Insert (e.g., Organization with long id)
INSERT INTO system_environment.organizations (name)
VALUES ('Test Organization');

-- Verify journal entry was created with the generated long id
SELECT o.id, oj.id, oj.seq_id, oj.name
FROM system_environment.organizations o
JOIN prism.organizations_journal oj ON o.id = oj.id
WHERE o.name = 'Test Organization';

-- Assertions: IDs match, seq_id = 1

4. Temporal Query Function Tests

Test the *_at() functions for point-in-time queries:

-- Setup: Create entity and update it with delays
SET app.user_id = '11111111-1111-1111-1111-111111111111';

-- Record timestamps
SELECT NOW() AS t1;  -- Before insert

INSERT INTO identity.users (id, email, username, create_date)
VALUES ('44444444-4444-4444-4444-444444444444', 'v1@example.com', 'user_v1', NOW());

SELECT pg_sleep(0.5);
SELECT NOW() AS t2;  -- After insert, before update

UPDATE identity.users
SET email = 'v2@example.com'
WHERE id = '44444444-4444-4444-4444-444444444444';

SELECT pg_sleep(0.5);
SELECT NOW() AS t3;  -- After update

-- Test temporal queries
-- At t1: Should return NULL (entity didn't exist)
SELECT * FROM prism.users_at(t1)
WHERE id = '44444444-4444-4444-4444-444444444444';

-- At t2: Should return v1@example.com
SELECT email FROM prism.users_at(t2)
WHERE id = '44444444-4444-4444-4444-444444444444';

-- At t3: Should return v2@example.com
SELECT email FROM prism.users_at(t3)
WHERE id = '44444444-4444-4444-4444-444444444444';

5. Backfill Verification Tests

-- Verify all entities have at least one journal entry
-- Run for each entity type

-- Identity
SELECT 'users' as entity,
    (SELECT COUNT(*) FROM identity.users) as source_count,
    (SELECT COUNT(DISTINCT id) FROM prism.users_journal) as journal_count,
    (SELECT COUNT(*) FROM identity.users u
     WHERE NOT EXISTS (SELECT 1 FROM prism.users_journal j WHERE j.id = u.id)) as missing_count;

-- Verify all backfilled entries have seq_id = 1
SELECT COUNT(*) as invalid_seq_count
FROM prism.users_journal
WHERE seq_id != 1
AND initiated_by_user_id = '00000000-0000-0000-0000-000000000000';

-- Verify all current entries have valid_to = infinity
SELECT COUNT(*) as unclosed_count
FROM prism.users_journal j1
WHERE j1.valid_to = 'infinity'
AND NOT EXISTS (
    SELECT 1 FROM identity.users u WHERE u.id = j1.id
);

6. Edge Case Tests

Rapid Successive Updates:

SET app.user_id = '11111111-1111-1111-1111-111111111111';

INSERT INTO identity.users (id, email, username, create_date)
VALUES ('55555555-5555-5555-5555-555555555555', 'rapid@example.com', 'rapid', NOW());

-- Rapid updates in quick succession
UPDATE identity.users SET email = 'v1@example.com' WHERE id = '55555555-5555-5555-5555-555555555555';
UPDATE identity.users SET email = 'v2@example.com' WHERE id = '55555555-5555-5555-5555-555555555555';
UPDATE identity.users SET email = 'v3@example.com' WHERE id = '55555555-5555-5555-5555-555555555555';

-- Verify all 4 versions are captured
SELECT seq_id, email, valid_from, valid_to
FROM prism.users_journal
WHERE id = '55555555-5555-5555-5555-555555555555'
ORDER BY seq_id;

-- Assertions:
-- 1. Should have 4 entries (seq_id 1-4)
-- 2. Only seq_id=4 should have valid_to = infinity
-- 3. Each version should have distinct valid_from/valid_to ranges

NULL Value Preservation:

-- Verify nullable columns preserve NULL correctly
SET app.user_id = '11111111-1111-1111-1111-111111111111';

INSERT INTO identity.users (id, email, username, middle_name, create_date)
VALUES ('66666666-6666-6666-6666-666666666666', 'null@example.com', 'nulltest', NULL, NOW());

SELECT middle_name FROM prism.users_journal
WHERE id = '66666666-6666-6666-6666-666666666666';

-- Assertion: middle_name should be NULL

Integration Test Script

Create a comprehensive test script at tests/journal-system-tests.sql:

-- Journal System Integration Tests
-- Run after migrations to verify complete system functionality

\echo '=== Journal System Integration Tests ==='

-- Test 1: Trigger Infrastructure
\echo 'Test 1: Verifying journal_writer function exists'
SELECT EXISTS (
    SELECT 1 FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'prism' AND p.proname = 'journal_writer'
) as function_exists;

-- Test 2: All triggers are installed
\echo 'Test 2: Verifying triggers are installed'
SELECT schemaname, tablename, COUNT(*) as trigger_count
FROM pg_triggers
WHERE tgfoid = (
    SELECT oid FROM pg_proc WHERE proname = 'journal_writer'
)
GROUP BY schemaname, tablename
ORDER BY schemaname, tablename;

-- Test 3: Journal tables exist in prism schema
\echo 'Test 3: Verifying journal tables exist'
SELECT tablename
FROM pg_tables
WHERE schemaname = 'prism'
AND tablename LIKE '%_journal'
ORDER BY tablename;

-- Test 4: All journal tables have correct composite key
\echo 'Test 4: Verifying composite primary keys'
SELECT tc.table_name, string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) as pk_columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
AND tc.table_schema = 'prism'
AND tc.table_name LIKE '%_journal'
GROUP BY tc.table_name
ORDER BY tc.table_name;

-- Test 5: Backfill completeness (sample check)
\echo 'Test 5: Backfill completeness verification'
SELECT
    'identity.users' as entity,
    (SELECT COUNT(*) FROM identity.users) as source,
    (SELECT COUNT(DISTINCT id) FROM prism.users_journal) as journaled,
    (SELECT COUNT(*) FROM identity.users) - (SELECT COUNT(DISTINCT id) FROM prism.users_journal) as gap
UNION ALL
SELECT
    'catalog.items',
    (SELECT COUNT(*) FROM catalog.items),
    (SELECT COUNT(DISTINCT id) FROM prism.items_journal),
    (SELECT COUNT(*) FROM catalog.items) - (SELECT COUNT(DISTINCT id) FROM prism.items_journal);

\echo '=== Tests Complete ==='

.NET Integration Tests

For testing journal behavior from the application layer, create integration tests:

// tests/Acsis.Dynaplex.Engines.Identity.Tests/JournalIntegrationTests.cs

[Fact]
public async Task Insert_CreatesJournalEntry()
{
    // Arrange
    await using var context = await CreateDbContextAsync();
    context.SetCurrentUserId(TestUserId);

    var user = new User
    {
        Id = Guid.NewGuid(),
        Email = "test@example.com",
        Username = "testuser"
    };

    // Act
    context.Users.Add(user);
    await context.SaveChangesAsync();

    // Assert
    var journalEntry = await context.UserLogs
        .SingleOrDefaultAsync(j => j.Id == user.Id);

    Assert.NotNull(journalEntry);
    Assert.Equal(1, journalEntry.SeqId);
    Assert.Equal("infinity", journalEntry.ValidTo.ToString());
    Assert.Equal(TestUserId, journalEntry.InitiatedByUserId);
}

[Fact]
public async Task Update_ClosesOldEntryAndCreatesNew()
{
    // Arrange
    await using var context = await CreateDbContextAsync();
    context.SetCurrentUserId(TestUserId);

    var user = new User
    {
        Id = Guid.NewGuid(),
        Email = "original@example.com",
        Username = "testuser"
    };
    context.Users.Add(user);
    await context.SaveChangesAsync();

    // Act
    user.Email = "updated@example.com";
    await context.SaveChangesAsync();

    // Assert
    var entries = await context.UserLogs
        .Where(j => j.Id == user.Id)
        .OrderBy(j => j.SeqId)
        .ToListAsync();

    Assert.Equal(2, entries.Count);

    // Old entry should be closed
    Assert.NotEqual(DateTimeOffset.MaxValue, entries[0].ValidTo);
    Assert.Equal(TestUserId, entries[0].InvalidatedByUserId);

    // New entry should be current
    Assert.Equal("updated@example.com", entries[1].Email);
    Assert.Equal(DateTimeOffset.MaxValue, entries[1].ValidTo);
    Assert.Null(entries[1].InvalidatedByUserId);
}

[Fact]
public async Task Insert_WithoutUserContext_Throws()
{
    // Arrange
    await using var context = await CreateDbContextAsync();
    // Intentionally NOT setting user context

    var user = new User
    {
        Id = Guid.NewGuid(),
        Email = "test@example.com",
        Username = "testuser"
    };

    // Act & Assert
    context.Users.Add(user);
    var exception = await Assert.ThrowsAsync<PostgresException>(
        () => context.SaveChangesAsync());

    Assert.Contains("app.user_id must be set", exception.Message);
}

Test Execution Checklist

Before considering the journal system complete, verify:

  • All journal_writer() triggers are installed (check pg_triggers)
  • All journal tables exist in prism schema
  • All journal tables have composite PK (id, seq_id)
  • INSERT creates journal entry with seq_id=1, valid_to=infinity
  • UPDATE closes old entry (valid_to, invalidated_by_user_id) and creates new entry
  • DELETE closes the current entry
  • Operations without app.user_id are rejected
  • Legacy integer ID entities work correctly
  • Passport (global_id) works correctly
  • All existing entities have been backfilled
  • Temporal query functions return correct point-in-time data
  • Null values are preserved correctly
  • Rapid successive updates are captured correctly