Skip to main content
Jira progress: loading…

SSSR-LIST

List Registry

(Addendum to SSSR – Smart Searchable Signal Registry)

1. Introduction​

The SSSR List Registry extends the Signal & Asset registries with a third pillar: lists.

It provides a unified way to store, manage, and reference lists of values across the ZAYAZ ecosystem.

Lists can be simple (e.g. file formats, currencies, DeepL language codes) or complex and hierarchical (e.g. NACE classifications, country/region groupings). Instead of scattering these definitions across multiple tables, the List Registry serves as a central authority that other data models can reference via [SSSR_REF: …].

2. Key Principles​

  1. Single Source of Truth
  • All lists are catalogued in one place.
  • Prevents duplication and ensures consistency across services.
  1. Mother vs. Child Role
  • Some lists are owned by the List Registry (e.g. file formats, currencies).
  • Other lists are external or authoritative (e.g. ISO countries, NACE codes).
  • In those cases, the List Registry acts as a “Child” that references or syncs from the external dataset.
  1. Nested / Hierarchical Support
  • Lists can have parent-child relationships (e.g. NACE Level 1 → Level 2 → Level 3).
  • This supports structured navigation and hierarchical queries.
  1. Uniform Referencing
  • Just like sssr_assets_registry and sssr_signal_registry, the List Registry integrates with SSSR links.
  • Any consumer can use a consistent [SSSR_REF: …] syntax to resolve list entries, regardless of whether the list is internal (Mother) or external (Child).
  1. Future-proof Design
  • New standards (ISO codes, UNSPSC, etc.) can be plugged in easily.
  • Users don’t need to know where the list originates; the registry abstracts that away.

3. SSSR List Registry​

3.1. Tables​

3.1.1. sssr_list_registry (catalog of lists)​

ColumnTypeRequiredDescription
list_iduuid✓Primary key.
list_codetext (unique)✓Stable identifier (e.g., countries_iso3166, nace_rev2, currencies_iso4217).
titletext✓Human title shown in UIs.
descriptiontextPurpose/notes.
authority_modetext✓One of: authoritative (Mother), federated (Child), overlay.
versiontextSemantic/date version exposed to consumers (e.g., v2025.08 or 2025-08-01).
external_refjsonbFor federated/overlay: connection hints (e.g., {"table":"countries","pk":"<your-identity-column>","source":"ISO_3166"}).
policyjsonbRules (e.g., {"unique_by":"item_code","immutable":true,"case":"upper"}).
lifecycle_statustext✓active | deprecated (controls visibility).
updated_attimestamptz✓Auto-updated timestamp.

Constraints / indexes

  • UNIQUE(list_code)
  • CHECK (authority_mode IN ('authoritative','federated','overlay'))
  • Optional: GIN index on policy for metadata queries.

3.1.2. sssr_list_items (Mother lists only)​

Only used when authority_mode='authoritative'. For Child/Overlay, items come from the external tables/views.

ColumnTypeRequiredDescription
item_uiduuid✓Primary key.
list_iduuid FK →sssr_list_registry.list_id✓Owning list.
item_codetext✓Canonical key (e.g., NO, EUR, application/pdf). Unique within a list.
labelsjsonbLocalized names, e.g., { "en":"Norway", "no":"Norge" }.
aliasesjsonbAlternate spellings/codes, e.g., ["NOR","Norway"].
metajsonbArbitrary data: numeric codes, regions, minor units, etc.
parent_codetextFor hierarchy (optional).
pathltree or textMaterialized path (e.g., Section.C.Division.C10.Group.C10.1).
sort_orderintStable UI sort.
valid_fromtimestamptzEffectivity window start.
valid_totimestamptzEffectivity window end.
updated_attimestamptz✓Auto-updated timestamp.

