[ Updating Audits ] - Auditing UUIDs?

[KofTwentyTwo]

Auditing UUIDs? Yes, Now You Can.

The QQQ audit system now supports tables with any primary key type - not just integers.

This landed in [issue #322].

Why This Matters

Not every table uses integer primary keys. UUIDs are common in distributed systems. External integrations hand you string IDs. Some domains have natural keys that aren't numbers.

UUIDs are also a common requirement for compliance frameworks like HIPAA and SOC2, where predictable sequential IDs can leak information about record counts or creation timing. If your security team mandates UUIDs, your audit system needs to keep up.

The audit system was built for integer PKs. Now it handles whatever you throw at it.

How It Works

AuditsMetaDataProvider accepts a new recordIdType configuration:

// INTEGER mode (default) - optimized for integer PKs
new AuditsMetaDataProvider()
   .defineAll(qInstance, backendName, null);

// STRING mode - supports any primary key type
new AuditsMetaDataProvider()
   .withRecordIdType(QFieldType.STRING)
   .defineAll(qInstance, backendName, null);

In STRING mode, the audit table's recordId column stores text values. Integer PKs convert automatically, so you can mix integer and string-keyed tables in the same application. The system handles the type conversion transparently.

Example: Document Management

Say you're building a document management system where documents have UUID identifiers:

new QTableMetaData()
   .withName("document")
   .withPrimaryKeyField("id")
   .withField(new QFieldMetaData("id", QFieldType.STRING))  // UUID as string
   .withField(new QFieldMetaData("title", QFieldType.STRING))
   .withField(new QFieldMetaData("content", QFieldType.TEXT));

With STRING mode enabled, every insert, update, and delete on this table gets a complete audit trail - who changed what, when, and what the values were before and after.

What Changed Under the Hood

• AuditsMetaDataProvider - new withRecordIdType() fluent configuration
• AuditSingleInput - recordId field now accepts Serializable (Integer, String, UUID)
• DMLAuditAction - runtime type detection matches your configuration
• GetAuditsForRecordProcess - retrieves audits by string or integer record ID

DDL Examples

INTEGER Mode (Default)

CREATE TABLE audit (
   id INT AUTO_INCREMENT PRIMARY KEY,
   audit_table_id INT NOT NULL,
   audit_user_id INT,
   timestamp DATETIME NOT NULL,
   record_id INT,
   message VARCHAR(250)
);

STRING Mode

CREATE TABLE audit (
   id INT AUTO_INCREMENT PRIMARY KEY,
   audit_table_id INT NOT NULL,
   audit_user_id INT,
   timestamp DATETIME NOT NULL,
   record_id VARCHAR(36),
   message VARCHAR(250)
);

The VARCHAR(36) accommodates standard UUID format (32 hex chars + 4 hyphens). Adjust the length if your string keys are longer.

Migrating an Existing Deployment

If you're switching from INTEGER to STRING mode on an existing system:

-- 1. Alter the column type
ALTER TABLE audit MODIFY COLUMN record_id VARCHAR(36);

-- 2. Update your AuditsMetaDataProvider configuration
-- (in Java, add .withRecordIdType(QFieldType.STRING))

Existing integer values in the record_id column will be preserved and work correctly - the database handles the implicit conversion when reading.

Suggested Indexes

Audit tables grow fast. Without indexes, queries slow to a crawl. Here's what we recommend:

-- Look up audits for a specific record (the most common query)
CREATE INDEX idx_audit_table_record ON audit (audit_table_id, record_id);

-- Filter by time range
CREATE INDEX idx_audit_timestamp ON audit (timestamp);

-- Find what a specific user changed
CREATE INDEX idx_audit_user ON audit (audit_user_id);

-- Combined index for "show me this record's history" queries
CREATE INDEX idx_audit_lookup ON audit (audit_table_id, record_id, timestamp);

The composite index on (audit_table_id, record_id, timestamp) covers the most common access pattern: fetching the audit trail for a single record, sorted by time. If you only add one index, make it that one.

Backwards Compatibility

This is a pure addition. The default remains INTEGER. Existing deployments continue working without any changes. No migrations required unless you want STRING mode.

Which Mode Should You Use?

Stick with INTEGER (default) if:

• All your audited tables use integer primary keys
• You want the most compact audit storage
• You're already running in production with no issues

Switch to STRING if:

• Any audited table uses UUID, GUID, or string primary keys
• You integrate with external systems that provide string identifiers
• You want maximum flexibility for future tables

The storage difference is minimal. A few extra bytes per audit record won't matter for most applications. Choose based on your data model, not premature optimization.