FS Tenant Statistics

The fs_tenant_statistics table provides a snapshot of financial statement progress across all tenants for a given fiscal year. It is populated by calling the create_fs_tenant_statistics SQL function, which iterates every tenant schema and collects submission states into one flat table for easy querying.

When to use

Monitoring tax return and annual statement submission progress during filing season
Generating per-month or per-day submission counts
Checking which tenants have completed (or not started) their year-end closing

Creating a statistics run

The function lives in the global schema.

select * from create_fs_tenant_statistics(fs_year := 2024);

-- verbosity: 0 = silent, 1 = start/end summary, 2 = progress every 100 tenants (default), 3 = per-tenant detail
select * from create_fs_tenant_statistics(fs_year := 2024, verbosity := 3);

Parameters

Parameter Default Description

Parameter	Default	Description
`fs_year`	(required)	Fiscal year to collect statistics for.
`verbosity`	`2`	Controls logging output. 0 = silent, 1 = start/completion summary, 2 = progress every 100 tenants, 3 = per-tenant detail.
`run_uuid`	`gen_random_uuid()`	UUID identifying this run. Auto-generated if omitted.

fs_year

(required)

Fiscal year to collect statistics for.

verbosity

2

Controls logging output. 0 = silent, 1 = start/completion summary, 2 = progress every 100 tenants, 3 = per-tenant detail.

run_uuid

gen_random_uuid()

UUID identifying this run. Auto-generated if omitted.

Return value

The function returns a create_fs_tenant_statistics_run composite type:

Field Description

Field	Description
`run_uuid`	UUID of this run
`fs_year`	The fiscal year
`num_tenants`	Total number of tenant schemas found
`num_processed`	Number of tenants actually processed (iterated)
`num_failures`	Number of tenants that failed
`failures`	Array of error messages (capped at 50)

run_uuid

UUID of this run

fs_year

The fiscal year

num_tenants

Total number of tenant schemas found

num_processed

Number of tenants actually processed (iterated)

num_failures

Number of tenants that failed

failures

Array of error messages (capped at 50)

Example result

run_uuid,fs_year,num_tenants,num_processed,num_failures,failures
503ffe6d-...-2be6f98f910b,2025,7297,7297,2,"{_8283031128b1fc6a20d49: column ass.sent_to_signing_at does not exist,_9219920096caa13b6b8d6: column ass.sent_to_signing_at does not exist}"

Failures typically occur when a tenant schema has not been fully migrated (e.g., missing columns from recent migrations). The function continues processing remaining tenants and collects failures in the result.

Querying results

After a run completes, use the run_uuid to query the results. The recommended way is to create the run and store the run_uuid in a session variable in one go:

-- recommended: create run and store the run_uuid in a session variable for subsequent queries
with run as (
    select * from create_fs_tenant_statistics(fs_year := 2024)
)
select set_config('finsta.run_uuid', run_uuid::text, false), * from run;

Alternatively, set the variable manually:

-- or set manually for an existing run
set finsta.run_uuid = '<run-uuid>';

-- or set from the most recent run
select set_config('finsta.run_uuid', run_uuid::text, false)
from fs_tenant_statistics order by id desc limit 1;

-- clear it when done
reset finsta.run_uuid;

Find the most recent run

select set_config('finsta.run_uuid', run_uuid::text, false), run_uuid, fs_year, created_at
from fs_tenant_statistics
order by id desc
limit 1;

Submitted tax returns per month

select
    substring(date_trunc('day', fts.trs_signed_by_representative_at at time zone 'Europe/Oslo')::varchar, 1, 7) as mnd,
    count(*) as Skattemelding
from fs_tenant_statistics fts
where fts.run_uuid = current_setting('finsta.run_uuid')::uuid
  and fts.trs_signed_by_representative_at is not null
group by 1
order by 1 desc;

Submitted annual statements per month

select
    substring(date_trunc('day', fts.ass_signed_by_representative_at at time zone 'Europe/Oslo')::varchar, 1, 7) as mnd,
    count(*) as Årsregnskap