Constraints / indexes

  • UNIQUE(list_id, item_code)
  • CHECK (valid_to IS NULL OR valid_to > COALESCE(valid_from, '-infinity'::timestamptz))
  • Indexes: (list_id, item_code), GIN on labels, aliases, meta; GiST/GiN on path (if ltree).

3.1.3. sssr_list_item_overlay (Overlay for Child lists)​

Enrich external lists without copying rows.

ColumnTypeRequiredDescription
overlay_uiduuid✓Primary key.
list_iduuid FK✓The federated/overlay list.
external_keytext✓Key that matches the external item (e.g., C10.12, NO).
labelsjsonbLocalized labels override/augment external.
aliasesjsonbExtra aliases.
metajsonbExtra metadata/flags (e.g., {"deprecated":true}).
valid_fromtimestamptzOptional effectivity.
valid_totimestamptzOptional effectivity.
updated_attimestamptz✓Timestamp.

Constraints

  • UNIQUE(list_id, external_key)

3.2. Views (normalize Child lists into the common shape)​

Create one view per federated list to present a uniform contract: Example: v_list_items_countries_iso3166

v-list-items-countries-iso3166.sqlGitHub ↗
CREATE OR REPLACE VIEW v_list_items_countries_iso3166 AS
SELECT
  r.list_id,
  c.code         AS item_code,
  jsonb_build_object('en', c.name_en)    -- plus other locales if you have them
    || COALESCE(o.labels, '{}')::jsonb   AS labels,
  COALESCE(o.aliases, '[]')::jsonb       AS aliases,
  (jsonb_build_object('alpha3', c.alpha3, 'numeric', c.numeric, 'region', c.region)
    || COALESCE(o.meta, '{}')::jsonb)    AS meta,
  NULL::text AS parent_code,
  NULL::text AS path,
  NULL::int  AS sort_order,
  NULL::timestamptz AS valid_from,
  NULL::timestamptz AS valid_to
FROM countries c
JOIN sssr_list_registry r ON r.list_code = 'countries_iso3166'
LEFT JOIN sssr_list_item_overlay o
       ON o.list_id = r.list_id AND o.external_key = c.code;

Create similar v_list_items_nace_rev2 with hierarchy columns (parent_code/path).

3.3. Access pattern (authoritative vs federated)​

When resolving items for a list_code:

  • If authority_mode='authoritative' → read from sssr_list_items.
  • If authority_mode IN ('federated','overlay') → read from the corresponding view (e.g., v_list_items_<list_code>).

This keeps consumers simple: one interface, two backends.

3.4. SSSR Reference Forms​

  • Whole list (inline):[SSSR_REF: sssr:list.<list_code>]e.g., [SSSR_REF: sssr:list.countries_iso3166]

  • List item (inline):[SSSR_REF: sssr:list_item.<list_code>.<item_code>]e.g., [SSSR_REF: sssr:list_item.nace_rev2.C10.12]

  • Machine JSON (DB fields):

machine-db.jsonGitHub ↗
{ "registry": "sssr:list_item", "list": "nace_rev2", "ids": ["C10.12","C10.1"] }

Provenance (extended) for exports/AI (recommended on output, not stored):

provenance-for-export.jsonGitHub ↗
{
  "target_registry": "sssr:list_item",
  "list": "countries_iso3166",
  "targets": ["NO","DE"],
  "source": {"registry":"sssr:orgs.country_code","row_id":"ORG-001"}
}

3.5. Validation helpers​

Function: does a list contain an item (as of now)?

