# DEBT-014 + DEBT-029 Implementation Guide

**Updated:** 2026-08-11  
**Status:** Framework Documented (Ready for Implementation)

---

## DEBT-014: Duplicate & Reconciliation Tracking (2 pts, Medium/Medium)

### Current State

```csharp
// MetricsSql.cs (lines 77-95)
public async Task<(int Detected, int Resolved, DateTime LastCheck)?> GetDuplicateDetectionAsync()
{
    // Returns null until audit infrastructure is extended
    return null;
}

public async Task<(int Detected, int Resolved, List<string> Pending)?> GetReconciliationBreaksAsync()
{
    // Returns null until audit trail is enriched
    return null;
}
```

### What's Needed

#### 1. Create `operation_audit_trail` Migration

**File:** `src/KArtSell.DbMigrator/migrations/004X_create_operation_audit_trail.sql`

```sql
CREATE TABLE IF NOT EXISTS compliance.operation_audit_trail (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type VARCHAR(50) NOT NULL, -- DUPLICATE_DETECTED, RECONCILIATION_BREAK_DETECTED
    correlation_id UUID NOT NULL,
    entity_type VARCHAR(50) NOT NULL, -- 'outbox_message', 'evidence_snapshot'
    entity_id UUID NOT NULL,
    details JSONB,
    detected_at TIMESTAMP NOT NULL DEFAULT NOW(),
    resolved_by UUID,
    resolved_at TIMESTAMP,
    published_at TIMESTAMP NOT NULL DEFAULT NOW(),
    revision INT NOT NULL DEFAULT 1,
    
    CONSTRAINT fk_compliance_audit_trail_resolver 
        FOREIGN KEY (resolved_by) REFERENCES model_operations.approvers(id)
);

CREATE INDEX idx_audit_trail_event_type ON compliance.operation_audit_trail(event_type, detected_at DESC);
CREATE INDEX idx_audit_trail_correlation ON compliance.operation_audit_trail(correlation_id);
```

#### 2. Hook OutboxPollerJob to Log Duplicates

**File:** `src/KArtSell.Host/Jobs/OutboxPollerJob.cs`

```csharp
public async Task ExecuteAsync(...)
{
    // After publishing outbox messages...
    var duplicates = await _outbox.GetDuplicatesAsync(window, ct);
    
    foreach (var dup in duplicates)
    {
        await _auditSql.InsertOperationAuditTrailAsync(
            eventType: "DUPLICATE_DETECTED",
            entityType: "outbox_message",
            entityId: dup.Id,
            correlationId: dup.CorrelationId,
            details: new { attemptCount = dup.AttemptCount, lastAttemptAt = dup.LastAttemptAt });
    }
}
```

#### 3. Implement MetricsSql Queries

**File:** `src/KArtSell.BuildingBlocks/Observability/MetricsSql.cs`

```csharp
public async Task<(int Detected, int Resolved, DateTime LastCheck)?> GetDuplicateDetectionAsync(...)
{
    const string sql = """
        SELECT
            COUNT(*) as detected,
            COUNT(CASE WHEN resolved_at IS NOT NULL THEN 1 END) as resolved,
            MAX(detected_at) as last_check
        FROM compliance.operation_audit_trail
        WHERE event_type = 'DUPLICATE_DETECTED'
          AND detected_at >= @sevenDaysAgo
          AND published_at <= @now
        """;
    
    var now = _clock.UtcNow.UtcDateTime;
    var result = await connection.QueryFirstOrDefaultAsync<(int, int, DateTime)?>(
        sql,
        new { now, sevenDaysAgo = now.AddDays(-7) });
    
    return result;
}

public async Task<(int Detected, int Resolved, List<string> Pending)?> GetReconciliationBreaksAsync(...)
{
    const string sql = """
        SELECT
            COUNT(*) as detected,
            COUNT(CASE WHEN resolved_at IS NOT NULL THEN 1 END) as resolved,
            STRING_AGG(DISTINCT (details->>'reason'), ', ') as reasons
        FROM compliance.operation_audit_trail
        WHERE event_type = 'RECONCILIATION_BREAK_DETECTED'
          AND detected_at >= @sevenDaysAgo
          AND published_at <= @now
        """;
    
    // ... similar structure
}
```

### Success Criteria

- [ ] Migration 004X creates `operation_audit_trail` table
- [ ] Migration passes fresh-install + idempotent re-run tests
- [ ] OutboxPollerJob logs duplicates on each run
- [ ] GetDuplicateDetectionAsync returns real counts (not null)
- [ ] GetReconciliationBreaksAsync returns real counts (not null)
- [ ] Dashboard observability queries reflect actual duplicates/breaks

