Audit Trail

Overview

Q01 Core APIs automatically maintain a complete audit trail of all operations for compliance, debugging, and accountability. Every record includes who created it, who modified it, and when these actions occurred.

Benefits:

✅ Complete accountability (who did what, when)
✅ Compliance with regulations (GDPR, SOX, HIPAA)
✅ Debugging and troubleshooting
✅ Security investigation
✅ Change history tracking
✅ Automatic (no manual logging needed)

Audit Information Tracked:

User identity (who)
Operation type (what - create/update/delete)
Timestamp (when)
Record affected (which)
IP address and user agent (where/how)
Payload (what changed)

Automatic Audit Fields

Standard Audit Fields

Every dimension table includes audit fields:

CREATE TABLE TB_ANAG_PRD00 (
    PRD_ID VARCHAR(36) PRIMARY KEY,

    -- Business fields
    XPRD01 VARCHAR(255),
    XPRD02 DECIMAL(10,2),

    -- Context fields
    PRD_SOURCE VARCHAR(50),
    PRD_CENTRO_DETT VARCHAR(50),
    PRD_PESO VARCHAR(1),
    PRD_AMBIENTE VARCHAR(20),
    TREC VARCHAR(1),

    -- ⭐ Audit fields (automatic)
    CREATED_BY VARCHAR(255),          -- Who created
    CREATED_AT VARCHAR(14),            -- When created (YYYYMMDDHHmmss)
    UPDATED_BY VARCHAR(255),           -- Who last updated
    UPDATED_AT VARCHAR(14),            -- When last updated
    DELETED_BY VARCHAR(255),           -- Who deleted (soft delete)
    DELETED_AT VARCHAR(14),            -- When deleted

    INDEX idx_created_by (CREATED_BY),
    INDEX idx_updated_by (UPDATED_BY),
    INDEX idx_created_at (CREATED_AT),
    INDEX idx_updated_at (UPDATED_AT)
);

Audit Field Population

Automatically set on every operation:

Create (POST)

INSERT INTO TB_ANAG_PRD00 (
    PRD_ID,
    XPRD01,
    XPRD02,
    CREATED_BY,              -- ⭐ From JWT token
    CREATED_AT,              -- ⭐ Current timestamp
    UPDATED_BY,              -- ⭐ Same as CREATED_BY
    UPDATED_AT               -- ⭐ Same as CREATED_AT
) VALUES (
    'uuid-...',
    'Widget Pro',
    99.99,
    'user@example.com',      -- From session
    '20251219160000',        -- YYYYMMDDHHmmss
    'user@example.com',
    '20251219160000'
);

Update (PUT/PATCH)

UPDATE TB_ANAG_PRD00
SET XPRD02 = 89.99,
    UPDATED_BY = 'admin@example.com',  -- ⭐ From JWT token
    UPDATED_AT = '20251219170000',      -- ⭐ Current timestamp
    TREC = 'M'
WHERE PRD_ID = 'uuid-...';

-- CREATED_BY and CREATED_AT unchanged (original creator preserved)

Delete (Soft)

UPDATE TB_ANAG_PRD00
SET TREC = 'C',
    DELETED_BY = 'admin@example.com',  -- ⭐ From JWT token
    DELETED_AT = '20251219180000',      -- ⭐ Current timestamp
    UPDATED_BY = 'admin@example.com',   -- Also updated
    UPDATED_AT = '20251219180000'
WHERE PRD_ID = 'uuid-...';

CoreWrite Audit Implementation

Automatic Audit Injection

DataStore.php handles audit fields:

// DataStore.php
public function insert(string $dimension, array $data): array {
    $sessionContext = $this->getSessionContext();
    $userId = $sessionContext['user_id'];
    $timestamp = date('YmdHis');

    // ⭐ Inject audit fields automatically
    $data['CREATED_BY'] = $userId;
    $data['CREATED_AT'] = $timestamp;
    $data['UPDATED_BY'] = $userId;
    $data['UPDATED_AT'] = $timestamp;

    // Also inject context fields
    $data["{$dimension}_SOURCE"] = $sessionContext['source'];
    $data["{$dimension}_CENTRO_DETT"] = $sessionContext['centro_dett'];
    $data["{$dimension}_PESO"] = $sessionContext['peso'];
    $data["{$dimension}_AMBIENTE"] = $sessionContext['ambiente'];
    $data['TREC'] = 'N';

    return $this->executeInsert($dimension, $data);
}