sssr-list-contains.sqlGitHub ↗
CREATE OR REPLACE FUNCTION sssr_list_contains(list_code text, item_code text)
RETURNS boolean LANGUAGE sql STABLE AS $$
WITH L AS (
  SELECT list_id, authority_mode FROM sssr_list_registry WHERE list_code = $1
)
SELECT CASE
  WHEN (SELECT authority_mode FROM L) = 'authoritative' THEN
    EXISTS (SELECT 1 FROM sssr_list_items i
            WHERE i.list_id = (SELECT list_id FROM L)
              AND i.item_code = $2
              AND COALESCE(i.valid_from, '-infinity') <= now()
              AND COALESCE(i.valid_to, 'infinity') >= now())
  ELSE
    -- Federated/overlay path: switch to the normalized view
    EXISTS (SELECT 1 FROM v_list_items_countries_iso3166 v -- swap view per list_code
            WHERE v.list_id = (SELECT list_id FROM L)
              AND v.item_code = $2
              AND COALESCE(v.valid_from, '-infinity') <= now()
              AND COALESCE(v.valid_to, 'infinity') >= now())
END;
$$;

Implement a small dispatcher (e.g., map list_code → view name) if you have multiple federated lists.

Column check example:

column-check-example.sqlGitHub ↗
ALTER TABLE orgs
  ADD CONSTRAINT country_code_valid
  CHECK (sssr_list_contains('countries_iso3166', country_code));

3.6. Hierarchy options (for NACE, etc.)​

  • Materialized path in path (Mother lists) or exposed via view (Child lists).
    • Pros: simple reads (WHERE path LIKE 'C10.%'), easy subtree queries.
  • Adjacency (parent_code) + optional closure table if deep graph analytics are needed.

Helper fields (in views/items):

  • level (section, division, group, class)
  • canonical_order (consistent UI sorting)

3.7. Indices & performance​

  • Registry: UNIQUE(list_code)
  • Items (Mother): UNIQUE(list_id, item_code); GIN on labels, aliases, meta
  • Overlay: UNIQUE(list_id, external_key); GIN on JSON columns
  • Views: back them with indexes on the underlying external tables’ keys (code, parent_code, etc.)

3.8. API sketch​

  • GET /api/lists/{list_code} → returns list metadata + paged items.
  • GET /api/lists/{list_code}/items?parent=C10 → hierarchical slice.
  • POST /api/lists/{list_code}/validate with {"ids":["NO","DE"]} → returns valid/invalid sets.

