System Parameters & Version Management¶
Runtime configuration and database version tracking. System parameters control journal logging, audit data retention, storage modes, permission cache timeouts, and partition management. Version management functions track migration history across components.
Source: 018_functions_public.sql, 012_tables_const.sql, 029_seed_data.sql
System Parameter Functions¶
auth.get_sys_param¶
Retrieves a single system parameter row by its group code and code. Returns null if no matching parameter exists.
| Parameter | Type | Default | Description |
|---|---|---|---|
_group_code |
text | -- | Parameter group (e.g., 'journal', 'auth', 'partition') |
_code |
text | -- | Parameter code within the group (e.g., 'level', 'retention_days') |
Returns: const.sys_param -- the full row including text_value, number_value, and bool_value columns, or null if not found.
Permission required: None
Source: 018_functions_public.sql:47
-- Read the journal logging level
select (auth.get_sys_param('journal', 'level')).text_value;
-- Read the permission cache timeout
select (auth.get_sys_param('auth', 'perm_cache_timeout_in_s')).number_value;
-- Read the full parameter row
select * from auth.get_sys_param('journal', 'storage_mode');
auth.update_sys_param¶
Creates or updates a system parameter. This function is restricted to user_id = 1 (the system user) and is intended for application startup configuration before any human users are active. Uses an upsert (on conflict ... do update) so it works for both inserting new parameters and updating existing ones.
| Parameter | Type | Default | Description |
|---|---|---|---|
_user_id |
bigint | -- | Must be 1 (system user); any other value raises an exception |
_group_code |
text | -- | Parameter group |
_code |
text | -- | Parameter code within the group |
_text_value |
text | null |
Text value to store |
_number_value |
bigint | null |
Numeric value to store |
_bool_value |
boolean | null |
Boolean value to store |
Returns: const.sys_param -- the upserted row.
Permission required: Must be called with _user_id = 1. Raises 42501 (insufficient_privilege) for any other user.
Source: 018_functions_public.sql:64
-- Change journal level to log everything (system user only)
select auth.update_sys_param(1, 'journal', 'level', 'all');
-- Disable journaling entirely
select auth.update_sys_param(1, 'journal', 'level', 'none');
-- Change storage mode to send notifications alongside local storage
select auth.update_sys_param(1, 'journal', 'storage_mode', 'both');
-- Set permission cache timeout to 10 minutes
select auth.update_sys_param(1, 'auth', 'perm_cache_timeout_in_s', _number_value := 600);
-- This will raise an exception -- only user_id = 1 is allowed
select auth.update_sys_param(42, 'journal', 'level', 'all');
Built-in Parameters¶
These parameters are seeded during initial database setup via 029_seed_data.sql. They control core system behavior and can be modified at runtime using auth.update_sys_param.
Journal Parameters¶
| group_code | code | Default | Type Used | Description |
|---|---|---|---|---|
journal |
level |
update |
text_value |
Journal logging verbosity. all = log everything including reads, update = state-changing operations only (default), none = disable journaling entirely |
journal |
retention_days |
365 |
text_value (cast to int) |
How many days of journal entries to keep before purging. Used by public.purge_audit_data() |
journal |
storage_mode |
local |
text_value |
Where journal data is sent. local = INSERT into PostgreSQL only (default), notify = pg_notify on journal_events channel only (skip INSERT), both = INSERT + pg_notify |
User Event Parameters¶
| group_code | code | Default | Type Used | Description |
|---|---|---|---|---|
user_event |
retention_days |
365 |
text_value (cast to int) |
How many days of user events to keep before purging. Used by public.purge_audit_data() |
user_event |
storage_mode |
local |
text_value |
Where user event data is sent. Same modes as journal: local, notify, or both |
Partition Parameters¶
| group_code | code | Default | Type Used | Description |
|---|---|---|---|---|
partition |
months_ahead |
3 |
number_value |
How many future monthly partitions to pre-create for journal and user_event tables. Used by unsecure.ensure_audit_partitions() |
Login Lockout Parameters¶
| group_code | code | Default | Type Used | Description |
|---|---|---|---|---|
login_lockout |
max_failed_attempts |
5 |
number_value |
Number of failed login attempts before the account is automatically locked |
login_lockout |
window_minutes |
15 |
number_value |
Time window in minutes for counting failed login attempts. Only failures within this window count toward the threshold |
Auth Parameters¶
| group_code | code | Default | Type Used | Description |
|---|---|---|---|---|
auth |
perm_cache_timeout_in_s |
300 (fallback) |
number_value |
Permission cache TTL in seconds. Not seeded -- the system uses a hardcoded fallback of 300 seconds if this parameter is missing. Set it explicitly to override the default |
Journal Level Behavior¶
The journal.level parameter is evaluated by the helper function helpers.should_log_journal(), which is called automatically by public.create_journal_message() before writing any journal entry.
| Level | State-changing events | Read-only events |
|---|---|---|
all |
Logged | Logged |
update (default) |
Logged | Skipped |
none |
Skipped | Skipped |
Whether an event is "read-only" is determined by the is_read_only flag on const.event_code. The helper helpers.is_event_read_only(_event_id) looks up this flag.
Source: 013_update_common-helpers_v1-6.sql:36 (should_log_journal), 013_update_common-helpers_v1-6.sql:56 (is_event_read_only)
Storage Mode Behavior¶
The storage_mode parameter (available for both journal and user_event groups) controls whether audit data is stored locally in PostgreSQL, sent to external systems via pg_notify, or both.
| Mode | INSERT into PostgreSQL | Fire pg_notify |
|---|---|---|
local (default) |
Yes | No |
notify |
No | Yes |
both |
Yes | Yes |
Two helper functions evaluate the storage mode:
helpers.should_store_locally(_group_code)-- returnstruewhen mode islocalorbothhelpers.should_notify_storage(_group_code)-- returnstruewhen mode isnotifyorboth
When mode is notify, the journal and user event notification functions (unsecure.notify_journal_event, unsecure.notify_user_event) send a JSON payload on the journal_events or user_events channel respectively.
Warning
When storage mode is notify, search and query functions (search_journal, get_journal_entry, search_user_events, etc.) return empty results since data is not stored in PostgreSQL. The application should query the external store directly for audit data.
Source: 013_update_common-helpers_v1-6.sql:81 (should_store_locally), 013_update_common-helpers_v1-6.sql:94 (should_notify_storage)
Underlying Table¶
System parameters are stored in const.sys_param:
| Column | Type | Description |
|---|---|---|
sys_param_id |
integer | Auto-generated primary key |
created_at |
timestamptz | Creation timestamp (defaults to now()) |
created_by |
text | Identifier of the creator (max 250 chars) |
updated_at |
timestamptz | Last update timestamp |
updated_by |
text | Identifier of the last updater (max 250 chars) |
group_code |
text | Parameter group (e.g., journal, user_event, partition, auth) |
code |
text | Parameter code within the group |
text_value |
text | Text value (nullable) |
number_value |
bigint | Numeric value (nullable) |
bool_value |
boolean | Boolean value (nullable) |
A unique index on (group_code, code) ensures each parameter combination is unique and enables the upsert behavior in auth.update_sys_param.
Source: 012_tables_const.sql:13
Version Management Functions¶
Version management tracks database migration history using the public.__version table. Each migration script calls start_version_update at the beginning and stop_version_update at the end, recording execution timestamps for each version.
public.start_version_update¶
Inserts a new version record with the current timestamp as the start time. Called at the beginning of each migration script.
| Parameter | Type | Default | Description |
|---|---|---|---|
_version |
text | -- | Version identifier (e.g., '1.6', '2.0') |
_title |
text | -- | Short title describing the update |
_description |
text | null |
Optional longer description |
_component |
text | 'main' |
Component name for tracking independent subsystems |
Returns: setof __version -- the newly inserted version record.
Source: 018_functions_public.sql:13
select * from start_version_update('1.6', 'Journal level control helper',
_component := 'common_helpers',
_description := 'Function to check journal logging level from sys_param');
public.stop_version_update¶
Marks a version record as completed by setting execution_finished to the current timestamp. Called at the end of each migration script.
| Parameter | Type | Default | Description |
|---|---|---|---|
_version |
text | -- | Version identifier to mark as finished |
_component |
text | 'main' |
Component name (must match the start_version_update call) |
Returns: setof __version -- the updated version record with execution_finished set.
Source: 018_functions_public.sql:24
public.check_version¶
Checks whether a specific version has been applied for a given component. Useful in migration scripts to avoid re-running already-applied updates.
| Parameter | Type | Default | Description |
|---|---|---|---|
_version |
text | -- | Version identifier to check |
_component |
text | 'main' |
Component name to check |
_throw_err |
boolean | false |
When true, raises an exception if the version is not found instead of returning false |
Returns: boolean -- true if the version exists, false otherwise (or raises exception if _throw_err is true).
Source: 018_functions_public.sql:437
-- Check if version 1.6 has been applied
select public.check_version('1.6');
-- Check a specific component
select public.check_version('1.6', 'common_helpers');
-- Raise exception if version is missing
select public.check_version('1.6', 'main', true);
public.get_version¶
Retrieves version information. When called with a specific version, returns that version's record. When called without a version, returns the most recently started version for the component.
| Parameter | Type | Default | Description |
|---|---|---|---|
_component |
text | 'main' |
Component name to query |
_version |
text | null |
Specific version to retrieve; when null, returns the latest version |
Returns: table(__version_id integer, __version text, __title text, __execution_started timestamptz, __execution_finished timestamptz)
Source: 018_functions_public.sql:460
-- Get the latest version of the main component
select * from public.get_version();
-- Get a specific version
select * from public.get_version('main', '1.6');
-- Get the latest version of a specific component
select * from public.get_version('common_helpers');
Version Table¶
Version records are stored in public.__version:
| Column | Type | Description |
|---|---|---|
version_id |
integer | Auto-generated primary key |
component |
text | Component name (default 'main'); allows tracking independent subsystems |
version |
text | Version identifier |
title |
text | Short description of the update |
description |
text | Optional longer description |
execution_started |
timestamptz | When the migration began (set by start_version_update) |
execution_finished |
timestamptz | When the migration completed (set by stop_version_update); null if still running or failed |
A unique index on (component, version) prevents duplicate version entries.
Source: 002_create_version_management.sql:12