public function update(string $dimension, string $id, array $data): array {
    $sessionContext = $this->getSessionContext();
    $userId = $sessionContext['user_id'];
    $timestamp = date('YmdHis');

    // ⭐ Update audit fields
    $data['UPDATED_BY'] = $userId;
    $data['UPDATED_AT'] = $timestamp;
    $data['TREC'] = 'M';

    // CREATED_BY and CREATED_AT NOT modified

    return $this->executeUpdate($dimension, $id, $data);
}

public function delete(string $dimension, string $id, bool $force = false): array {
    $sessionContext = $this->getSessionContext();
    $userId = $sessionContext['user_id'];
    $timestamp = date('YmdHis');

    if ($force) {
        // Force delete - no audit fields (record deleted)
        return $this->executePhysicalDelete($dimension, $id);
    }

    // Soft delete - set audit fields
    $data = [
        'TREC' => 'C',
        'DELETED_BY' => $userId,
        'DELETED_AT' => $timestamp,
        'UPDATED_BY' => $userId,
        'UPDATED_AT' => $timestamp
    ];

    return $this->executeUpdate($dimension, $id, $data);
}

Querying Audit Information

Who Created a Record?

SELECT CREATED_BY, CREATED_AT, XPRD01
FROM TB_ANAG_PRD00
WHERE PRD_ID = '123';

-- Result:
-- CREATED_BY: user@example.com
-- CREATED_AT: 20251219160000
-- XPRD01: Widget Pro

JavaScript:

async function getRecordAudit(dimension, id) {
  const response = await fetch(
    `${apiBase}/api/v4/core/${dimension}/${id}`,
    {
      headers: {
        'Authorization': `Bearer ${token}`
      }
    }
  );

  const result = await response.json();

  return {
    createdBy: result.data.CREATED_BY,
    createdAt: parseTimestamp(result.data.CREATED_AT),
    updatedBy: result.data.UPDATED_BY,
    updatedAt: parseTimestamp(result.data.UPDATED_AT)
  };
}

function parseTimestamp(timestamp) {
  // YYYYMMDDHHmmss → Date
  const year = timestamp.substr(0, 4);
  const month = timestamp.substr(4, 2) - 1;
  const day = timestamp.substr(6, 2);
  const hour = timestamp.substr(8, 2);
  const minute = timestamp.substr(10, 2);
  const second = timestamp.substr(12, 2);

  return new Date(year, month, day, hour, minute, second);
}

// Usage
const audit = await getRecordAudit('PRD', '123');
console.log(`Created by ${audit.createdBy} on ${audit.createdAt}`);
console.log(`Last updated by ${audit.updatedBy} on ${audit.updatedAt}`);

Who Modified a Record?

SELECT UPDATED_BY, UPDATED_AT, CREATED_BY, CREATED_AT
FROM TB_ANAG_PRD00
WHERE PRD_ID = '123';

-- Result shows both creator and last updater

Records Created by User

SELECT PRD_ID, XPRD01, CREATED_AT
FROM TB_ANAG_PRD00
WHERE CREATED_BY = 'user@example.com'
  AND PRD_SOURCE = 'productManagement'
  AND TREC != 'C'
ORDER BY CREATED_AT DESC;

Records Updated Recently

SELECT PRD_ID, XPRD01, UPDATED_BY, UPDATED_AT
FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'productManagement'
  AND UPDATED_AT >= '20251219000000'  -- Today
  AND TREC = 'M'
ORDER BY UPDATED_AT DESC;

Deleted Records

SELECT PRD_ID, XPRD01, DELETED_BY, DELETED_AT
FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'productManagement'
  AND TREC = 'C'
  AND DELETED_AT >= '20251219000000'  -- Today
ORDER BY DELETED_AT DESC;

Outbox Event Audit

Complete Operation Log

OUTBOX table provides detailed event log:

CREATE TABLE OUTBOX (
    OUTBOX_ID VARCHAR(36) PRIMARY KEY,
    EVENT_TYPE VARCHAR(100) NOT NULL,     -- ProductCreated, OrderUpdated, etc.
    AGGREGATE_ID VARCHAR(36) NOT NULL,    -- Record ID
    AGGREGATE_TYPE VARCHAR(10) NOT NULL,  -- Dimension (PRD, ORD, etc.)
    OPERATION VARCHAR(10) NOT NULL,       -- POST, PUT, PATCH, DELETE
    PAYLOAD TEXT NOT NULL,                -- Full record data (JSON)
    METADATA TEXT,                        -- Session context
    USER VARCHAR(255),                    -- ⭐ Who performed operation
    CREATED_AT VARCHAR(14) NOT NULL,      -- ⭐ When operation occurred
    PUBLISHED_AT VARCHAR(14),
    STATUS VARCHAR(20),

    INDEX idx_outbox_user (USER),
    INDEX idx_outbox_created_at (CREATED_AT),
    INDEX idx_outbox_aggregate (AGGREGATE_TYPE, AGGREGATE_ID)
);