3.9. Governance & versioning​

  • version on sssr_list_registry communicates the exposed snapshot/version (even for Child lists).
  • For Mother lists, store a source checksum in policy (e.g., {"source_hash":"…"}) and emit telemetry on diffs.
  • For Child lists, record last sync timestamp to external sources in external_ref (e.g., {“synced_at":"2025-08-17T08:00:00Z"}).

4. Starter Pack​

Here’s a proposal for a clean, production-ready starter pack that can be pasted into the DB (PostgreSQL). It covers both Mother (authoritative) and Child/Overlay modes, with concrete examples for currencies (Mother) and countries (Child+Overlay), plus validation helpers.

4.1. Prereqs (schema + extension)​

prereqs.sqlGitHub ↗
-- Optional but recommended
CREATE SCHEMA IF NOT EXISTS sssr;

-- If you plan to use materialized paths later
CREATE EXTENSION IF NOT EXISTS ltree;

4.2. Core tables​

core-tables.sqlGitHub ↗
-- Registry of lists (Mother / Child / Overlay)
CREATE TABLE IF NOT EXISTS sssr.sssr_list_registry (
  list_id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  list_code        text UNIQUE NOT NULL,              -- e.g., 'countries_iso3166', 'currencies_iso4217'
  title            text NOT NULL,
  description      text,
  authority_mode   text NOT NULL CHECK (authority_mode IN ('authoritative','federated','overlay')),
  version          text,                              -- e.g., 'v2025.08' or '2025-08-01'
  external_ref     jsonb,                             -- only for federated/overlay
  policy           jsonb,                             -- e.g., {"unique_by":"item_code","immutable":true}
  lifecycle_status text NOT NULL DEFAULT 'active' CHECK (lifecycle_status IN ('active','deprecated')),
  updated_at       timestamptz NOT NULL DEFAULT now()
);

-- Items for Mother lists only (Authoritative)
CREATE TABLE IF NOT EXISTS sssr.sssr_list_items (
  item_uid     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  list_id      uuid NOT NULL REFERENCES sssr.sssr_list_registry(list_id) ON DELETE CASCADE,
  item_code    text NOT NULL,                         -- canonical key: 'EUR', 'application/pdf'
  labels       jsonb,                                 -- {"en":"Euro","no":"Euro"}
  aliases      jsonb,                                 -- ["€","euro"]
  meta         jsonb,                                 -- {"numeric":978,"minor_unit":2}
  parent_code  text,                                  -- for hierarchy (optional)
  path         ltree,                                 -- or text if ltree is unwanted
  sort_order   int,
  valid_from   timestamptz,
  valid_to     timestamptz,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (list_id, item_code),
  CHECK (valid_to IS NULL OR valid_to > COALESCE(valid_from, '-infinity'::timestamptz))
);

-- Overlay for Child/Overlay lists (adds labels/aliases/meta without copying base rows)
CREATE TABLE IF NOT EXISTS sssr.sssr_list_item_overlay (
  overlay_uid  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  list_id      uuid NOT NULL REFERENCES sssr.sssr_list_registry(list_id) ON DELETE CASCADE,
  external_key text NOT NULL,                         -- e.g., 'NO'
  labels       jsonb,                                 -- additional or overriding labels
  aliases      jsonb,                                 -- extra aliases
  meta         jsonb,                                 -- flags, annotations, etc.
  valid_from   timestamptz,
  valid_to     timestamptz,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (list_id, external_key),
  CHECK (valid_to IS NULL OR valid_to > COALESCE(valid_from, '-infinity'::timestamptz))
);

Helpful indexes

helpful–indexes.sqlGitHub ↗
CREATE INDEX IF NOT EXISTS sssr_list_items_list_code_idx
  ON sssr.sssr_list_items (list_id, item_code);

CREATE INDEX IF NOT EXISTS sssr_list_items_labels_gin
  ON sssr.sssr_list_items USING GIN (labels);

CREATE INDEX IF NOT EXISTS sssr_list_items_meta_gin
  ON sssr.sssr_list_items USING GIN (meta);

CREATE INDEX IF NOT EXISTS sssr_list_overlay_labels_gin
  ON sssr.sssr_list_item_overlay USING GIN (labels);

CREATE INDEX IF NOT EXISTS sssr_list_overlay_meta_gin
  ON sssr.sssr_list_item_overlay USING GIN (meta);

4.3. Mother example: currencies_iso4217​

currencies-iso4217-example.sqlGitHub ↗
-- Register the list (Mother / Authoritative)
INSERT INTO sssr.sssr_list_registry (list_code, title, description, authority_mode, version, policy)
VALUES (
  'currencies_iso4217',
  'ISO 4217 Currencies',
  'Official currency codes with numeric code and minor unit.',
  'authoritative',
  'v2025.08',
  '{"unique_by":"item_code","case":"upper"}'
)
ON CONFLICT (list_code) DO NOTHING;

-- Seed some items (extend as needed)
WITH L AS (
  SELECT list_id FROM sssr.sssr_list_registry WHERE list_code='currencies_iso4217'
)
INSERT INTO sssr.sssr_list_items (list_id, item_code, labels, aliases, meta, sort_order)
SELECT L.list_id, 'EUR',
       '{"en":"Euro"}'::jsonb,
       '["€","euro"]'::jsonb,
       '{"numeric":978,"minor_unit":2}'::jsonb,
       10
FROM L
ON CONFLICT DO NOTHING;

WITH L AS (
  SELECT list_id FROM sssr.sssr_list_registry WHERE list_code='currencies_iso4217'
)
INSERT INTO sssr.sssr_list_items (list_id, item_code, labels, aliases, meta, sort_order)
SELECT L.list_id, 'USD',
       '{"en":"US Dollar"}'::jsonb,
       '["$","usd"]'::jsonb,
       '{"numeric":840,"minor_unit":2}'::jsonb,
       20
FROM L
ON CONFLICT DO NOTHING;

For bulk seeds, drive inserts from the authoritative ISO table or a staging table.

4.4. Child+Overlay example: countries_iso3166​

Assumptions about the existing countries table (adapt to your real columns):

countries-iso3166-overlay-example.sqlGitHub ↗
-- Example external authoritative table (already exists in the DB)
-- countries(code text PK, name_en text, alpha3 text, numeric int, region text, ...)

-- Register the list (Child / Federated)
INSERT INTO sssr.sssr_list_registry (
  list_code, title, description, authority_mode, version, external_ref, policy
) VALUES (
  'countries_iso3166',
  'ISO 3166 Countries',
  'Country codes from external authoritative table.',
  'federated',
  '2025-08-17',
  '{"table":"public.countries","pk":"code","source":"ISO_3166"}',
  '{"unique_by":"code","immutable":true}'
)
ON CONFLICT (list_code) DO NOTHING;

-- Optional overlay examples
WITH L AS (SELECT list_id FROM sssr.sssr_list_registry WHERE list_code='countries_iso3166')
INSERT INTO sssr.sssr_list_item_overlay (list_id, external_key, labels, meta)
SELECT L.list_id, 'NO', '{"no":"Norge"}'::jsonb, '{"eu_like": false}'::jsonb
FROM L
ON CONFLICT DO NOTHING;

WITH L AS (SELECT list_id FROM sssr.sssr_list_registry WHERE list_code='countries_iso3166')
INSERT INTO sssr.sssr_list_item_overlay (list_id, external_key, labels)
SELECT L.list_id, 'DE', '{"no":"Tyskland"}'::jsonb
FROM L
ON CONFLICT DO NOTHING;

Normalized view for the federated list (this is the contract all consumers will use):

normalized-federated-list-view.sqlGitHub ↗
CREATE OR REPLACE VIEW sssr.v_list_items_countries_iso3166 AS
SELECT
  r.list_id,
  c.code              AS item_code,
  (jsonb_build_object('en', c.name_en) || COALESCE(o.labels, '{}')::jsonb) AS labels,
  COALESCE(o.aliases, '[]'::jsonb) AS aliases,
  (jsonb_build_object('alpha3', c.alpha3, 'numeric', c.numeric, 'region', c.region)
     || COALESCE(o.meta, '{}')::jsonb) AS meta,
  NULL::text  AS parent_code,
  NULL::text  AS path,
  NULL::int   AS sort_order,
  NULL::timestamptz AS valid_from,
  NULL::timestamptz AS valid_to
FROM public.countries c
JOIN sssr.sssr_list_registry r ON r.list_code = 'countries_iso3166'
LEFT JOIN sssr.sssr_list_item_overlay o
       ON o.list_id = r.list_id AND o.external_key = c.code;

4.5. Validation helpers​

4.5.1. Generic dispatcher by list_code​

(Use explicit CASEs for federated lists to avoid unsafe dynamic SQL.)

dispatcher-by-list-code.sqlGitHub ↗
CREATE OR REPLACE FUNCTION sssr.sssr_list_contains(list_code text, item_code text)
RETURNS boolean
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  mode text;
  lid uuid;
  ok  boolean := false;
BEGIN
  SELECT authority_mode, list_id INTO mode, lid
  FROM sssr.sssr_list_registry
  WHERE list_code = sssr.sssr_list_contains.list_code;

  IF mode IS NULL THEN
    RETURN false;
  END IF;

  IF mode = 'authoritative' THEN
    SELECT EXISTS (
      SELECT 1 FROM sssr.sssr_list_items i
      WHERE i.list_id = lid
        AND i.item_code = item_code
        AND COALESCE(i.valid_from, '-infinity') <= now()
        AND COALESCE(i.valid_to,   'infinity')  >= now()
    ) INTO ok;
    RETURN ok;
  END IF;

  -- Federated / Overlay: route to the normalized view by list_code
  CASE list_code
    WHEN 'countries_iso3166' THEN
      SELECT EXISTS (
        SELECT 1 FROM sssr.v_list_items_countries_iso3166 v
        WHERE v.list_id = lid AND v.item_code = item_code
          AND COALESCE(v.valid_from, '-infinity') <= now()
          AND COALESCE(v.valid_to,   'infinity')  >= now()
      ) INTO ok;

    -- Add more CASE branches for each federated list/view you introduce:
    -- WHEN 'nace_rev2' THEN ... (your future v_list_items_nace_rev2)
    ELSE
      ok := false; -- unknown federated list; tighten as needed
  END CASE;

  RETURN ok;
END $$;

4.5.2. Column-level CHECK examples​

column-level-check-examples.sqlGitHub ↗
-- On a table storing organization country codes
ALTER TABLE public.orgs
  ADD CONSTRAINT orgs_country_valid
  CHECK (sssr.sssr_list_contains('countries_iso3166', country_code));

-- On a payments table storing currency codes
ALTER TABLE public.payments
  ADD CONSTRAINT payments_currency_valid
  CHECK (sssr.sssr_list_contains('currencies_iso4217', currency_code));

4.6. How to reference (SSSR style)​

Inline (human)

  • Whole list: [SSSR_REF: sssr:list.countries_iso3166]
  • List item: [SSSR_REF: sssr:list_item.countries_iso3166.NO]

Machine (DB JSON)

machine-db-2.jsonGitHub ↗
{ "registry": "sssr:list_item", "list": "countries_iso3166", "ids": ["NO","DE"] }

4.7. Quick queries that will be used a lot​

fetch-active-currency.sqlGitHub ↗
-- Fetch active currency items (Mother)
SELECT item_code, labels->>'en' AS name_en, meta
FROM sssr.sssr_list_items i
JOIN sssr.sssr_list_registry r USING (list_id)
WHERE r.list_code='currencies_iso4217'
  AND r.lifecycle_status='active'
  AND COALESCE(i.valid_from,'-infinity') <= now()
  AND COALESCE(i.valid_to,'infinity')   >= now()
ORDER BY sort_order NULLS LAST, item_code;

-- Fetch countries (Child+Overlay) via the normalized view
SELECT item_code, labels->>'en' AS name_en, labels->>'no' AS name_no, meta
FROM sssr.v_list_items_countries_iso3166
ORDER BY item_code;

4.8. API sketch​

api-sketch.htmlGitHub ↗
GET /api/lists/currencies_iso4217        -> metadata + items (paged)
GET /api/lists/countries_iso3166         -> metadata + items (paged, via view)
GET /api/lists/countries_iso3166/items?code=NO
POST /api/lists/countries_iso3166/validate { "ids":["NO","ZZ"] } -> { "valid":["NO"], "invalid":["ZZ"] }

5. Policies - sssr_list_registry.policy — what to store​

Think of policy as the per‑list “contract”: identity rules, required fields, validation, and API defaults. Here are two ready‑to‑use examples and a generic template.

**Example 1 **— currencies_iso4217

currencies-iso4217-example-1.jsonGitHub ↗
{
  "unique_by": "item_code",                 
  "case": "upper",                          
  "labels_required_locales": ["en"],       
  "immutable": true,                        
  "schema": {
    "required_meta_keys": ["numeric", "minor_unit"],
    "types": {
      "numeric": "integer",
      "minor_unit": "integer",
      "fund": "boolean"
    },
    "constraints": {
      "numeric": { "min": 1, "max": 999 },
      "minor_unit": { "enum": [0,1,2,3] }
    }
  },
  "api": {
    "default_order": [ {"field":"item_code","direction":"asc"} ],
    "include_fund_by_default": false        
  }
}

Example 2 — languages_deepl

languages-deepl-example-2.jsonGitHub ↗
{
  "unique_by": "item_code",     
  "case": "as-is",              
  "labels_required_locales": ["en"],
  "immutable": true,
  "schema": {
    "required_meta_keys": ["bcp47", "direction"],
    "types": {
      "bcp47": "string",
      "direction": "string",
      "script": "string",
      "region": "string",
      "deepl_code": "string"
    },
    "constraints": {
      "direction": { "enum": ["ltr","rtl"] },
      "bcp47": { "pattern": "^[A-Za-z]{2,3}(-[A-Za-z0-9]{2,8})*$" }
    }
  },
  "api": {
    "default_order": [ {"field":"sort_order","direction":"asc"} ],
    "filterable_meta": ["direction","script","region"]
  }
}

Generic template (for any Mother/Child list)

generic-template.jsonGitHub ↗
{
  "unique_by": "item_code",
  "case": "upper|lower|as-is",
  "labels_required_locales": ["en"],
  "immutable": true,
  "schema": {
    "required_meta_keys": [],
    "types": {},                 
    "constraints": {}            
  },
  "api": {
    "default_order": [ {"field":"item_code","direction":"asc"} ],
    "filterable_meta": [],
    "include_flags": {}          
  }
}

Validate policy at write‑time or via a trigger that runs a tiny JSON‑Schema check; also normalize item_code per case before insert/update.


JSON‑Schema check Enforce policy JSON shape validation in PostgreSQL with a trigger.

The pattern is:

  1. Store a JSON Schema (minimal, just enough to check required keys and types).
  2. Use PL/pgSQL (or PL/Python if you want full JSON Schema validation).
  3. Run the check on INSERT/UPDATE into sssr_list_registry.

Here’s a compact working example in pure PL/pgSQL (no extra extensions):

json-shape-validation.sqlGitHub ↗
-- 1. Minimal schema for policy validation
-- (could also live in a separate table if you want different schemas per list)
CREATE TABLE sssr_policy_schema (
    schema_id text primary key,
    schema_def jsonb not null
);

INSERT INTO sssr_policy_schema (schema_id, schema_def) VALUES
('default', '{
  "required": ["unique_by", "case", "labels_required_locales", "schema", "api"],
  "properties": {
    "unique_by": {"type": "string"},
    "case": {"enum": ["upper","lower","as-is"]},
    "labels_required_locales": {"type": "array"},
    "schema": {"type": "object"},
    "api": {"type": "object"}
  }
}'::jsonb);

-- 2. Trigger function to validate policy JSON
CREATE OR REPLACE FUNCTION validate_policy_json()
RETURNS trigger AS $$
DECLARE
    required_keys text[];
    missing_keys text[];
    schema jsonb;
BEGIN
    -- Load schema (for now always 'default')
    SELECT schema_def INTO schema
    FROM sssr_policy_schema WHERE schema_id = 'default';

    required_keys := ARRAY(
        SELECT jsonb_array_elements_text(schema->'required')
    );

    missing_keys := ARRAY(
        SELECT k FROM unnest(required_keys) k
        WHERE NOT (NEW.policy ? k)
    );

    IF array_length(missing_keys,1) IS NOT NULL THEN
        RAISE EXCEPTION 'Policy JSON missing required keys: %', missing_keys;
    END IF;

    -- Simple case enum check
    IF (NEW.policy->>'case') NOT IN ('upper','lower','as-is') THEN
        RAISE EXCEPTION 'Invalid case value in policy: %', NEW.policy->>'case';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3. Attach trigger to sssr_list_registry
DROP TRIGGER IF EXISTS trg_validate_policy ON sssr_list_registry;

CREATE TRIGGER trg_validate_policy
BEFORE INSERT OR UPDATE ON sssr_list_registry
FOR EACH ROW
EXECUTE FUNCTION validate_policy_json();

6. How SSSR table/column references improve audits​

6.1. Direct Lineage & Provenance​

  • The SSSR stores signals as column-level definitions (sssr:source_table.column_reference) with full metadata (data type, validation rules, dependencies, fallback logic, linked metrics, etc.) .
  • When we link to an asset and include its originating table/column, we’re explicitly documenting where the value came from.
  • This means that in an audit, we don’t need to “reverse engineer” which dataset or field fed a report metric — the provenance is already embedded.

6.2. Immutable Audit Trail​

  • Every SSSR reference is logged in the Audit Logging and Tamper Detection (ALTD) module, alongside routing, validation, and fallback events .
  • Auditors can pull a complete chain:

Metric → SSSR ID → Table.Column → Registry Metadata → Validation/Fallback Logs

  • This chain is immutable (anchored to blockchain for certain events), so we don’t just know where to look — we know why and how it was processed.

6.3. Reverse Lookups for Context​

  • The registry supports reverse lookups: from a metric or audit log, we can see exactly which signals (table+column) were used .
  • That avoids the “backtracking problem” — auditors don’t have to manually open a linking table; the SSSR and audit logs expose direct dependencies.

6.4. Deprecation & Versioning​

  • If a column is later deprecated, the SSSR reference still points to its metadata profile (availability_status=deprecated). Auditors see exactly which version of the field was used at reporting time .

6.4. Traceability Across Engines​

  • Since ZSSR (Smart System Router) and processing engines (SEM, FOGE, DAIM, etc.) all rely on SSSR metadata, any signal processed carries its table/column lineage throughout all computations .
  • That means even if we’re looking at a derived metric (e.g., Scope 3 transport estimate), we can always trace back to the raw source field.

Practical Example Instead of an audit log saying:

Scope 3 Transport Emissions: 125,000 tCO₂e

It will say (simplified):

Metric: Scope 3 Transport
Value: 125,000 tCO₂e
Source: [SSSR_REF: sssr:transport_activity.distance_km @ ROW-12345]
Fallback: sector_average_if_missing
Processed by: SEM → DAIM
Audit Trail: auditlog://signal/scope3.transport/20250714_082231

7. How SSSR & ALTD work together​

7.1. Signal Registration (SSSR)​

  • Every table column is registered as a signal with a unique ID (sssr:table.column).
  • Metadata includes: source_table, column_reference, validation rules, fallback logic, dependencies, linked metrics, etc.

7.2. Routing & Processing (ZSSR)​

  • When a signal is used in a workflow (form, calculation, extrapolation), it’s routed via ZSSR.
  • The routing decision (which engine processed it, whether fallback was used, trust score, etc.) is stored as a Signal Routing Decision Object (SRDO)

7.3. Audit Logging (ALTD)​

  • ALTD records each event with:

    • entity_type (signal, engine, verifier, etc.)
    • entity_id (e.g., sssr:scope3_transport.distance_km)
    • event_type (validated, routed, fallback_triggered, verifier_signed, etc.)
    • event_payload (SRDO, rule outcomes, AI context snapshot)
    • trust_score_at_event, geo_context, linked_audit_ids, and optional blockchain anchors .

  • Example:

audit-logging.jsonGitHub ↗
{
  "audit_id": "a7c9-1234",
  "timestamp": "2025-07-14T08:22:31Z",
  "entity_type": "signal",
  "entity_id": "sssr:transport_activity.distance_km",
  "event_type": "validated",
  "actor_type": "engine",
  "actor_id": "SEM",
  "event_payload": { "srdo_id": "c98e812c" },
  "trust_score_at_event": 0.91,
  "geo_context": "aws.eu-central-1",
  "blockchain_anchor": "hash://…"
}

7.4. Auditor View​

  • Auditors don’t have to “dig through linking tables.”
  • They simply query ALTD for the audit chain of a signal or metric, and it resolves all the way back to the SSSR entry (the actual table/column of origin).


GitHub RepoRequest for Change (RFC)