Example Queries

Useful SQL queries against the command_log table for debugging, monitoring, and reporting. Replace the example command class names and table references with your own.

Helper functions

The following SQL functions are provided as convenience wrappers for session-scoped parameters. Set them once, then use the getters in your queries.

Use cmd_set to configure multiple parameters in one call — all parameters are optional and named:

select cmd_set(
    tenant_id := 'a1b2c3d4-...',
    class_pattern := '%.CreateOrderCommand',
    since := '2025-03-01'
);

Or use the individual setters:

select cmd_set_tenant_id(tenant_id := 'a1b2c3d4-...');
select cmd_set_class_pattern(class_pattern := '%.CreateOrderCommand');
select cmd_set_classes('com.example.submission.InitiateSubmissionCommand', 'com.example.submission.SendToSigningCommand');
select cmd_set_user_id(user_id := 'user-123');
select cmd_set_uuid(cmd_uuid := 'a1b2c3d4-...');
select cmd_set_since(since := '2025-03-01');

cmd_set

-- set multiple session parameters at once (all optional, named params)
create function cmd_set(
    tenant_id uuid default null,
    class_pattern text default null,
    user_id text default null,
    cmd_uuid uuid default null,
    since timestamptz default null
) returns void
    language sql
    as $$
    select case when tenant_id is not null then set_config('cmd.tenant_id', tenant_id::text, false) end;
    select case when class_pattern is not null then set_config('cmd.class_pattern', class_pattern, false) end;
    select case when user_id is not null then set_config('cmd.user_id', user_id, false) end;
    select case when cmd_uuid is not null then set_config('cmd.uuid', cmd_uuid::text, false) end;
    select case when since is not null then set_config('cmd.since', since::text, false) end;
$$;

cmd_tenant_id

-- tenant_id (uuid)
create function cmd_set_tenant_id(tenant_id uuid) returns void
    language sql
    as $$
    select set_config('cmd.tenant_id', tenant_id::text, false)
$$;

create function cmd_tenant_id() returns uuid
    language sql stable
    as $$
    select current_setting('cmd.tenant_id')::uuid
$$;

cmd_class_pattern

-- class_pattern (text, for use with LIKE)
create function cmd_set_class_pattern(class_pattern text) returns void
    language sql
    as $$
    select set_config('cmd.class_pattern', class_pattern, false)
$$;

create function cmd_class_pattern() returns text
    language sql stable
    as $$
    select current_setting('cmd.class_pattern')
$$;

cmd_classes

-- cmd_classes (text[], for use with IN)
create function cmd_set_classes(variadic classes text[]) returns void
    language sql
    as $$
    select set_config('cmd.classes', array_to_string(classes, ','), false)
$$;

create function cmd_classes() returns text[]
    language sql stable
    as $$
    select string_to_array(current_setting('cmd.classes'), ',')
$$;

cmd_user_id

-- user_id (text)
create function cmd_set_user_id(user_id text) returns void
    language sql
    as $$
    select set_config('cmd.user_id', user_id, false)
$$;

create function cmd_user_id() returns text
    language sql stable
    as $$
    select current_setting('cmd.user_id')
$$;

cmd_since

-- since (timestamptz)
create function cmd_set_since(since timestamptz) returns void
    language sql
    as $$
    select set_config('cmd.since', since::text, false)
$$;

create function cmd_since() returns timestamptz
    language sql stable
    as $$
    select current_setting('cmd.since')::timestamptz
$$;

cmd_uuid

-- cmd_uuid (uuid)
create function cmd_set_uuid(cmd_uuid uuid) returns void
    language sql
    as $$
    select set_config('cmd.uuid', cmd_uuid::text, false)
$$;

create function cmd_uuid() returns uuid
    language sql stable
    as $$
    select current_setting('cmd.uuid')::uuid
$$;

cmd_ts_bucket

-- truncate a timestamp to a time bucket (default: day)
create function cmd_ts_bucket(ts timestamptz, bucket text default 'day') returns date
    language sql stable
    as $$
    select date_trunc(bucket, ts)::date
$$;

Browsing recent commands

select * from command_log c order by c.id desc limit 200;

Lookup by identifier

By tenant:

select c.* from command_log c
where c.tenant_id = cmd_tenant_id()::text
order by created_at desc limit 500;

By cmd_uuid:

select c.* from command_log c
where cmd_uuid = cmd_uuid();

By user, filtered on command class:

select c.tenant_id, c.cmd_class, c.state, count(*)
from command_log c
where c.user_id = cmd_user_id()
  and c.cmd_class like '%Submit%'
group by 1, 2, 3;

Error investigation

Recent 5xx errors:

select c.* from command_log c
where c.http_status_code >= 500
order by created_at desc limit 50;

Recent failed commands:

select * from command_log c
where c.state = 'Failed'
order by c.created_at desc limit 20;

By problem code:

select * from command_log c
where c.problem_code = '1021'
order by c.created_at desc;

Errors with a specific problem code, filtered by problem text:

select c.tenant_id, c.started_at, c.problem
from command_log c
where c.problem_code = 2611 and c.problem::text like '%Virus%'
order by c.created_at desc limit 10;

Error breakdown by tenant and user:

select c.tenant_id, c.user_id, count(*)
from command_log c
where c.problem_code = 'CDP-1002'
group by 1, 2 order by 1, 3;

Errors for a specific command class and problem codes:

select c.tenant_id, count(*)
from command_log c
where c.cmd_class like cmd_class_pattern()
  and c.created_at >= cmd_since()
  and c.problem_code in (2515, 1012)