Query Operation History

All operations on a record:

SELECT OUTBOX_ID, EVENT_TYPE, OPERATION, USER, CREATED_AT
FROM OUTBOX
WHERE AGGREGATE_TYPE = 'PRD'
  AND AGGREGATE_ID = '123'
ORDER BY CREATED_AT ASC;

-- Result:
-- ProductCreated   POST   user@example.com   20251219160000
-- ProductUpdated   PATCH  admin@example.com  20251219170000
-- ProductDeleted   DELETE admin@example.com  20251219180000

All operations by user:

SELECT AGGREGATE_TYPE, AGGREGATE_ID, EVENT_TYPE, CREATED_AT
FROM OUTBOX
WHERE USER = 'admin@example.com'
  AND CREATED_AT >= '20251219000000'  -- Today
ORDER BY CREATED_AT DESC
LIMIT 100;

Operations by type:

-- All delete operations today
SELECT AGGREGATE_TYPE, AGGREGATE_ID, USER, CREATED_AT
FROM OUTBOX
WHERE OPERATION = 'DELETE'
  AND CREATED_AT >= '20251219000000'
ORDER BY CREATED_AT DESC;

Audit Dashboard Example

User Activity Dashboard

class AuditDashboard {
  async getUserActivity(userId, startDate, endDate) {
    // Query OUTBOX for user's operations
    const response = await fetch(
      `${apiBase}/api/v4/audit/users/${userId}?` +
      `start=${startDate}&end=${endDate}`,
      {
        headers: {
          'Authorization': `Bearer ${token}`
        }
      }
    );

    const data = await response.json();

    return {
      totalOperations: data.total,
      creates: data.operations.filter(op => op.OPERATION === 'POST').length,
      updates: data.operations.filter(op => op.OPERATION === 'PATCH').length,
      deletes: data.operations.filter(op => op.OPERATION === 'DELETE').length,
      operations: data.operations
    };
  }

  async getRecordHistory(dimension, recordId) {
    const response = await fetch(
      `${apiBase}/api/v4/audit/records/${dimension}/${recordId}`,
      {
        headers: {
          'Authorization': `Bearer ${token}`
        }
      }
    );

    const data = await response.json();

    return data.history.map(event => ({
      timestamp: parseTimestamp(event.CREATED_AT),
      user: event.USER,
      operation: event.OPERATION,
      changes: JSON.parse(event.PAYLOAD)
    }));
  }

  renderActivity(activity) {
    return `
      <div class="audit-summary">
        <h3>User Activity</h3>
        <p>Total Operations: ${activity.totalOperations}</p>
        <ul>
          <li>Creates: ${activity.creates}</li>
          <li>Updates: ${activity.updates}</li>
          <li>Deletes: ${activity.deletes}</li>
        </ul>
      </div>

      <table>
        <tr>
          <th>Timestamp</th>
          <th>Operation</th>
          <th>Type</th>
          <th>Record</th>
        </tr>
        ${activity.operations.map(op => `
          <tr>
            <td>${parseTimestamp(op.CREATED_AT).toLocaleString()}</td>
            <td>${op.OPERATION}</td>
            <td>${op.AGGREGATE_TYPE}</td>
            <td>${op.AGGREGATE_ID}</td>
          </tr>
        `).join('')}
      </table>
    `;
  }
}

Compliance Requirements

Right to be forgotten:

-- Remove user's personal data from audit fields
UPDATE TB_ANAG_PRD00
SET CREATED_BY = 'deleted-user',
    UPDATED_BY = CASE WHEN UPDATED_BY = 'user@example.com' THEN 'deleted-user' ELSE UPDATED_BY END
WHERE CREATED_BY = 'user@example.com';

-- Also update OUTBOX
UPDATE OUTBOX
SET USER = 'deleted-user'
WHERE USER = 'user@example.com';

Data access request:

-- Export all operations by user
SELECT *
FROM OUTBOX
WHERE USER = 'user@example.com'
ORDER BY CREATED_AT DESC;

SOX Compliance

Audit trail requirements:

✅ Who performed operation (CREATED_BY, UPDATED_BY)
✅ When operation occurred (CREATED_AT, UPDATED_AT)
✅ What changed (OUTBOX PAYLOAD)
✅ Cannot be modified (OUTBOX is append-only)
✅ Complete history (all operations logged)

