Database Schema

The command_log table

The command_log table stores traced command executions.

Schema

create table command
(
    id                 bigint generated by default as identity (start with 1),
    tenant_id          text,
    user_id            text,
    cmd_uuid           uuid    not null,
    cmd_source_ref     text,
    cmd_class          text,
    cmd_version        text                     default 'v1',
    state              text    not null,
    dry_run            boolean,
    attributes         jsonb,
    importance         text,
    retention          text,

    http_method        text,
    http_status        text,
    http_status_code   smallint,

    problem_code       text,
    problem            jsonb,

    cmd_body           jsonb,

    created_at         timestamp with time zone default now(),
    started_at         timestamp with time zone,
    ended_at           timestamp with time zone,
    duration_in_millis integer,
    protected_at       timestamp with time zone,
    archived_at        timestamp with time zone,

    version            integer not null         default 0,
    constraint pk_command primary key (id)
);

Column reference

id

Auto-generated primary key.

tenant_id

Tenant scope. Populated from TenantIdAware.getTenantId().

user_id

User scope. Populated from UserIdAware.getUserId().

cmd_uuid

Unique command identifier (required). Generated by the tracing interceptor.

cmd_source_ref

Optional reference to the originating source (e.g., a correlation ID or external request ID).

cmd_class

Fully qualified class name of the command (e.g., com.example.order.CreateOrderCommand).

cmd_version

Defaults to v1.

state

Execution outcome. Values: Created, Cancelled, Successful, Rejected, Conflict, Failed.

Derived from HTTP status: 2xx → Successful, 3xx → Cancelled, 4xx → Rejected, 5xx → Failed.

dry_run

true if the command implements DryRunEnabled and was executed in dry-run mode.

attributes

JSONB map of custom key-value pairs from AttributesEnabled.attributes().

importance

Low, Normal, or High. Controls query filtering and retention policy.

retention

ShortLived, LongLived, or Permanent. Derived from importance: Low → ShortLived, Normal → LongLived, High → Permanent.

http_method

HTTP method of the request (GET, POST, PUT, DELETE, PATCH).

http_status

HTTP status name (e.g., OK, BAD_REQUEST).

http_status_code

Numeric HTTP status code (e.g., 200, 400, 500).

problem_code

Application-specific error code from the Problem response body.

problem

Full Problem response body as JSONB.

cmd_body

Command payload as JSONB. Excluded when CommandTracingOption.ExcludeCmdBody is set.

created_at

Timestamp when the row was inserted (defaults to now()).

started_at

Timestamp when command execution started.

ended_at

Timestamp when command execution completed.

duration_in_millis

Execution duration in milliseconds.

protected_at

Optional timestamp for retention protection.

archived_at

Optional timestamp marking the command as archived.

Indexes on command_log

The command_log table grows append-only and is read in many shapes — single-row lookups by natural key, per-tenant activity feeds, time-windowed support queries, attribute searches. A handful of cheap, single-purpose indexes covers them all by composing through the planner’s BitmapAnd, rather than one bloated multi-column composite.

This page documents the indexes shipped by the library and the query shape each one serves.

Cheat sheet

Index Serves Notes

pk_command_log (id)

Primary key, surrogate FKs.

Always present.

ux_command_log_cmd_uuid (UNIQUE on cmd_uuid)

findOne(tenantId, cmdUuid), deep-link URLs, log/audit citations.

Also enforces uniqueness — a duplicate-insert bug would otherwise silently corrupt the audit trail.

ix_command_log_tenant_id_started_at (tenant_id, started_at DESC)

Per-tenant activity-feed reads, including cursor pagination (started_at < :before) and the polling started_at > :since path.

DESC matches the natural ORDER BY started_at DESC LIMIT N, so the planner walks the index in storage order and stops at LIMIT — no Sort node.

ix_command_log_started_at (started_at DESC)

Cross-tenant time-windowed queries (support tooling, developer console).

Foundation for the global query path. Composes with the GIN(attributes) index via BitmapAnd for "attribute match within a time range".

ix_command_log_attributes_gin (GIN on attributes, jsonb_path_ops)

attributes @> '{…}' containment lookups.

jsonb_path_ops is ~2× smaller than the default jsonb_ops and supports exactly the @> operator. Skip this index if your queries need the full ?, ?&, `?

` operator set.

ix_command_log_high_started_at (partial (started_at DESC) WHERE importance = 'High')

"Show me only the High-importance commands" dedicated views.

Optional. Worth it only if such a view is hot — High is typically ~15% of rows, so a partial index is ~6× smaller than the full one.

