Task: Verify country SQL against the evaluation checklist
This page documents a task in the Commission: country story. It captures the goal, current status, acceptance, and any notes or results.
Goal
Verify the country DDL thoroughly against every criterion of the domain entity evaluation checklist — constraints, indexes, trigger validation functions, soft-delete rule, soft-FK validations — mirroring currency's verify-SQL task, beyond the appraisal's spot-grep.
Status
| Field | Value |
|---|---|
| State | DONE |
| Parent story | Commission: country |
| Now | Nothing. |
| Waiting on | Nothing. |
| Next | Nothing. |
| Last touched | 2026-06-25 |
Acceptance
- Every DB-layer checklist criterion has a recorded pass/fail for refdata_countries_create.sql and the notify trigger.
- Failures are fixed on this task or filed with findings.
- The checklist knowledge doc is extended where country surfaces criteria it lacks.
Plan
Notes
PRs
| PR | Title |
|---|---|
| #1300 | [ores.sql,doc] Commission country: verify SQL, add validate_country_fn, document trigger categories |
Review
| # | Comment summary | File | Decision | Notes |
|---|---|---|---|---|
| 1 | Duplicate party_countries in Category 3 list |
postgresql_architecture.org | Fixed in 5ab4ade | Typo — removed duplicate. |
| 2 | Stale note says change_reason_code "not fixed here" | task_verify_sql.org | Fixed in 5ab4ade | Updated to reflect fix in a12d4863. |
| 3 | security definer absent from validate_fn |
refdata_countries_create.sql | Fixed in 1cb85c9a | Architectural analysis concluded this is a genuine gap, not a deliberate omission. Added to validate_fn and to codegen template. |
| 4 | Bootstrap pass-through lacks valid_to filter |
refdata_countries_create.sql | Fixed in 1cb85c9a | Semantic bug: bare not-exists would skip pass-through for tables with only soft-deleted rows. Fixed in validate_fn and codegen template. |
| 5 | Capture add-security-definer-to-validate-fns.org has placeholder body |
product_backlog/inbox | Fixed in round 4 | Updated title, What, Why, References, See also to reflect narrowed scope post-template-fix. |
Result
All DB-layer checklist criteria evaluated for refdata_countries_create.sql and the notify
trigger. One gap found and fixed inline; one pre-existing gap in the currencies file noted
for follow-up.
refdata_countries_create.sql — domain entity
| Criterion | Status | Notes |
|---|---|---|
Table ores_refdata_countries_tbl |
✓ | |
tenant_id column |
✓ | Validated via ores_iam_validate_tenant_fn in insert trigger. |
version |
✓ | |
modified_by, performed_by |
✓ | Validated and set in insert trigger. |
change_reason_code, change_commentary |
✓ | Validated via ores_dq_validate_change_reason_fn. |
valid_from, valid_to |
✓ | |
CHECK valid_from < valid_to |
✓ | |
CHECK alpha2_code <> '' |
✓ | Natural key non-empty guard. |
| GIST exclusion (tenant_id, alpha2_code, tstzrange) | ✓ | |
| Primary key (tenant_id, alpha2_code, valid_from, valid_to) | ✓ | |
Version unique index countries_version_uniq_idx |
✓ | WHERE valid_to = infinity. |
Natural key unique index countries_alpha2_code_uniq_idx |
✓ | WHERE valid_to = infinity. Renamed from countries_code_uniq_idx when brought under codegen (task 3E63E209). |
Tenant index countries_tenant_idx |
✓ | WHERE valid_to = infinity. |
Additional alpha3 index countries_alpha3_idx |
✓ | Country-specific; supports ISO alpha3 lookups. |
Additional numeric index countries_numeric_idx |
✓ | Country-specific; supports ISO numeric lookups. |
Insert trigger ores_refdata_countries_insert_trg |
✓ | BEFORE INSERT FOR EACH ROW. |
Insert function — security definer + set search_path |
✓ | Present; country is more secure than currency (noted below). |
Soft-FK: coding_scheme_code validated (optional) |
✓ | Nullable; validates against ores_dq_coding_schemes_tbl. |
| Version management (optimistic lock, P0002) | ✓ | |
Upsert: closes old row with valid_to = current_timestamp |
✓ | |
Soft-delete rule ores_refdata_countries_delete_rule |
✓ | |
Validation function ores_refdata_validate_country_fn |
✓ | Added in this task — was missing. |
RLS policy countries_tenant_isolation_policy |
✓ | In refdata_rls_policies_create.sql. |
| Drop file drops rule, trigger, insert fn, validate fn, table | ✓ | validate fn drop added in this task. |
Notify trigger (refdata_countries_notify_trigger_create.sql)
| Criterion | Status | Notes |
|---|---|---|
Entity name ores.refdata.country |
✓ | |
Channel ores_refdata_countries |
✓ | |
| Payload: entity, timestamp, entity_ids, tenant_id | ✓ | Uses ores_utility_iso8601_timestamp_fn. |
| AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW | ✓ | |
| Drop file drops trigger and function | ✓ |
Gap fixed: missing ores_refdata_validate_country_fn
Currency defines ores_refdata_validate_currency_fn for use by other entities' soft-FK
validations. Country had no equivalent. Added ores_refdata_validate_country_fn to
refdata_countries_create.sql following the currency pattern exactly (null/empty guard,
freshly-provisioned pass-through, tenant+system-tenant lookup, descriptive error). Also
added drop function if exists ores_refdata_validate_country_fn; to the drop file,
following the rounding_type pattern (which currency's own drop file omits — see below).
change_reason_code validation placement — fixed
The country insert trigger originally called ores_dq_validate_change_reason_fn at the
end of the function, after the version management block. The currency trigger calls it
before. Fixed in commit a12d4863 to match the Category 1 ordering rule (fail fast before
acquiring the row lock). Junction tables (party_currencies, party_countries) retain
the after-version placement — that is consistent with their Category 3 contract.
Pre-existing gap in currencies: drop file omits ores_refdata_validate_currency_fn
refdata_currencies_drop.sql does not drop ores_refdata_validate_currency_fn, leaving
it as a dangling function after the table is dropped. The rounding_type drop file
correctly drops its validate function. Filed as a separate finding; not in scope here.