---

## DEBT-029: LogAuditEventCommandHandler Cross-Integration (3 pts, High/Medium)

### Current State

```csharp
// VS-27 audit trail infrastructure exists:
// - LogAuditEventCommandHandler (compliance/LogAuditEventHandler.cs)
// - compliance.audit_event_types seed data
// - Tests pass for AuditSql.InsertAuditEventAsync directly

// BUT: No slice actually calls LogAuditEventCommandHandler
// - ApprovalWorkflow/Handlers.cs doesn't call it
// - TradeExecution/TradeHandlers.cs doesn't call it
// - SellDecision handlers don't call it
// - PortfolioReconciliation/ReconcileTradeHandler doesn't call it

// Result: Audit trail is empty in production despite infrastructure being complete
```

### What's Needed

#### Strategy: Event-Driven Integration (Preferred)

Instead of calling `LogAuditEventCommandHandler` directly from each handler, emit events via Outbox and let a consumer job log them:

**File:** `src/KArtSell.Host/Consumers/AuditTrailConsumer.cs`

```csharp
public sealed class AuditTrailConsumer : IOutboxEventConsumer
{
    public async Task ConsumeAsync(OutboxEvent e, CancellationToken ct)
    {
        // Map outbox events to audit trail entries
        var auditEntry = e.EventType switch
        {
            "APPROVAL_PROPOSED" => new AuditEntry(
                EventType: "APPROVAL_PROPOSED",
                EntityId: e.EntityId,
                UserId: e.ActedBy,
                Details: JsonSerializer.Serialize(e.Payload)),
            "APPROVAL_APPROVED" => ...,
            "MODEL_ACTIVATED" => ...,
            "SELL_EXECUTED" => ...,
            _ => null,
        };
        
        if (auditEntry != null)
        {
            await _auditSql.InsertAuditEventAsync(auditEntry, ct);
        }
    }
}
```

**Registration:** `src/KArtSell.Host/Program.cs`

```csharp
// Register consumer
builder.Services.AddScoped<AuditTrailConsumer>();

// Wire to OutboxPollerJob
// (already exists; just add AuditTrailConsumer to the list of consumers)
```

#### Alternative: Direct Handler Integration (If Events Not Available)

If a handler doesn't emit an event, call directly:

**File:** `src/KArtSell.Modules.ModelOperations/Features/ApprovalWorkflow/ApproveApprovalHandler.cs`

```csharp
public async Task HandleAsync(ApproveApprovalCommand cmd, ...)
{
    // ... approve logic ...
    
    // Log to audit trail
    await _auditSql.InsertAuditEventAsync(new AuditEntry(
        EventType: "APPROVAL_APPROVED",
        EntityId: cmd.ProposalId,
        UserId: cmd.ApproverId,
        Details: JsonSerializer.Serialize(evidence)), ct);
}
```

### Implementation Order

1. **Phase 1:** Wire `AuditTrailConsumer` to existing Outbox events
   - ApprovalWorkflow: APPROVAL_PROPOSED, APPROVAL_APPROVED, MODEL_ACTIVATED
   - TradeExecution: TRADE_SUBMITTED, TRADE_CONFIRMED
   - SellDecision: SELL_DECISION_MADE
   
2. **Phase 2:** Add direct logging for handlers without Outbox events
   - PortfolioReconciliation: RECONCILIATION_COMPLETED
   - Any other missing slices

### Success Criteria

- [ ] AuditTrailConsumer integrated with OutboxPollerJob
- [ ] At least 5 distinct event types logged to `compliance.audit_events`
- [ ] Audit dashboard shows activity from all slices
- [ ] GDPR/compliance queries return non-empty results
- [ ] No duplicate audit entries (idempotent consumer)

---

## Integration Timeline

**Q3 2026 (Current):**
- ✅ DEBT-030: HomePage Framework (Completed)
- ⏳ DEBT-014: audit_trail infrastructure (Ready for PR)
- ⏳ DEBT-029: AuditTrailConsumer + event mapping (Ready for PR)

**Q4 2026:**
- Complete slice-by-slice audit logging integration
- Add GDPR data export endpoint
- Compliance dashboard reports

---

## Related

- DEBT-009: PBO/DSR simplified analytics (Gate 3 testing)
- DEBT-010: Model prediction logic fixes
- DEBT-031: Workspace dirty-guard dirty-state bridge
- DEBT-032: Frontend `.js`/`.vue.js` twin cleanup