ix_command_log_cmd_class_started_at (cmd_class, started_at DESC)

cmd_class IN (…) filters with long-tail classes.

Why some columns are deliberately not indexed

cmd_body

The largest jsonb column (the full request payload, often TOAST-stored). A GIN on it would dwarf the GIN on attributes (200–400 MB on a 1 GB table) and meaningfully raise per-row insert cost. Queries against cmd_body are typically occasional — the acceptable pattern is to narrow first via an indexed column (tenant_id, started_at, cmd_class, attributes), then let cmd_body @> '{…}' be a recheck on the candidate set.

If a recurring cmd_body query does emerge, prefer either promoting the relevant field into attributes at write time (so the GIN(attributes) covers it) or building a partial GIN scoped to a narrow cmd_class.

problem

Same reasoning as cmd_body — large jsonb, infrequently queried. Filter by problem_code (which is plain text) instead.

Composition by BitmapAnd

These indexes are designed to compose, not to stand alone. Common shapes the planner combines:

attributes @> '{…}' + time range

GIN(attributes) BitmapAnd (started_at DESC).

cmd_class IN (…) + attributes @> '{…}'

GIN(attributes) BitmapAnd (cmd_class, started_at).

per-tenant + attributes @> '{…}'

GIN(attributes) BitmapAnd (tenant_id, started_at).

importance = 'High' + per-tenant

partial(started_at DESC where importance='High') BitmapAnd (tenant_id, started_at).

If a query plan shows a Seq Scan on command_log despite filters that should hit one of these indexes, run EXPLAIN (ANALYZE, BUFFERS) and check whether table statistics are stale (ANALYZE command_log) or whether the filter constants are too unselective for the index to win against the seq scan cost estimate.

Build cost and concurrency

All index migrations shipped by the library use CREATE INDEX CONCURRENTLY. This is required: command_log is write-hot, and a regular CREATE INDEX would take ACCESS EXCLUSIVE and block every traced command for the duration of the build.

CREATE INDEX CONCURRENTLY cannot run inside a transaction. Each index migration ships a companion .sql.conf with executeInTransaction=false so Flyway runs it without its default transaction wrap.

If a CONCURRENT build fails midway, PostgreSQL leaves an INVALID index behind that does not auto-clean. Detect with:

select indexrelid::regclass, indisvalid
from   pg_index
where  indrelid = 'command_log'::regclass and not indisvalid;

Drop the listed names with DROP INDEX CONCURRENTLY and re-run the migration.

Installing migrations

The conta-command-postgresql module ships Flyway migrations as resources. To use them in your project, extract them into your own migrations directory using the no.conta.archive-extract Gradle plugin.

Gradle setup

Add the plugin to your db module (the module that applies java-library and owns your migrations). The extracted SQL files are placed in src/main/resources and become part of the published jar, so downstream modules that depend on your db module get the migrations automatically.

plugins {
    id 'no.conta.archive-extract'
}

archiveExtract {
    commandMigrations {
        from "no.conta.command:conta-command-postgresql:${contaCommandVersion}" as String
        include 'migrations/*.sql'
        files {
            rename 'migrations/V001__create_table_command.sql', 'V1_0_0_010__create_table_command.sql'
            rename 'migrations/V002__rename_table_command_to_command_log.sql', 'V1_0_0_011__rename_table_command_to_command_log.sql'
            rename 'migrations/V003__create_helper_functions.sql', 'V1_0_0_012__create_helper_functions.sql'
        }
        into 'src/main/resources/com/example/migrations'
    }
}

Rename each upstream migration to match your project’s Flyway versioning scheme. This ensures the command log migrations run in the right order relative to your own migrations.

Every matched file must have a rename or skip entry. When you upgrade the library and a new migration is added, the build fails until you explicitly handle it — no migration is silently included or ignored.

Skipping migrations

If a migration is not needed (e.g., you created the table manually or the helper functions are not wanted), use skip instead of rename:

archiveExtract {
    commandMigrations {
        from "no.conta.command:conta-command-postgresql:${contaCommandVersion}" as String
        include 'migrations/*.sql'
        files {
            rename 'migrations/V001__create_table_command.sql', 'V1_0_0_010__create_table_command.sql'
            rename 'migrations/V002__rename_table_command_to_command_log.sql', 'V1_0_0_011__rename_table_command_to_command_log.sql'
            skip 'migrations/V003__create_helper_functions.sql'
        }
        into 'src/main/resources/com/example/migrations'
    }
}