group by 1 order by 2 desc;

Aggregate statistics

Command count and time range:

select count(*) from command_log;
select min(c.created_at) from command_log c;
select max(created_at) from command_log;

State breakdown (recent):

select c.state, count(*)
from command_log c
where c.created_at >= cmd_since()
group by 1 order by 1;

Command class breakdown (recent, successful, non-dry-run):

select cmd_class, count(*)
from command_log
where created_at >= cmd_since()
  and (dry_run is false or dry_run is null)
  and state = 'Successful'
group by 1 order by 2 desc;

Performance analysis

Duration stats per day, grouped by command class and HTTP status:

select cmd_ts_bucket(c.created_at),
       c.cmd_class,
       c.http_method, c.http_status, c.http_status_code,
       count(*),
       cast(min(c.duration_in_millis) as numeric(11, 2)),
       cast(avg(c.duration_in_millis) as numeric(11, 2)),
       cast(max(c.duration_in_millis) as numeric(11, 2))
from command_log c
where c.created_at >= cmd_since()
  and c.cmd_class like cmd_class_pattern()
group by 1, 2, 3, 4, 5
order by 1 desc, 2, 5;

Average duration per day for a specific command:

select cmd_ts_bucket(c.created_at) as dato,
       count(*) as num_commands,
       avg(c.duration_in_millis)::bigint as avg_duration
from command_log c
where c.created_at >= cmd_since()
  and c.duration_in_millis < 60000
  and c.cmd_class like cmd_class_pattern()
group by 1
order by 1 desc;

Daily activity counts

Successful commands per day for a specific class:

select cmd_ts_bucket(c.created_at) as dato,
       count(*) as daily_count
from command_log c
where c.created_at >= cmd_since()
  and c.cmd_class like cmd_class_pattern()
  and c.http_status = 'OK'
group by 1
order by 1 desc;

New resource creation per day (with timezone):

select
    substring(date_trunc('day', c.created_at, 'Europe/Oslo')::varchar, 1, 10) as dato,
    count(*) as daily_count
from command_log c
where c.created_at >= cmd_since()
  and c.cmd_class like cmd_class_pattern()
group by 1
order by 1 desc;

Advanced queries

Unique counts with window functions

Count unique tenants that completed a specific command after a date:

with completed as (
    select
        c.tenant_id,
        c.started_at,
        c.state,
        row_number() over (partition by c.tenant_id order by c.started_at desc) as row_no
    from command_log c
    where c.cmd_class like cmd_class_pattern()
      and c.state = 'Successful'
      and c.started_at > cmd_since()
)
select count(*) as unique_tenants
from completed
where row_no = 1;

Multi-class comparison

Error rates across related command classes:

select c.cmd_class, c.problem_code, count(*)
from command_log c
where c.cmd_class = any(cmd_classes())
  and c.created_at >= cmd_since()
group by 1, 2
order by 1, 2;

User activity

Commands per user across tenants:

select c.user_id, count(distinct c.tenant_id)
from command_log c
where c.cmd_class like cmd_class_pattern()
group by 1 order by 2 desc;

JSONB body queries

The cmd_body column stores the command payload as JSONB. These queries show common patterns for filtering on body content.

Filter by a nested field value:

select *
from command_log c
where c.cmd_class like cmd_class_pattern()
  and c.cmd_body -> 'answers' ->> 'questionNo' = 'Q-1'
  and c.cmd_body -> 'answers' ->> 'value' = 'Yes';

Filter by a nested object property:

select c.tenant_id, c.cmd_class, count(*)
from command_log c
where c.cmd_class like cmd_class_pattern()
  and c.cmd_body -> 'payment' ->> 'paymentType' = 'Invoice'
group by 1, 2 order by 3 desc;

Exclude dry-run commands using JSONB key existence:

select c.tenant_id, count(*) as num_created
from command_log c
where c.created_at >= cmd_since()
  and c.cmd_class like cmd_class_pattern()
  and not (c.cmd_body ? 'options' and c.cmd_body -> 'options' ? 'DryRun')
  and c.http_status_code = 200
group by 1 order by 2 desc;

Filter by JSONB containment (@>):

select c.tenant_id, c.cmd_body ->> 'orgNo' as org_no
from command_log c
where c.cmd_class like cmd_class_pattern()
  and c.http_status = 'OK'
  and c.cmd_body @> '{"year": 2023}';

Categorize commands by body content:

select
    case when c.cmd_body ? 'importedData' then 'import' else 'manual' end as origin,
    c.cmd_body ->> 'year' as report_year,
    count(*) as cnt
from command_log c
where c.cmd_class like cmd_class_pattern()
  and not (c.cmd_body ? 'options' and c.cmd_body -> 'options' ? 'DryRun')
  and c.http_status_code = 200
group by 1, 2
order by 2, 1;

First creation per tenant, with ranking:

with resource_created as (
    select
        substring((c.created_at at time zone 'Europe/Oslo')::varchar, 1, 16) created_at_str,
        c.cmd_body ->> 'orgNo' as org_no,
        c.user_id,
        c.tenant_id,
        row_number() over (partition by c.tenant_id order by c.created_at) as row_no
    from command_log c
    where c.cmd_class like cmd_class_pattern()
      and c.http_status = 'OK'
      and (not c.cmd_body -> 'options' ? 'DryRun')
      and c.cmd_body @> '{"year": 2023}'
)
select
    org_no,
    min(created_at_str) first_created,
    max(created_at_str) last_created,
    max(row_no) num_created,
    tenant_id
from resource_created
group by org_no, tenant_id
order by 3 desc
limit 30;