Phase 2 Audit Migration Guide

# Review changesets that will be applied
./gradlew liquibaseStatus

# Apply changesets (creates new Phase 2 tables)
./gradlew liquibaseUpdate

CREATE TABLE audit_event ...
CREATE TABLE audit_snapshot ...
CREATE TABLE audit_event_snapshot ...
CREATE TABLE audit_change ...

# application.yml
audit:
  phase2:
    enabled: true          # Enable Phase 2 audit writer
    dual-write: true       # Write to both Phase 1 and Phase 2

./gradlew bootRun

-- Should have matching counts for new events
SELECT COUNT(*) FROM audit_log WHERE occurred_at > NOW() - INTERVAL 1 HOUR;
SELECT COUNT(*) FROM audit_event WHERE occurred_at > NOW() - INTERVAL 1 HOUR;

# Apply migration changesets (backfill historical data)
./gradlew liquibaseUpdate -PliquibaseContexts=migration

# This will execute:
# 1. Migrate events from audit_log to audit_event
# 2. Extract and deduplicate snapshots
# 3. Link events to snapshots
# 4. Create validation views

-- MySQL: Migrate in batches of 10,000 events
SET @batch_size = 10000;
SET @offset = 0;

-- Run in loop until no more rows
INSERT INTO audit_event (...)
SELECT ... FROM audit_log
WHERE id > @offset AND id <= @offset + @batch_size
...

-- Check migration completeness
SELECT * FROM v_audit_migration_stats;

metric                          | count
--------------------------------|-------
Phase 1 Events                  | 50000
Phase 2 Events                  | 50000  ← Should match Phase 1
Phase 1 Payloads (old)          | 40000
Phase 1 Payloads (new)          | 45000
Phase 2 Unique Snapshots        | 30000  ← Deduplicated (60% savings)
Phase 2 Total Snapshot Refs     | 85000  ← Sum of old + new payloads

SELECT * FROM v_audit_deduplication_stats;

unique_snapshots | total_references | savings_pct
-----------------|------------------|-------------
30000            | 85000            | 64.71

-- Get timeline for a specific entity
SELECT ae.occurred_at, ae.operation, ae.username
FROM audit_event ae
WHERE ae.entity_type = 'AppPort' AND ae.entity_id = '1'
ORDER BY ae.occurred_at ASC;

-- Verify changes were tracked (for new events only)
SELECT COUNT(*) FROM audit_change;
-- Note: Only events created after Phase 2 activation will have field changes

-- Check for discrepancies in recent events
SELECT
    (SELECT COUNT(*) FROM audit_log WHERE occurred_at > NOW() - INTERVAL 1 DAY) AS phase1_count,
    (SELECT COUNT(*) FROM audit_event WHERE occurred_at > NOW() - INTERVAL 1 DAY) AS phase2_count;

-- Both counts should be identical

-- Check timeline query performance (should be <100ms)
EXPLAIN SELECT * FROM audit_event
WHERE entity_type = 'AppPort' AND entity_id = '123'
ORDER BY occurred_at DESC;

-- Verify index usage: should use idx_event_entity_history

# application.yml
audit:
  phase2:
    enabled: true
    dual-write: false      # Stop writing to Phase 1

./gradlew bootRun

-- New events should only appear in Phase 2
SELECT COUNT(*) FROM audit_event WHERE occurred_at > NOW() - INTERVAL 5 MINUTE;
-- Should show new events

SELECT COUNT(*) FROM audit_log WHERE occurred_at > NOW() - INTERVAL 5 MINUTE;
-- Should show 0 (no new events)

-- Create archive tables
CREATE TABLE audit_log_archive AS SELECT * FROM audit_log;
CREATE TABLE audit_log_payload_archive AS SELECT * FROM audit_log_payload;

-- Verify archive
SELECT COUNT(*) FROM audit_log_archive;
SELECT COUNT(*) FROM audit_log;
-- Counts should match

-- Drop old tables (optional, only after verification)
-- DROP TABLE audit_log_payload;
-- DROP TABLE audit_log;

# application.yml
audit:
  phase2:
    enabled: false         # Disable Phase 2
    dual-write: false

# Step 1: Re-enable dual-write pointing to Phase 1
audit:
  phase2:
    enabled: false
    dual-write: false

-- Step 2: Drop Phase 2 tables (rollback changesets)
./gradlew liquibaseRollbackCount -PliquibaseRollbackCount=10

-- Or manually:
DROP VIEW v_audit_deduplication_stats;
DROP VIEW v_audit_migration_stats;
DROP TABLE audit_event_snapshot;
DROP TABLE audit_change;
DROP TABLE audit_snapshot;
DROP TABLE audit_event;

-- Process 10,000 events at a time
INSERT INTO audit_event (...)
SELECT ... FROM audit_log
WHERE id BETWEEN 1 AND 10000;

INSERT INTO audit_event (...)
SELECT ... FROM audit_log
WHERE id BETWEEN 10001 AND 20000;
-- Repeat...

ERROR: Duplicate entry 'abc123...' for key 'content_hash'

-- Clear partial migration data
DELETE FROM audit_event_snapshot WHERE event_id IN (SELECT id FROM audit_log);
DELETE FROM audit_snapshot WHERE content_hash IN (
    SELECT old_payload_hash FROM audit_log_payload
    UNION
    SELECT new_payload_hash FROM audit_log_payload
);
DELETE FROM audit_event WHERE id IN (SELECT id FROM audit_log);

-- Re-run migration
./gradlew liquibaseUpdate -PliquibaseContexts=migration

EXPLAIN SELECT * FROM audit_event
WHERE entity_type = 'AppPort' AND entity_id = '123'
ORDER BY occurred_at DESC;

SHOW INDEXES FROM audit_event WHERE Key_name = 'idx_event_entity_history';

CREATE INDEX idx_event_entity_history
ON audit_event (entity_type, entity_id, occurred_at DESC);

-- Check if snapshots are truly different
SELECT content_hash, ref_count
FROM audit_snapshot
ORDER BY ref_count DESC
LIMIT 10;

SELECT 'Phase 1' AS phase, COUNT(*) AS events FROM audit_log
UNION ALL
SELECT 'Phase 2' AS phase, COUNT(*) AS events FROM audit_event;

SELECT
    COUNT(*) AS unique_snapshots,
    SUM(ref_count) AS total_references,
    ROUND(100.0 * (1 - COUNT(*) / SUM(ref_count)), 2) AS savings_pct
FROM audit_snapshot;

-- Verify events have proper ordering
SELECT
    entity_type,
    entity_id,
    COUNT(*) AS event_count,
    MIN(occurred_at) AS first_change,
    MAX(occurred_at) AS last_change
FROM audit_event
GROUP BY entity_type, entity_id
HAVING COUNT(*) > 1
ORDER BY event_count DESC
LIMIT 10;

-- Verify events are linked to snapshots
SELECT
    COUNT(DISTINCT ae.id) AS events_with_snapshots,
    (SELECT COUNT(*) FROM audit_event) AS total_events
FROM audit_event ae
JOIN audit_event_snapshot aes ON ae.id = aes.event_id;