from fs_tenant_statistics fts
where fts.run_uuid = current_setting('finsta.run_uuid')::uuid
  and fts.ass_signed_by_representative_at is not null
group by 1
order by 1 desc;

To switch from monthly to daily breakdown, change the substring length from 1, 7 (year-month) to 1, 10 (year-month-day).

Submission status overview

select
    coalesce(fts.trs_submission_state, '-') as Skattemelding,
    coalesce(fts.ass_submission_state, '-') as Årsregnskap,
    count(*)                                as Antall
from fs_tenant_statistics fts
where fts.run_uuid = current_setting('finsta.run_uuid')::uuid
  and fts.fs_uuid is not null
group by 1, 2
order by 1 desc, 2 desc;

Cleanup

Over time, repeated runs accumulate rows. The cleanup_fs_tenant_statistics function deduplicates runs within configurable time windows, keeping only the most recent run per window.

The function works in two phases:

Analyze — scans all runs in the period, groups them into time buckets, and reports which runs would be kept or deleted.
Execute or report — in dry-run mode (default) it stops after the analysis. In execution mode it proceeds to delete the identified runs.

Dry run (default)

By default the function runs in dry-run mode, reporting what it would do via RAISE NOTICE without deleting anything.

select * from cleanup_fs_tenant_statistics();

Example output:

analyzing runs in period [2025-03-15 .. 2026-04-17], window: 1 day
found 14 runs across 8 buckets

bucket [2026-04-15 .. 2026-04-16]
  KEEP   run 503ffe6d-... (2026-04-15 14:30, 7200 rows)
  DELETE run 7be7716f-... (2026-04-15 09:15, 7180 rows)
bucket [2026-04-14 .. 2026-04-15]
  KEEP   run a1b2c3d4-... (2026-04-14 16:00, 7195 rows)

DRY RUN complete — no data was modified.
summary: 8 runs kept, 6 runs to delete (43000 rows would be removed), 0 empty rows would be removed

Delete empty rows

Runs created before V2.1.0 may contain rows with fs_uuid IS NULL (tenants without a financial statement for the year). Use delete_empty := true to remove these.

select * from cleanup_fs_tenant_statistics(delete_empty := true);

Full cleanup

select * from cleanup_fs_tenant_statistics(delete_empty := true, dry_run := false);

Example output:

analyzing runs in period [2025-03-15 .. 2026-04-17], window: 1 day
found 320 empty rows (fs_uuid IS NULL, legacy)
found 14 runs across 8 buckets

bucket [2026-04-15 .. 2026-04-16]
  KEEP   run 503ffe6d-... (2026-04-15 14:30, 7200 rows)
  DELETE run 7be7716f-... (2026-04-15 09:15, 7180 rows)

starting cleanup...
  deleted 320 empty rows
  deleted run 7be7716f-... done (7180 rows)
cleanup complete.
summary: 8 runs kept, 6 runs deleted (43000 rows removed), 320 empty rows removed

Custom period and window

select * from cleanup_fs_tenant_statistics(
    window_interval := '12 hours',
    from_ts := '2025-01-01'::timestamptz,
    to_ts := '2025-06-01'::timestamptz,
    dry_run := false
);

Parameters

Parameter Default Description

Parameter	Default	Description
`window_interval`	`'1 day'`	Time bucket size. Within each bucket, only the most recent run is kept.
`from_ts`	`null` (earliest row)	Start of the cleanup period.
`to_ts`	`null` (now)	End of the cleanup period.
`delete_empty`	`false`	Delete rows where `fs_uuid IS NULL` (legacy empty rows) within the cleanup period.
`dry_run`	`true`	Preview only. Set to `false` to actually delete.

window_interval

'1 day'

Time bucket size. Within each bucket, only the most recent run is kept.

from_ts

null (earliest row)

Start of the cleanup period.

to_ts

null (now)

End of the cleanup period.

delete_empty

false

Delete rows where fs_uuid IS NULL (legacy empty rows) within the cleanup period.

dry_run

true

Preview only. Set to false to actually delete.