HIPAA Compliance

Access logging:

-- Log all access to sensitive records
INSERT INTO ACCESS_LOG (
    USER, RESOURCE_TYPE, RESOURCE_ID, ACTION, TIMESTAMP
) VALUES (
    'doctor@hospital.com', 'PATIENT', '123', 'READ', '20251219160000'
);

Best Practices

✅ DO:

Trust automatic audit fields:

// ✅ Good - audit fields handled automatically
await createProduct({ XPRD01: 'Widget' });
// CREATED_BY, CREATED_AT set automatically

Query audit fields for debugging:

-- ✅ Good - investigate who changed record
SELECT UPDATED_BY, UPDATED_AT, XPRD02
FROM TB_ANAG_PRD00
WHERE PRD_ID = '123';

Use OUTBOX for complete history:

-- ✅ Good - full operation history
SELECT * FROM OUTBOX
WHERE AGGREGATE_TYPE = 'PRD'
  AND AGGREGATE_ID = '123'
ORDER BY CREATED_AT ASC;

Preserve audit trail on delete:

-- ✅ Good - soft delete preserves audit
DELETE /api/v4/core/PRD/123
-- Sets DELETED_BY, DELETED_AT, TREC='C'

❌ DON'T:

Don't try to override audit fields:

// ❌ Bad - audit fields are read-only
await createProduct({
  XPRD01: 'Widget',
  CREATED_BY: 'other@example.com'  // Validation error!
});

Don't force delete unnecessarily:

# ❌ Bad - destroys audit trail
DELETE /api/v4/core/PRD/123?force=true
# Record physically deleted, no DELETED_BY/DELETED_AT

Don't modify OUTBOX:

-- ❌ Bad - OUTBOX is append-only
UPDATE OUTBOX SET USER = 'anonymous' WHERE ...;

Don't store sensitive data in OUTBOX:

// ⚠️ Caution - OUTBOX stores full payload
await createUser({
  username: 'user@example.com',
  password: 'plaintext'  // Don't do this!
});
// Password in OUTBOX forever

Summary

✅ Automatic audit fields (CREATED_BY, UPDATED_BY, etc.)
✅ Every operation tracked with user and timestamp
✅ OUTBOX provides complete operation history
✅ Audit fields read-only (cannot be overridden)
✅ Soft delete preserves audit trail
✅ Compliance with GDPR, SOX, HIPAA
✅ Query audit history for debugging and investigation

Key Takeaways:

All records include audit fields automatically
Audit fields populated from JWT token (user_id)
Cannot override or modify audit fields
Soft delete preserves audit trail
OUTBOX provides complete operation history
Query audit fields for investigation
Comply with regulatory requirements

Audit Fields:

Field	Purpose	Set On	Modified On
CREATED_BY	Who created	POST	Never
CREATED_AT	When created	POST	Never
UPDATED_BY	Who last updated	POST, PUT, PATCH, DELETE	PUT, PATCH, DELETE
UPDATED_AT	When last updated	POST, PUT, PATCH, DELETE	PUT, PATCH, DELETE
DELETED_BY	Who deleted	DELETE (soft)	Never
DELETED_AT	When deleted	DELETE (soft)	Never

Audit Query Examples:

-- Who created record?
SELECT CREATED_BY FROM TB_ANAG_PRD00 WHERE PRD_ID = '123';

-- When was record last updated?
SELECT UPDATED_BY, UPDATED_AT FROM TB_ANAG_PRD00 WHERE PRD_ID = '123';

-- Complete operation history
SELECT * FROM OUTBOX WHERE AGGREGATE_TYPE = 'PRD' AND AGGREGATE_ID = '123';

Security Overview - Multi-level security
Authentication - JWT tokens with user_id
Delete Operations - Soft vs force delete
Outbox Pattern - Event sourcing

Audit Trail

Overview​

Automatic Audit Fields​

Standard Audit Fields​

Audit Field Population​

Create (POST)​

Update (PUT/PATCH)​

Delete (Soft)​

CoreWrite Audit Implementation​

Automatic Audit Injection​

Querying Audit Information​

Who Created a Record?​

Who Modified a Record?​

Records Created by User​

Records Updated Recently​

Deleted Records​

Outbox Event Audit​

Complete Operation Log​

Query Operation History​

Audit Dashboard Example​

User Activity Dashboard​

Compliance Requirements​

GDPR Compliance​

SOX Compliance​

HIPAA Compliance​

Best Practices​

✅ DO:​

❌ DON'T:​

Summary​

Related Concepts​