Jira progress: loading…

ESGX-RES

ESGX Resolver SQL + Execution Flow

1. Purpose

The ESGX resolver is the runtime mechanism that takes:

an authenticated financial institution
an ESGX code
an optional client/resource scope
an optional requested output profile
an optional runtime payload

and resolves them into a governed execution context that can be executed through ZSSR.

The resolver must answer:

does this ESGX exist
is the calling institution approved
does this institution have an active grant
what opcode and operation_key does this ESGX resolve to
what target/artifact path should be used
what output profile is allowed
what final ZCP command should be built

2. Core principle

ESGX is a reusable request profile.
The grant is the client authorization layer.
ZSSR is the mandatory execution gate.

So runtime permission is based on:

Authenticated institution
+ ESGX profile
+ active grant
+ policy validation
+ ZSSR approval

3. Inputs

Recommended resolver inputs:

recommended-resolver-inputs.yamlGitHub ↗
esgx_code: "ESGX_4jbj6c76_1410"
institution_id: "nordbank"
grant_scope_ref: "client_bigcorp"
requested_output_profile: "json"
request_payload:
  latest: true
environment_scope: "production"

Notes

institution_id should come from the authenticated caller context, not be trusted from the raw request body
grant_scope_ref may represent:
- client id
- artifact scope
- tenant scope
- other scope key
requested_output_profile is optional and subject to negotiation rules

4. Resolver stages

Stage 1 — Resolve ESGX binding

This resolves the reusable ESGX profile.

SQL

resolve-esgx-binding.sqlGitHub ↗
SELECT
    e.external_opcode_binding_registry_id,
    e.esgx_code,
    e.opex_hash,
    e.opcode_suffix,
    e.binding_name,
    e.description,
    e.partner_id,
    e.client_id,
    e.tenant_id,
    e.owner_org_id,
    e.operation_key,
    e.opcode,
    e.api_binding_id,
    e.artifact_type,
    e.target_resolution_mode,
    e.target_cmi,
    e.target_cmi_version,
    e.ruleset_profile_code,
    e.auth_profile_code,
    e.execution_mode,
    e.requires_zssr,
    e.idempotency_supported,
    e.partner_api_allowed,
    e.direct_opex_allowed,
    e.internal_api_allowed,
    e.tenant_scope_mode,
    e.caller_scope_mode,
    e.input_contract_type,
    e.output_contract_type,
    e.default_input_payload,
    e.fixed_context,
    e.request_constraints,
    e.response_projection,
    e.route_binding_required,
    e.route_binding_override_allowed,
    e.usage_limit_profile,
    e.security_class,
    e.compliance_impact,
    e.trust_threshold,
    e.status,
    e.lifecycle_status,
    e.valid_from,
    e.valid_to,
    e.profile_owner_mode,
    e.allowed_output_profiles,
    e.default_output_profile,
    e.allow_format_negotiation,
    e.request_profile_kind
FROM zar.external_opcode_binding_registry e
WHERE e.esgx_code = $1
  AND e.status = 'active'
  AND e.lifecycle_status = 'active'
  AND (e.valid_from IS NULL OR e.valid_from <= NOW())
  AND (e.valid_to IS NULL OR e.valid_to >= NOW());

Parameters

$1 = esgx_code

Failure classes

esgx_binding_not_found
esgx_binding_inactive

Stage 2 — Resolve financial institution

This confirms the caller belongs to an approved institution.

SQL

resolve-financial-institution.sqlGitHub ↗
SELECT
    f.financial_institution_registry_id,
    f.institution_id,
    f.legal_name,
    f.display_name,
    f.institution_type,
    f.jurisdiction_code,
    f.country_code,
    f.auth_profile_code,
    f.integration_profile_code,
    f.institution_status,
    f.lifecycle_status,
    f.trust_level,
    f.onboarding_status
FROM zar.financial_institution_registry f
WHERE f.institution_id = $1
  AND f.institution_status = 'active'
  AND f.lifecycle_status = 'active'
  AND f.onboarding_status = 'approved';

Parameters

$1 = authenticated institution_id

Failure classes

institution_not_registered
institution_not_active

Stage 3 — Resolve active grant

This is the client permission layer.

SQL

resolve-active-grant.sqlGitHub ↗
SELECT
    g.external_opcode_access_grant_id,
    g.external_opcode_binding_registry_id,
    g.financial_institution_registry_id,
    g.grant_scope_type,
    g.grant_scope_ref,
    g.granted_by_client_id,
    g.granted_by_tenant_id,
    g.granted_by_org_id,
    g.permitted_use_code,
    g.access_mode,
    g.allowed_data_classes,
    g.allowed_output_profiles,
    g.default_output_profile,
    g.allow_format_negotiation,
    g.allow_parameter_override,
    g.client_constraints,
    g.bank_minimum_requirements,
    g.grant_status,
    g.lifecycle_status,
    g.valid_from,
    g.valid_to,
    g.max_access_count,
    g.access_count,
    g.last_accessed_at
FROM zar.external_opcode_access_grant g
WHERE g.external_opcode_binding_registry_id = $1
  AND g.financial_institution_registry_id = $2
  AND g.grant_status = 'active'
  AND g.lifecycle_status = 'active'
  AND (g.valid_from IS NULL OR g.valid_from <= NOW())
  AND (g.valid_to IS NULL OR g.valid_to >= NOW())
  AND ($3 IS NULL OR g.grant_scope_ref = $3)
  AND (g.max_access_count IS NULL OR g.access_count < g.max_access_count)
ORDER BY g.sort_order, g.external_opcode_access_grant_id
LIMIT 1;

Parameters

$1 = external_opcode_binding_registry_id
$2 = financial_institution_registry_id
$3 = grant_scope_ref or NULL

Failure classes

esgx_access_grant_not_found
esgx_access_grant_inactive
esgx_access_limit_exceeded

Stage 4 — Resolve core opcode and artifact compatibility

This confirms the ESGX profile still resolves to an active core opcode and compatible artifact type.

SQL

resolve-core-opcode-and-artifact-compatibility.sqlGitHub ↗
SELECT
    o.opcode,
    o.operation_key,
    o.readable_name,
    o.category_code,
    o.subcategory_code,
    o.intent_class,
    o.status,
    o.public_api_allowed,
    o.internal_zcp_required,
    o.requires_zssr,
    o.default_execution_mode,
    o.input_contract_type,
    o.output_contract_type,
    o.default_audit_required,
    o.default_human_review_required,
    o.default_retention_class,
    o.default_trust_threshold,
    o.security_class,
    o.compliance_impact,
    o.override_requires_approval,
    b.artifact_type,
    b.binding_status,
    b.exposure_mode,
    b.review_required
FROM zar.opcode_registry o
JOIN zar.opcode_artifact_type_binding b
  ON b.opcode = o.opcode
WHERE o.opcode = $1
  AND o.status IN ('active', 'draft')
  AND b.artifact_type = $2
  AND b.binding_status IN ('active', 'draft')
ORDER BY
    CASE o.status WHEN 'active' THEN 1 ELSE 2 END,
    CASE b.binding_status WHEN 'active' THEN 1 ELSE 2 END,
    b.sort_order
LIMIT 1;

Parameters

$1 = ESGX binding opcode
$2 = ESGX binding artifact_type

Failure classes

opcode_not_resolvable
artifact_type_incompatible

Stage 5 — Resolve API route binding if present

If the ESGX is linked to an API binding, load it for request/response projection and route policy.

SQL

resolve-api-route-binding.sqlGitHub ↗
SELECT
    a.api_route_binding_registry_id,
    a.api_binding_id,
    a.route_template,
    a.http_method,
    a.api_version,
    a.binding_name,
    a.description,
    a.operation_key,
    a.opcode,
    a.artifact_type,
    a.target_resolution_mode,
    a.target_cmi,
    a.target_cmi_version,
    a.input_contract_type,
    a.output_contract_type,
    a.request_mapping_profile,
    a.response_mapping_profile,
    a.ruleset_profile_code,
    a.requires_zssr,
    a.public_api_allowed,
    a.partner_api_allowed,
    a.internal_api_allowed,
    a.execution_mode,
    a.idempotency_supported,
    a.auth_profile_code,
    a.tenant_scope_mode,
    a.route_params_schema,
    a.query_params_schema,
    a.request_body_schema,
    a.response_body_schema,
    a.fixed_context,
    a.default_input_payload,
    a.status,
    a.lifecycle_status
FROM zar.api_route_binding_registry a
WHERE a.api_binding_id = $1
  AND a.status = 'active'
  AND a.lifecycle_status = 'active';

Parameters

$1 = ESGX binding api_binding_id

Failure class

esgx_route_binding_required_but_missing

If route_binding_required = true and no active row is found, fail.

Stage 6 — Resolve effective output profile

Controlled format negotiation should happen here.

Inputs considered

ESGX binding:
- allowed_output_profiles
- default_output_profile
- allow_format_negotiation
Grant:
- allowed_output_profiles
- default_output_profile
- allow_format_negotiation
Request:
- requested_output_profile

Resolution rule

determine intersection of ESGX allowed profiles and grant allowed profiles
if intersection is empty, fail
if request asks for a profile:
- require both ESGX and grant to allow negotiation
- require requested profile in intersection
else choose:
- grant default_output_profile if present
- else ESGX default_output_profile
- else first profile in intersection

Example SQL helper query

example-helper-query.sqlGitHub ↗
If you want to inspect the arrays relationally:

WITH esgx_profiles AS (
    SELECT jsonb_array_elements_text($1::jsonb) AS profile
),
grant_profiles AS (
    SELECT jsonb_array_elements_text($2::jsonb) AS profile
)
SELECT e.profile
FROM esgx_profiles e
JOIN grant_profiles g
  ON g.profile = e.profile
ORDER BY e.profile;

Parameters

$1 = ESGX allowed_output_profiles
$2 = Grant allowed_output_profiles

Failure classes

output_profile_not_allowed
output_profile_negotiation_denied

Stage 7 — Resolve effective payload and constraints

The final input payload is constructed by merging:

ESGX default_input_payload
request payload
constrained by:
- ESGX request_constraints
- Grant client_constraints
- Grant bank_minimum_requirements

Recommended rule

request payload may only override fields if both:
- ESGX allows it
- Grant allows it
client constraints always have final narrowing authority
bank minimum requirements may strengthen requirements but not widen access

Example resolution pseudocode

effectivePayload = merge(esgx.default_input_payload, request.payload)
effectivePayload = applyBindingConstraints(
  effectivePayload,
  esgx.request_constraints
)
effectivePayload = applyGrantConstraints(
  effectivePayload,
  grant.client_constraints
)
effectivePayload = enforceBankMinimumRequirements(
  effectivePayload,
  grant.bank_minimum_requirements
)

Failure classes

request_constraints_failed
client_constraints_failed
bank_minimum_requirements_failed

Stage 8 — Resolve ruleset profile

The effective ruleset profile should be resolved in this precedence:

grant-specific restriction or override, if later supported
ESGX ruleset_profile_code
API route binding ruleset_profile_code
opcode ruleset profile binding default

Example query against opcode ruleset profile binding

example-query-against-opcode-ruleset-profile-binding.sqlGitHub ↗
SELECT
    r.opcode,
    r.artifact_type,
    r.environment_scope,
    r.ruleset_family,
    r.ruleset_profile_code,
    r.binding_strength,
    r.status
FROM zar.opcode_ruleset_profile_binding r
WHERE r.opcode = $1
  AND (r.artifact_type = $2 OR r.artifact_type IS NULL)
  AND r.status IN ('active', 'draft')
ORDER BY
    CASE r.binding_strength
        WHEN 'required' THEN 1
        WHEN 'recommended' THEN 2
        ELSE 3
    END,
    r.sort_order
LIMIT 1;

Parameters

$1 = opcode
$2 = artifact_type

Failure class

ruleset_profile_missing

Stage 9 — Resolve execution target

This depends on target_resolution_mode.

9.1. explicit_cmi

Use ESGX binding target_cmi.

9.2. artifact_type

Resolve by artifact type + opcode + environment.

9.3. manifest_capability

Resolve through approved manifest capability matching.

9.4. api_binding

Resolve using the linked API route binding as the target definition profile.

9.5. workflow_template

Resolve to a pre-approved workflow dispatcher or template artifact.

Generic capability resolution query skeleton

This depends on the manifest tables, but conceptually:

generic-capability-resolution-query-skeleton.sqlGitHub ↗
SELECT
    m.artifact_manifest_registry_id,
    m.cmi,
    m.artifact_type,
    m.component_id,
    m.status,
    m.lifecycle_status,
    m.environment_scope,
    m.operations
FROM zar.artifact_manifest_registry m
WHERE m.artifact_type = $1
  AND m.status = 'active'
  AND m.lifecycle_status = 'active'
  AND (
      m.operations @> jsonb_build_array(
          jsonb_build_object('opcode', $2)
      )
  )
ORDER BY m.sort_order, m.artifact_manifest_registry_id
LIMIT 1;

Parameters

$1 = artifact_type
$2 = opcode

Failure class

target_resolution_failed

Stage 10 — Build effective execution profile

At this point we have:

ESGX binding
financial institution
active grant
active opcode + artifact compatibility
route binding if any
chosen output profile
ruleset profile
resolved target artifact

Now build the effective execution profile.

Effective execution fields

esgx_code
institution_id
grant_id
grant_scope_ref
operation_key
opcode
artifact_type
target_cmi
ruleset_profile_code
input_contract_type
output_contract_type
effective_output_profile
execution_mode
requires_zssr
effective_payload
effective_context
trust/security/compliance metadata

Stage 11 — Build ZCP command envelope

Canonical output

build-zcp-command-envelope-output.yamlGitHub ↗
zcp_version: "0.1"
command_id: "cmd_01J..."
operation_key: "pull_from_external_system"
opcode: "OP_1410"
source:
  invoked_via: "esgx"
  esgx_code: "ESGX_4jbj6c76_1410"
  external_binding_id: 42
  external_access_grant_id: 88
caller:
  institution_id: "nordbank"
  institution_trust_level: "enhanced"
scope:
  grant_scope_type: "client_resource"
  grant_scope_ref: "client_bigcorp"
  permitted_use_code: "covenant_monitoring"
target:
  artifact_type: "connector"
  cmi: "mice.CONN.BIGCORP_QREPORT_PULL.CONNECTOR.1_0_0"
routing:
  via_zssr: true
  ruleset_profile_code: "BIGCORP_QREPORT_PULL_V1"
  execution_mode: "async"
contracts:
  input_contract_type: "external_pull_request"
  output_contract_type: "external_pull_result"
  output_profile: "json"
input:
  payload:
    report_type: "quarterly"
    latest: true
    client_code: "bigcorp"
policy:
  audit_required: true
  human_review_required: false
  retention_class: "compliance_7y"
  min_trust_score: 0.88
  security_class: "high"
  compliance_impact: "medium"
trace:
  api_binding_id: "api.reports.quarterly.pull.post.v1"
  resolved_opcode: "OP_1410"
  resolved_operation_key: "pull_from_external_system"

Stage 12 — Execute through ZSSR

This is mandatory.

Even if the ESGX path bypasses generic API translation, it does not bypass:

ZSSR
ruleset evaluation
trust threshold checks
execution policy
review policy
audit requirements

Failure class

esgx_execution_denied_by_zssr

Stage 13 — Project result to caller

The ZCP result must be projected back according to:

effective output profile
route binding response profile, if any
ESGX response_projection
grant restrictions

Example result

project-result-to-caller-result.jsonGitHub ↗
{
  "status": "success",
  "esgx_code": "ESGX_4jbj6c76_1410",
  "output_profile": "json",
  "data": {
    "report_id": "Q1-2026",
    "published_at": "2026-04-01T10:00:00Z",
    "download_url": "..."
  },
  "execution_id": "exec_01J..."
}

Failure classes

response_projection_failed
output_profile_projection_failed

5. Unified resolver SQL pattern

In practice, we will likely orchestrate this in application code, not one huge SQL statement.

But here is the recommended staged SQL sequence:

Step A

Resolve ESGX binding

Step B

Resolve institution

Step C

Resolve grant

Step D

Resolve opcode + artifact compatibility

Step E

Resolve route binding if needed

Step F

Resolve ruleset profile

Step G

Resolve target artifact

Step H

Build final command envelope in application code

That is cleaner, easier to debug, and easier to audit.

6. Grant access count update

After successful execution or after accepted dispatch, update access count.

SQL

grant-access-count-update.sqlGitHub ↗
UPDATE zar.external_opcode_access_grant
SET
    access_count = access_count + 1,
    last_accessed_at = NOW(),
    updated_at = NOW()
WHERE external_opcode_access_grant_id = $1;

Parameter

$1 = external_opcode_access_grant_id

7. Denial logging

We should later add a dedicated access log or resolver log table, but for now at minimum log these fields in application logs:

esgx_code
institution_id
grant_scope_ref
denial class
denial reason
timestamp
correlation id / request id

This will become very important for banks.

8. Failure classes summary

Standardized classes:

esgx_binding_not_found
esgx_binding_inactive
institution_not_registered
institution_not_active
esgx_access_grant_not_found
esgx_access_grant_inactive
esgx_access_limit_exceeded
opcode_not_resolvable
artifact_type_incompatible
esgx_route_binding_required_but_missing
output_profile_not_allowed
output_profile_negotiation_denied
request_constraints_failed
client_constraints_failed
bank_minimum_requirements_failed
ruleset_profile_missing
target_resolution_failed
esgx_execution_denied_by_zssr
response_projection_failed

9. Recommended execution flow pseudocode

async function resolveEsgxExecution(input: {
  esgxCode: string;
  institutionId: string;
  grantScopeRef?: string | null;
  requestedOutputProfile?: string | null;
  requestPayload?: Record<string, unknown> | null;
}) {
  const esgx = await resolveEsgxBinding(input.esgxCode);
  if (!esgx) throw new ResolverError('esgx_binding_not_found');
  const institution = await resolveFinancialInstitution(input.institutionId);
  if (!institution) throw new ResolverError('institution_not_registered');
  const grant = await resolveAccessGrant(
    esgx.external_opcode_binding_registry_id,
    institution.financial_institution_registry_id,
    input.grantScopeRef ?? null,
  );
  if (!grant) throw new ResolverError('esgx_access_grant_not_found');
  const opcodeBinding = await resolveOpcodeArtifactCompatibility(
    esgx.opcode,
    esgx.artifact_type,
  );
  if (!opcodeBinding) throw new ResolverError('artifact_type_incompatible');
  const apiBinding = esgx.api_binding_id
    ? await resolveApiBinding(esgx.api_binding_id)
    : null;
  if (esgx.route_binding_required && !apiBinding) {
    throw new ResolverError('esgx_route_binding_required_but_missing');
  }
  const outputProfile = resolveEffectiveOutputProfile({
    esgx,
    grant,
    requestedOutputProfile: input.requestedOutputProfile ?? null,
  });
  const effectivePayload = resolveEffectivePayload({
    esgx,
    grant,
    requestPayload: input.requestPayload ?? {},
  });
  const rulesetProfile = await resolveRulesetProfile({
    esgx,
    apiBinding,
    opcode: esgx.opcode,
    artifactType: esgx.artifact_type,
  });
  const target = await resolveExecutionTarget({
    esgx,
    apiBinding,
    opcode: esgx.opcode,
    artifactType: esgx.artifact_type,
  });
  const command = buildZcpCommand({
    esgx,
    institution,
    grant,
    opcodeBinding,
    apiBinding,
    rulesetProfile,
    target,
    outputProfile,
    effectivePayload,
  });
  return command;
}

10. Strategic note

This resolver design gives us:

bank defines broad reusable ESGX profiles
client authorizes actual use through grants
institutions authenticate separately
output format can be negotiated in a controlled way
ZSSR remains the execution gate
one ESGX can be reused across many clients without losing client control

APPENDIX A - Test and Production-Ready TypeScript Service Layer

We'll do it in this order:

Seed zar.financial_institution_registry
Seed real zar.external_opcode_access_grant example data
Build the resolver as a production-ready TypeScript service layer
Wire that service into server.ts

If we build the resolver endpoint before the institution/grant data exists, we might spend time debugging empty-state failures instead of validating real logic.

The resolver needs real rows for:

ESGX binding
financial institution
access grant
output profile negotiation
scope matching

We seed enough real data to exercise the resolver, then build the service layer against actual cases, then expose it as an endpoint.

A.1. Execution plan

Phase 1 — Seed minimum real runtime data

We'll create:

2–3 financial institutions
2–3 access grants tied to your existing ESGX rows

That gives us testable scenarios like:

NordBank can access BigCorp annual snapshot
NordBank can access BigCorp quarterly rolling data
another bank is denied for the same ESGX

Phase 2 — Build resolver as service layer

This should come before endpoint wiring.

We create something like:

resolveEsgxBinding(...)
resolveFinancialInstitution(...)
resolveEsgxGrant(...)
resolveOpcodeArtifactCompatibility(...)
resolveApiBinding(...)
resolveEffectiveOutputProfile(...)
resolveEffectivePayload(...)
buildZcpCommandFromEsgx(...)

Then a top-level orchestrator:

resolveEsgxExecution(...)

This is the cleanest architecture and easiest to test.

Phase 3 — Wire into server.ts

We'll add an endpoint like:

POST /esgx/resolve

and later possibly:

POST /esgx/execute

Where:

/resolve returns the resolved execution profile / ZCP command envelope
/execute actually pushes through ZSSR

That separation is very useful.

APPENDIX B - Test Seed

We’ll create some examples so the resolver actually has real, meaningful scenarios to execute against:

Financial institutions (banks)
Access grants tied to your existing ESGX bindings

Aligned to existing rows:

ESGX_4jbj6c76_1410 → BigCorp quarterly pull ESGX_7md8ka21_0910 → XBRL export ESGX_2kr91pz4_0600 → compute metric ESGX_q8ls5v2n_0810 → human review ESGX_m3cv9t7x_0800 → workflow dispatch

Seed zar.financial_institution_registry

seed-financial-institution-registry.sqlGitHub ↗
INSERT INTO zar.financial_institution_registry (
    institution_id,
    legal_name,
    display_name,
    institution_type,
    jurisdiction_code,
    country_code,
    auth_profile_code,
    integration_profile_code,
    institution_status,
    lifecycle_status,
    trust_level,
    onboarding_status
)
VALUES
-- Primary Nordic bank
(
    'nordbank',
    'NordBank ASA',
    'NordBank',
    'bank',
    'EU',
    'NO',
    'bank_oauth_v1',
    'bank_standard_esgx_v1',
    'active',
    'active',
    'enhanced',
    'approved'
),
-- Secondary EU bank
(
    'eurocredit',
    'EuroCredit Group',
    'EuroCredit',
    'bank',
    'EU',
    'DE',
    'bank_oauth_v1',
    'bank_standard_esgx_v1',
    'active',
    'active',
    'standard',
    'approved'
),
-- Assurance / audit firm (important edge case)
(
    'assuranceco',
    'AssuranceCo Ltd',
    'AssuranceCo',
    'regulator',
    'EU',
    'NL',
    'partner_bearer_v1',
    'assurance_esgx_v1',
    'active',
    'active',
    'enhanced',
    'approved'
);

Seed zar.external_opcode_access_grant

We now define client-controlled access, aligned with our philosophy:

ESGX is reusable Grants define who can use it, on what, and how

Case 1. NordBank → BigCorp quarterly report (🔥 flagship use case)

nordbank-bigcorp-quarterly-report.sqlGitHub ↗
INSERT INTO zar.external_opcode_access_grant (
    external_opcode_binding_registry_id,
    financial_institution_registry_id,
    grant_scope_type,
    grant_scope_ref,
    granted_by_client_id,
    permitted_use_code,
    access_mode,
    allowed_data_classes,
    allowed_output_profiles,
    default_output_profile,
    allow_format_negotiation,
    allow_parameter_override,
    client_constraints,
    bank_minimum_requirements,
    grant_status,
    lifecycle_status,
    valid_from,
    valid_to,
    version,
    sort_order
)
SELECT
    e.external_opcode_binding_registry_id,
    f.financial_institution_registry_id,
    'client_resource',
    'client_bigcorp',
    'client_bigcorp',
    'covenant_monitoring',
    'rolling',
    '["quarterly_report","esg_report"]'::jsonb,
    '["json","pdf","signed_package"]'::jsonb,
    'json',
    TRUE,
    FALSE,
    '{"allow_period_override": false}'::jsonb,
    '{"require_signed_data": true}'::jsonb,
    'active',
    'active',
    NOW(),
    NOW() + INTERVAL '2 years',
    '1_0_0',
    100
FROM zar.external_opcode_binding_registry e
JOIN zar.financial_institution_registry f
  ON f.institution_id = 'nordbank'
WHERE e.esgx_code = 'ESGX_4jbj6c76_1410';

Case 2. NordBank → BigCorp annual / snapshot

nordbank-bigcorp-annual-snapshot.sqlGitHub ↗
INSERT INTO zar.external_opcode_access_grant (
    external_opcode_binding_registry_id,
    financial_institution_registry_id,
    grant_scope_type,
    grant_scope_ref,
    granted_by_client_id,
    permitted_use_code,
    access_mode,
    allowed_data_classes,
    allowed_output_profiles,
    default_output_profile,
    allow_format_negotiation,
    allow_parameter_override,
    client_constraints,
    bank_minimum_requirements,
    grant_status,
    lifecycle_status,
    valid_from,
    valid_to,
    version,
    sort_order
)
SELECT
    e.external_opcode_binding_registry_id,
    f.financial_institution_registry_id,
    'client_resource',
    'client_bigcorp',
    'client_bigcorp',
    'loan_origination',
    'snapshot',
    '["annual_report","esg_report"]'::jsonb,
    '["pdf","xbrl","json"]'::jsonb,
    'pdf',
    TRUE,
    FALSE,
    '{"allow_historical_only": true}'::jsonb,
    '{"minimum_output_profiles":["pdf"]}'::jsonb,
    'active',
    'active',
    NOW(),
    NOW() + INTERVAL '90 days',
    '1_0_0',
    110
FROM zar.external_opcode_binding_registry e
JOIN zar.financial_institution_registry f
  ON f.institution_id = 'nordbank'
WHERE e.esgx_code = 'ESGX_7md8ka21_0910';

Case 3. AssuranceCo → BigCorp assurance support

assuranceco-bigcorp-assurance-support.sqlGitHub ↗
INSERT INTO zar.external_opcode_access_grant (
    external_opcode_binding_registry_id,
    financial_institution_registry_id,
    grant_scope_type,
    grant_scope_ref,
    granted_by_client_id,
    permitted_use_code,
    access_mode,
    allowed_data_classes,
    allowed_output_profiles,
    default_output_profile,
    allow_format_negotiation,
    allow_parameter_override,
    client_constraints,
    bank_minimum_requirements,
    grant_status,
    lifecycle_status,
    valid_from,
    valid_to,
    version,
    sort_order
)
SELECT
    e.external_opcode_binding_registry_id,
    f.financial_institution_registry_id,
    'client_resource',
    'client_bigcorp',
    'client_bigcorp',
    'assurance_support',
    'rolling',
    '["review_request","assurance_evidence"]'::jsonb,
    '["json"]'::jsonb,
    'json',
    FALSE,
    FALSE,
    '{}'::jsonb,
    '{}'::jsonb,
    'active',
    'active',
    NOW(),
    NOW() + INTERVAL '1 year',
    '1_0_0',
    120
FROM zar.external_opcode_binding_registry e
JOIN zar.financial_institution_registry f
  ON f.institution_id = 'assuranceco'
WHERE e.esgx_code = 'ESGX_q8ls5v2n_0810';

Case 4. EuroCredit → same ESGX but restricted format

eurocredit-same-esgx-but-restricted-format.sqlGitHub ↗
INSERT INTO zar.external_opcode_access_grant (
    external_opcode_binding_registry_id,
    financial_institution_registry_id,
    grant_scope_type,
    grant_scope_ref,
    granted_by_client_id,
    permitted_use_code,
    access_mode,
    allowed_data_classes,
    allowed_output_profiles,
    default_output_profile,
    allow_format_negotiation,
    allow_parameter_override,
    client_constraints,
    grant_status,
    lifecycle_status,
    valid_from,
    valid_to
)
SELECT
    e.external_opcode_binding_registry_id,
    f.financial_institution_registry_id,
    'client_resource',
    'client_bigcorp',
    'client_bigcorp',
    'credit_monitoring',
    'read_only',
    '["esg_report"]'::jsonb,
    '["json"]'::jsonb,
    'json',
    FALSE,
    FALSE,
    '{"latest_only": true}'::jsonb,
    'active',
    'active',
    NOW(),
    NOW() + INTERVAL '1 year'
FROM zar.external_opcode_binding_registry e
JOIN zar.financial_institution_registry f
  ON f.institution_id = 'eurocredit'
WHERE e.esgx_code = 'ESGX_4jbj6c76_1410';

Case 5. AssuranceCo → human review workflow

assuranceco-human-review-workflow.sqlGitHub ↗
INSERT INTO zar.external_opcode_access_grant (
    external_opcode_binding_registry_id,
    financial_institution_registry_id,
    grant_scope_type,
    grant_scope_ref,
    granted_by_client_id,
    permitted_use_code,
    access_mode,
    allowed_output_profiles,
    default_output_profile,
    grant_status,
    lifecycle_status,
    valid_from
)
SELECT
    e.external_opcode_binding_registry_id,
    f.financial_institution_registry_id,
    'client_resource',
    'client_bigcorp',
    'client_bigcorp',
    'external_assurance',
    'invoke',
    '["json"]'::jsonb,
    'json',
    'active',
    'active',
    NOW()
FROM zar.external_opcode_binding_registry e
JOIN zar.financial_institution_registry f
  ON f.institution_id = 'assuranceco'
WHERE e.esgx_code = 'ESGX_q8ls5v2n_0810';

Quick validation queries (run these)

Check institutions

quick-validation-query-check-institutions.sqlGitHub ↗
SELECT institution_id, display_name, trust_level
FROM zar.financial_institution_registry;

Check ESGX bindings

quick-validation-query-check-esgx-bindings.sqlGitHub ↗
SELECT esgx_code, operation_key, opcode
FROM zar.external_opcode_binding_registry;

Check grants

quick-validation-query-check-grants.sqlGitHub ↗
SELECT
    g.external_opcode_access_grant_id,
    f.institution_id,
    e.esgx_code,
    g.grant_scope_ref,
    g.allowed_output_profiles,
    g.allow_format_negotiation
FROM zar.external_opcode_access_grant g
JOIN zar.external_opcode_binding_registry e
  ON e.external_opcode_binding_registry_id = g.external_opcode_binding_registry_id
JOIN zar.financial_institution_registry f
  ON f.financial_institution_registry_id = g.financial_institution_registry_id;

What we now have (very important)

We now have real execution scenarios:

Scenario A (🔥 flagship)

NordBank → ESGX quarterly report → BigCorp
Negotiation allowed
Signed data required

Scenario B

EuroCredit → same ESGX
JSON only
No negotiation

Scenario C

AssuranceCo → human review workflow

APPENDIX C - esgxResolver.ts service layer

This is designed as a modular service file so it can plug into our backend cleanly and be tested stage by stage. It is aligned with the SQL and table model we have in ZAR.

esgxResolver.ts

esgxResolver.tsGitHub ↗
type QueryResultRow = Record<string, unknown>;
type Queryable = {
  query: (sql: string, params?: unknown[]) => Promise<{ rows: QueryResultRow[] }>;
};

type PoolClient = Queryable & {
  release: () => void;
};

type Pool = Queryable & {
  connect: () => Promise<PoolClient>;
};

export type EsgxBindingRow = {
  external_opcode_binding_registry_id: number;
  esgx_code: string;
  esgx_hash: string;
  opcode_suffix: string;
  binding_name: string;
  description: string | null;
  partner_id: string | null;
  client_id: string | null;
  tenant_id: string | null;
  owner_org_id: string | null;
  operation_key: string;
  opcode: string;
  api_binding_id: string | null;
  artifact_type: string;
  target_resolution_mode:
    | 'explicit_cmi'
    | 'artifact_type'
    | 'manifest_capability'
    | 'api_binding'
    | 'workflow_template';
  target_cmi: string | null;
  target_cmi_version: string | null;
  ruleset_profile_code: string | null;
  auth_profile_code: string | null;
  execution_mode: 'sync' | 'async' | 'hybrid';
  requires_zssr: boolean;
  idempotency_supported: boolean;
  partner_api_allowed: boolean;
  direct_esgx_allowed: boolean;
  internal_api_allowed: boolean;
  tenant_scope_mode: 'caller_scoped' | 'binding_fixed' | 'payload_scoped' | 'none';
  caller_scope_mode: 'partner_scoped' | 'client_scoped' | 'tenant_scoped' | 'org_scoped' | 'global';
  input_contract_type: string;
  output_contract_type: string;
  default_input_payload: Record<string, unknown>;
  fixed_context: Record<string, unknown>;
  request_constraints: Record<string, unknown>;
  response_projection: Record<string, unknown>;
  route_binding_required: boolean;
  route_binding_override_allowed: boolean;
  usage_limit_profile: string | null;
  security_class: 'low' | 'medium' | 'high' | 'critical';
  compliance_impact: 'low' | 'medium' | 'high' | 'critical';
  trust_threshold: number | null;
  status: string;
  lifecycle_status: string;
  valid_from: string | null;
  valid_to: string | null;
  profile_owner_mode: 'institution_defined' | 'client_defined' | 'joint_defined';
  allowed_output_profiles: string[];
  default_output_profile: string | null;
  allow_format_negotiation: boolean;
  request_profile_kind: 'reusable_bank_profile' | 'client_specific_profile' | 'shared_profile';
};

export type FinancialInstitutionRow = {
  financial_institution_registry_id: number;
  institution_id: string;
  legal_name: string;
  display_name: string;
  institution_type: string;
  jurisdiction_code: string | null;
  country_code: string | null;
  auth_profile_code: string;
  integration_profile_code: string | null;
  institution_status: string;
  lifecycle_status: string;
  trust_level: 'basic' | 'standard' | 'enhanced' | 'strategic';
  onboarding_status: string;
};

export type EsgxAccessGrantRow = {
  external_opcode_access_grant_id: number;
  external_opcode_binding_registry_id: number;
  financial_institution_registry_id: number;
  grant_scope_type: 'client_resource' | 'tenant_scope' | 'artifact_scope' | 'data_class';
  grant_scope_ref: string | null;
  granted_by_client_id: string | null;
  granted_by_tenant_id: string | null;
  granted_by_org_id: string | null;
  permitted_use_code:
    | 'loan_origination'
    | 'covenant_monitoring'
    | 'margin_review'
    | 'assurance_support'
    | 'other';
  access_mode: 'snapshot' | 'rolling';
  allowed_data_classes: string[];
  allowed_output_profiles: string[];
  default_output_profile: string | null;
  allow_format_negotiation: boolean;
  allow_parameter_override: boolean;
  client_constraints: Record<string, unknown>;
  bank_minimum_requirements: Record<string, unknown>;
  grant_status: string;
  lifecycle_status: string;
  valid_from: string | null;
  valid_to: string | null;
  max_access_count: number | null;
  access_count: number;
  last_accessed_at: string | null;
};

export type OpcodeCompatibilityRow = {
  opcode: string;
  operation_key: string;
  readable_name: string;
  category_code: string;
  subcategory_code: string | null;
  intent_class: string;
  status: string;
  public_api_allowed: boolean;
  internal_zcp_required: boolean;
  requires_zssr: boolean;
  default_execution_mode: 'sync' | 'async' | 'hybrid';
  input_contract_type: string;
  output_contract_type: string;
  default_audit_required: boolean;
  default_human_review_required: boolean;
  default_retention_class: string;
  default_trust_threshold: number | null;
  security_class: 'low' | 'medium' | 'high' | 'critical';
  compliance_impact: 'low' | 'medium' | 'high' | 'critical';
  override_requires_approval: boolean;
  artifact_type: string;
  binding_status: string;
  exposure_mode: string | null;
  review_required: boolean | null;
};

export type ApiRouteBindingRow = {
  api_route_binding_registry_id: number;
  api_binding_id: string;
  route_template: string;
  http_method: string;
  api_version: string;
  binding_name: string;
  description: string | null;
  operation_key: string;
  opcode: string;
  artifact_type: string;
  target_resolution_mode: string;
  target_cmi: string | null;
  target_cmi_version: string | null;
  input_contract_type: string;
  output_contract_type: string;
  request_mapping_profile: string | null;
  response_mapping_profile: string | null;
  ruleset_profile_code: string | null;
  requires_zssr: boolean;
  public_api_allowed: boolean;
  partner_api_allowed: boolean;
  internal_api_allowed: boolean;
  execution_mode: 'sync' | 'async' | 'hybrid';
  idempotency_supported: boolean;
  auth_profile_code: string | null;
  tenant_scope_mode: string;
  route_params_schema: Record<string, unknown>;
  query_params_schema: Record<string, unknown>;
  request_body_schema: Record<string, unknown>;
  response_body_schema: Record<string, unknown>;
  fixed_context: Record<string, unknown>;
  default_input_payload: Record<string, unknown>;
  status: string;
  lifecycle_status: string;
};

export type RulesetResolutionRow = {
  opcode: string;
  artifact_type: string | null;
  environment_scope: string | null;
  ruleset_family: string | null;
  ruleset_profile_code: string;
  binding_strength: 'required' | 'recommended' | 'optional';
  status: string;
};

export type TargetResolutionRow = {
  artifact_manifest_registry_id: number;
  cmi: string;
  artifact_type: string;
  component_id: string | null;
  status: string;
  lifecycle_status: string;
  environment_scope: string | null;
  operations: unknown;
};

export type EsgxResolverInput = {
  esgxCode: string;
  institutionId: string;
  grantScopeRef?: string | null;
  requestedOutputProfile?: string | null;
  requestPayload?: Record<string, unknown> | null;
  environmentScope?: string | null;
};

export type EsgxResolvedOutputProfile = {
  chosenProfile: string;
  allowedProfiles: string[];
};

export type ZcpCommandEnvelope = {
  zcp_version: '0.1';
  command_id: string;
  operation_key: string;
  opcode: string;
  source: {
    invoked_via: 'esgx';
    esgx_code: string;
    external_binding_id: number;
    external_access_grant_id: number;
  };
  caller: {
    institution_id: string;
    institution_trust_level: string;
  };
  scope: {
    grant_scope_type: string;
    grant_scope_ref: string | null;
    permitted_use_code: string;
  };
  target: {
    artifact_type: string;
    cmi: string | null;
  };
  routing: {
    via_zssr: boolean;
    ruleset_profile_code: string | null;
    execution_mode: 'sync' | 'async' | 'hybrid';
  };
  contracts: {
    input_contract_type: string;
    output_contract_type: string;
    output_profile: string;
  };
  input: {
    payload: Record<string, unknown>;
  };
  policy: {
    audit_required: boolean;
    human_review_required: boolean;
    retention_class: string;
    min_trust_score: number | null;
    security_class: string;
    compliance_impact: string;
  };
  trace: {
    api_binding_id: string | null;
    resolved_opcode: string;
    resolved_operation_key: string;
  };
};

export type EsgxResolveResult = {
  esgxBinding: EsgxBindingRow;
  institution: FinancialInstitutionRow;
  grant: EsgxAccessGrantRow;
  opcodeCompatibility: OpcodeCompatibilityRow;
  apiBinding: ApiRouteBindingRow | null;
  rulesetProfileCode: string | null;
  target: TargetResolutionRow | null;
  outputProfile: EsgxResolvedOutputProfile;
  effectivePayload: Record<string, unknown>;
  command: ZcpCommandEnvelope;
};

export class EsgxResolverError extends Error {
  readonly code: string;
  readonly details?: Record<string, unknown>;

  constructor(code: string, message?: string, details?: Record<string, unknown>) {
    super(message ?? code);
    this.name = 'EsgxResolverError';
    this.code = code;
    this.details = details;
  }
}

export type EsgxResolverDependencies = {
  pool: Pool;
  generateCommandId?: () => string;
};

function rowToJsonObject<T extends QueryResultRow>(row: T, key: keyof T): Record<string, unknown> {
  const value = row[key];
  if (value && typeof value === 'object' && !Array.isArray(value)) {
    return value as Record<string, unknown>;
  }
  return {};
}

function rowToStringArray<T extends QueryResultRow>(row: T, key: keyof T): string[] {
  const value = row[key];
  if (Array.isArray(value)) {
    return (value as unknown[])
      .map((item) => String(item).trim())
      .filter((v) => v.length > 0);
  }
  return [];
}

function normalizeEsgxBindingRow(row: QueryResultRow): EsgxBindingRow {
  return {
    ...row,
    default_input_payload: rowToJsonObject(row, 'default_input_payload'),
    fixed_context: rowToJsonObject(row, 'fixed_context'),
    request_constraints: rowToJsonObject(row, 'request_constraints'),
    response_projection: rowToJsonObject(row, 'response_projection'),
    allowed_output_profiles: rowToStringArray(row, 'allowed_output_profiles'),
  } as EsgxBindingRow;
}

function normalizeInstitutionRow(row: QueryResultRow): FinancialInstitutionRow {
  return row as FinancialInstitutionRow;
}

function normalizeGrantRow(row: QueryResultRow): EsgxAccessGrantRow {
  return {
    ...row,
    allowed_data_classes: rowToStringArray(row, 'allowed_data_classes'),
    allowed_output_profiles: rowToStringArray(row, 'allowed_output_profiles'),
    client_constraints: rowToJsonObject(row, 'client_constraints'),
    bank_minimum_requirements: rowToJsonObject(row, 'bank_minimum_requirements'),
  } as EsgxAccessGrantRow;
}

function normalizeApiBindingRow(row: QueryResultRow): ApiRouteBindingRow {
  return {
    ...row,
    route_params_schema: rowToJsonObject(row, 'route_params_schema'),
    query_params_schema: rowToJsonObject(row, 'query_params_schema'),
    request_body_schema: rowToJsonObject(row, 'request_body_schema'),
    response_body_schema: rowToJsonObject(row, 'response_body_schema'),
    fixed_context: rowToJsonObject(row, 'fixed_context'),
    default_input_payload: rowToJsonObject(row, 'default_input_payload'),
  } as ApiRouteBindingRow;
}

function assertAllowedProfile(
  binding: EsgxBindingRow,
  grant: EsgxAccessGrantRow,
  requestedOutputProfile?: string | null,
): EsgxResolvedOutputProfile {
  const bindingProfiles = new Set(binding.allowed_output_profiles);
  const grantProfiles = new Set(grant.allowed_output_profiles);
  const intersection = [...bindingProfiles].filter((profile) => grantProfiles.has(profile));

  if (intersection.length === 0) {
    throw new EsgxResolverError('output_profile_not_allowed', 'No common output profiles are allowed.', {
      esgxCode: binding.esgx_code,
      grantId: grant.external_opcode_access_grant_id,
    });
  }

  if (requestedOutputProfile) {
    if (!binding.allow_format_negotiation || !grant.allow_format_negotiation) {
      throw new EsgxResolverError(
        'output_profile_negotiation_denied',
        'Requested output profile negotiation is not allowed.',
        { requestedOutputProfile },
      );
    }

    if (!intersection.includes(requestedOutputProfile)) {
      throw new EsgxResolverError('output_profile_not_allowed', 'Requested output profile is not allowed.', {
        requestedOutputProfile,
        allowedProfiles: intersection,
      });
    }

    return {
      chosenProfile: requestedOutputProfile,
      allowedProfiles: intersection,
    };
  }

  const chosenProfile =
    grant.default_output_profile || binding.default_output_profile || intersection[0];

  if (!chosenProfile || !intersection.includes(chosenProfile)) {
    throw new EsgxResolverError('output_profile_not_allowed', 'Default output profile is invalid.', {
      chosenProfile,
      allowedProfiles: intersection,
    });
  }

  return {
    chosenProfile,
    allowedProfiles: intersection,
  };
}

function mergeObjects(
  base: Record<string, unknown>,
  patch: Record<string, unknown>,
): Record<string, unknown> {
  return { ...base, ...patch };
}

function resolveEffectivePayload(
  binding: EsgxBindingRow,
  grant: EsgxAccessGrantRow,
  requestPayload?: Record<string, unknown> | null,
): Record<string, unknown> {
  const payload = mergeObjects(binding.default_input_payload, requestPayload ?? {});

  if (!grant.allow_parameter_override && requestPayload) {
    for (const key of Object.keys(requestPayload)) {
      if (!(key in binding.default_input_payload)) {
        continue;
      }
      if (binding.default_input_payload[key] !== requestPayload[key]) {
        throw new EsgxResolverError(
          'client_constraints_failed',
          `Parameter override is not allowed for key: ${key}`,
          { key },
        );
      }
    }
  }

  return payload;
}

function defaultGenerateCommandId(): string {
  const random = Math.random().toString(36).slice(2, 10);
  return `cmd_${Date.now()}_${random}`;
}

export class EsgxResolverService {
  private readonly pool: Pool;
  private readonly generateCommandId: () => string;

  constructor(deps: EsgxResolverDependencies) {
    this.pool = deps.pool;
    this.generateCommandId = deps.generateCommandId ?? defaultGenerateCommandId;
  }

  async resolveEsgxBinding(client: Pool | PoolClient, esgxCode: string): Promise<EsgxBindingRow> {
    const sql = `
      SELECT
          e.external_opcode_binding_registry_id,
          e.esgx_code,
          e.esgx_hash,
          e.opcode_suffix,
          e.binding_name,
          e.description,
          e.partner_id,
          e.client_id,
          e.tenant_id,
          e.owner_org_id,
          e.operation_key,
          e.opcode,
          e.api_binding_id,
          e.artifact_type,
          e.target_resolution_mode,
          e.target_cmi,
          e.target_cmi_version,
          e.ruleset_profile_code,
          e.auth_profile_code,
          e.execution_mode,
          e.requires_zssr,
          e.idempotency_supported,
          e.partner_api_allowed,
          e.direct_esgx_allowed,
          e.internal_api_allowed,
          e.tenant_scope_mode,
          e.caller_scope_mode,
          e.input_contract_type,
          e.output_contract_type,
          e.default_input_payload,
          e.fixed_context,
          e.request_constraints,
          e.response_projection,
          e.route_binding_required,
          e.route_binding_override_allowed,
          e.usage_limit_profile,
          e.security_class,
          e.compliance_impact,
          e.trust_threshold,
          e.status,
          e.lifecycle_status,
          e.valid_from,
          e.valid_to,
          e.profile_owner_mode,
          e.allowed_output_profiles,
          e.default_output_profile,
          e.allow_format_negotiation,
          e.request_profile_kind
      FROM zar.external_opcode_binding_registry e
      WHERE e.esgx_code = $1
        AND e.status = 'active'
        AND e.lifecycle_status = 'active'
        AND (e.valid_from IS NULL OR e.valid_from <= NOW())
        AND (e.valid_to IS NULL OR e.valid_to >= NOW())
      LIMIT 1;
    `;

    const result = await client.query(sql, [esgxCode]);
    const row = result.rows[0];

    if (!row) {
      throw new EsgxResolverError('esgx_binding_not_found', 'ESGX binding not found or inactive.', {
        esgxCode,
      });
    }

    return normalizeEsgxBindingRow(row);
  }

  async resolveFinancialInstitution(
    client: Pool | PoolClient,
    institutionId: string,
  ): Promise<FinancialInstitutionRow> {
    const sql = `
      SELECT
          f.financial_institution_registry_id,
          f.institution_id,
          f.legal_name,
          f.display_name,
          f.institution_type,
          f.jurisdiction_code,
          f.country_code,
          f.auth_profile_code,
          f.integration_profile_code,
          f.institution_status,
          f.lifecycle_status,
          f.trust_level,
          f.onboarding_status
      FROM zar.financial_institution_registry f
      WHERE f.institution_id = $1
        AND f.institution_status = 'active'
        AND f.lifecycle_status = 'active'
        AND f.onboarding_status = 'approved'
      LIMIT 1;
    `;

    const result = await client.query(sql, [institutionId]);
    const row = result.rows[0];

    if (!row) {
      throw new EsgxResolverError('institution_not_registered', 'Financial institution is not registered or active.', {
        institutionId,
      });
    }

    return normalizeInstitutionRow(row);
  }

  async resolveEsgxGrant(
    client: Pool | PoolClient,
    params: {
      externalOpcodeBindingRegistryId: number;
      financialInstitutionRegistryId: number;
      grantScopeRef?: string | null;
    },
  ): Promise<EsgxAccessGrantRow> {
    const sql = `
      SELECT
          g.external_opcode_access_grant_id,
          g.external_opcode_binding_registry_id,
          g.financial_institution_registry_id,
          g.grant_scope_type,
          g.grant_scope_ref,
          g.granted_by_client_id,
          g.granted_by_tenant_id,
          g.granted_by_org_id,
          g.permitted_use_code,
          g.access_mode,
          g.allowed_data_classes,
          g.allowed_output_profiles,
          g.default_output_profile,
          g.allow_format_negotiation,
          g.allow_parameter_override,
          g.client_constraints,
          g.bank_minimum_requirements,
          g.grant_status,
          g.lifecycle_status,
          g.valid_from,
          g.valid_to,
          g.max_access_count,
          g.access_count,
          g.last_accessed_at
      FROM zar.external_opcode_access_grant g
      WHERE g.external_opcode_binding_registry_id = $1
        AND g.financial_institution_registry_id = $2
        AND g.grant_status = 'active'
        AND g.lifecycle_status = 'active'
        AND (g.valid_from IS NULL OR g.valid_from <= NOW())
        AND (g.valid_to IS NULL OR g.valid_to >= NOW())
        AND ($3::text IS NULL OR g.grant_scope_ref = $3)
        AND (g.max_access_count IS NULL OR g.access_count < g.max_access_count)
      ORDER BY g.sort_order, g.external_opcode_access_grant_id
      LIMIT 1;
    `;

    const result = await client.query(sql, [
      params.externalOpcodeBindingRegistryId,
      params.financialInstitutionRegistryId,
      params.grantScopeRef ?? null,
    ]);

    const row = result.rows[0];

    if (!row) {
      throw new EsgxResolverError('esgx_access_grant_not_found', 'No active access grant found for ESGX and institution.', {
        externalOpcodeBindingRegistryId: params.externalOpcodeBindingRegistryId,
        financialInstitutionRegistryId: params.financialInstitutionRegistryId,
        grantScopeRef: params.grantScopeRef ?? null,
      });
    }

    return normalizeGrantRow(row);
  }

  async resolveOpcodeCompatibility(
    client: Pool | PoolClient,
    opcode: string,
    artifactType: string,
  ): Promise<OpcodeCompatibilityRow> {
    const sql = `
      SELECT
          o.opcode,
          o.operation_key,
          o.readable_name,
          o.category_code,
          o.subcategory_code,
          o.intent_class,
          o.status,
          o.public_api_allowed,
          o.internal_zcp_required,
          o.requires_zssr,
          o.default_execution_mode,
          o.input_contract_type,
          o.output_contract_type,
          o.default_audit_required,
          o.default_human_review_required,
          o.default_retention_class,
          o.default_trust_threshold,
          o.security_class,
          o.compliance_impact,
          o.override_requires_approval,
          b.artifact_type,
          b.binding_status,
          b.exposure_mode,
          b.review_required
      FROM zar.opcode_registry o
      JOIN zar.opcode_artifact_type_binding b
        ON b.opcode = o.opcode
      WHERE o.opcode = $1
        AND o.status IN ('active', 'draft')
        AND b.artifact_type = $2
        AND b.binding_status IN ('active', 'draft')
      ORDER BY
        CASE o.status WHEN 'active' THEN 1 ELSE 2 END,
        CASE b.binding_status WHEN 'active' THEN 1 ELSE 2 END,
        b.sort_order
      LIMIT 1;
    `;

    const result = await client.query(sql, [opcode, artifactType]);
    const row = result.rows[0];

    if (!row) {
      throw new EsgxResolverError('artifact_type_incompatible', 'Opcode is not compatible with requested artifact type.', {
        opcode,
        artifactType,
      });
    }

    return row as OpcodeCompatibilityRow;
  }

  async resolveApiBinding(
    client: Pool | PoolClient,
    apiBindingId: string,
  ): Promise<ApiRouteBindingRow | null> {
    const sql = `
      SELECT
          a.api_route_binding_registry_id,
          a.api_binding_id,
          a.route_template,
          a.http_method,
          a.api_version,
          a.binding_name,
          a.description,
          a.operation_key,
          a.opcode,
          a.artifact_type,
          a.target_resolution_mode,
          a.target_cmi,
          a.target_cmi_version,
          a.input_contract_type,
          a.output_contract_type,
          a.request_mapping_profile,
          a.response_mapping_profile,
          a.ruleset_profile_code,
          a.requires_zssr,
          a.public_api_allowed,
          a.partner_api_allowed,
          a.internal_api_allowed,
          a.execution_mode,
          a.idempotency_supported,
          a.auth_profile_code,
          a.tenant_scope_mode,
          a.route_params_schema,
          a.query_params_schema,
          a.request_body_schema,
          a.response_body_schema,
          a.fixed_context,
          a.default_input_payload,
          a.status,
          a.lifecycle_status
      FROM zar.api_route_binding_registry a
      WHERE a.api_binding_id = $1
        AND a.status = 'active'
        AND a.lifecycle_status = 'active'
      LIMIT 1;
    `;

    const result = await client.query(sql, [apiBindingId]);
    const row = result.rows[0];
    return row ? normalizeApiBindingRow(row) : null;
  }

  async resolveRulesetProfile(
    client: Pool | PoolClient,
    params: {
      binding: EsgxBindingRow;
      apiBinding: ApiRouteBindingRow | null;
    },
  ): Promise<string | null> {
    if (params.binding.ruleset_profile_code) {
      return params.binding.ruleset_profile_code;
    }

    if (params.apiBinding?.ruleset_profile_code) {
      return params.apiBinding.ruleset_profile_code;
    }

    const sql = `
      SELECT
          r.opcode,
          r.artifact_type,
          r.environment_scope,
          r.ruleset_family,
          r.ruleset_profile_code,
          r.binding_strength,
          r.status
      FROM zar.opcode_ruleset_profile_binding r
      WHERE r.opcode = $1
        AND (r.artifact_type = $2 OR r.artifact_type IS NULL)
        AND r.status IN ('active', 'draft')
      ORDER BY
          CASE r.binding_strength
              WHEN 'required' THEN 1
              WHEN 'recommended' THEN 2
              ELSE 3
          END,
          r.sort_order
      LIMIT 1;
    `;

    const result = await client.query(sql, [params.binding.opcode, params.binding.artifact_type]);
    const row = result.rows[0] as RulesetResolutionRow | undefined;
    return row?.ruleset_profile_code ?? null;
  }

  async resolveExecutionTarget(
    client: Pool | PoolClient,
    params: {
      binding: EsgxBindingRow;
      apiBinding: ApiRouteBindingRow | null;
      environmentScope?: string | null;
    },
  ): Promise<TargetResolutionRow | null> {
    if (params.binding.target_resolution_mode === 'explicit_cmi' && params.binding.target_cmi) {
      return {
        artifact_manifest_registry_id: -1,
        cmi: params.binding.target_cmi,
        artifact_type: params.binding.artifact_type,
        component_id: null,
        status: 'active',
        lifecycle_status: 'active',
        environment_scope: params.environmentScope ?? null,
        operations: null,
      };
    }

    const sql = `
      SELECT
          m.artifact_manifest_registry_id,
          m.cmi,
          m.artifact_type,
          m.component_id,
          m.status,
          m.lifecycle_status,
          m.environment_scope,
          m.operations
      FROM zar.artifact_manifest_registry m
      WHERE m.artifact_type = $1
        AND m.status = 'active'
        AND m.lifecycle_status = 'active'
        AND ($3::text IS NULL OR m.environment_scope = $3 OR m.environment_scope IS NULL)
        AND (
            m.operations @> jsonb_build_array(
                jsonb_build_object('opcode', $2)
            )
        )
      ORDER BY m.sort_order, m.artifact_manifest_registry_id
      LIMIT 1;
    `;

    const result = await client.query(sql, [
      params.binding.artifact_type,
      params.binding.opcode,
      params.environmentScope ?? null,
    ]);

    const row = result.rows[0];
    if (!row && params.binding.target_resolution_mode !== 'api_binding') {
      throw new EsgxResolverError('target_resolution_failed', 'No target artifact could be resolved.', {
        esgxCode: params.binding.esgx_code,
        artifactType: params.binding.artifact_type,
        opcode: params.binding.opcode,
      });
    }

    return (row as TargetResolutionRow | undefined) ?? null;
  }

  buildZcpCommand(params: {
    binding: EsgxBindingRow;
    institution: FinancialInstitutionRow;
    grant: EsgxAccessGrantRow;
    opcodeCompatibility: OpcodeCompatibilityRow;
    apiBinding: ApiRouteBindingRow | null;
    rulesetProfileCode: string | null;
    target: TargetResolutionRow | null;
    outputProfile: EsgxResolvedOutputProfile;
    effectivePayload: Record<string, unknown>;
  }): ZcpCommandEnvelope {
    const minTrust =
      params.binding.trust_threshold ?? params.opcodeCompatibility.default_trust_threshold ?? null;

    return {
      zcp_version: '0.1',
      command_id: this.generateCommandId(),
      operation_key: params.binding.operation_key,
      opcode: params.binding.opcode,
      source: {
        invoked_via: 'esgx',
        esgx_code: params.binding.esgx_code,
        external_binding_id: params.binding.external_opcode_binding_registry_id,
        external_access_grant_id: params.grant.external_opcode_access_grant_id,
      },
      caller: {
        institution_id: params.institution.institution_id,
        institution_trust_level: params.institution.trust_level,
      },
      scope: {
        grant_scope_type: params.grant.grant_scope_type,
        grant_scope_ref: params.grant.grant_scope_ref,
        permitted_use_code: params.grant.permitted_use_code,
      },
      target: {
        artifact_type: params.binding.artifact_type,
        cmi: params.target?.cmi ?? params.binding.target_cmi ?? null,
      },
      routing: {
        via_zssr: params.binding.requires_zssr,
        ruleset_profile_code: params.rulesetProfileCode,
        execution_mode: params.binding.execution_mode,
      },
      contracts: {
        input_contract_type: params.binding.input_contract_type,
        output_contract_type: params.binding.output_contract_type,
        output_profile: params.outputProfile.chosenProfile,
      },
      input: {
        payload: params.effectivePayload,
      },
      policy: {
        audit_required: params.opcodeCompatibility.default_audit_required,
        human_review_required: params.opcodeCompatibility.default_human_review_required,
        retention_class: params.opcodeCompatibility.default_retention_class,
        min_trust_score: minTrust,
        security_class: params.binding.security_class,
        compliance_impact: params.binding.compliance_impact,
      },
      trace: {
        api_binding_id: params.binding.api_binding_id,
        resolved_opcode: params.binding.opcode,
        resolved_operation_key: params.binding.operation_key,
      },
    };
  }

  async incrementGrantAccessCount(
    client: Pool | PoolClient,
    externalOpcodeAccessGrantId: number,
  ): Promise<void> {
    const sql = `
      UPDATE zar.external_opcode_access_grant
      SET
          access_count = access_count + 1,
          last_accessed_at = NOW(),
          updated_at = NOW()
      WHERE external_opcode_access_grant_id = $1;
    `;

    await client.query(sql, [externalOpcodeAccessGrantId]);
  }

  async resolveEsgxExecution(input: EsgxResolverInput): Promise<EsgxResolveResult> {
    const client = await this.pool.connect();

    try {
      const esgxBinding = await this.resolveEsgxBinding(client, input.esgxCode);
      const institution = await this.resolveFinancialInstitution(client, input.institutionId);

      if (esgxBinding.auth_profile_code && institution.auth_profile_code !== esgxBinding.auth_profile_code) {
        throw new EsgxResolverError('institution_not_active', 'Institution auth profile does not match ESGX binding requirements.', {
          institutionId: input.institutionId,
          bindingAuthProfile: esgxBinding.auth_profile_code,
          institutionAuthProfile: institution.auth_profile_code,
        });
      }

      const grant = await this.resolveEsgxGrant(client, {
        externalOpcodeBindingRegistryId: esgxBinding.external_opcode_binding_registry_id,
        financialInstitutionRegistryId: institution.financial_institution_registry_id,
        grantScopeRef: input.grantScopeRef ?? null,
      });

      const opcodeCompatibility = await this.resolveOpcodeCompatibility(
        client,
        esgxBinding.opcode,
        esgxBinding.artifact_type,
      );

      const apiBinding = esgxBinding.api_binding_id
        ? await this.resolveApiBinding(client, esgxBinding.api_binding_id)
        : null;

      if (esgxBinding.route_binding_required && !apiBinding) {
        throw new EsgxResolverError(
          'esgx_route_binding_required_but_missing',
          'ESGX binding requires an active API binding, but none was found.',
          { apiBindingId: esgxBinding.api_binding_id },
        );
      }

      const outputProfile = assertAllowedProfile(
        esgxBinding,
        grant,
        input.requestedOutputProfile ?? null,
      );

      const effectivePayload = resolveEffectivePayload(
        esgxBinding,
        grant,
        input.requestPayload ?? {},
      );

      const rulesetProfileCode = await this.resolveRulesetProfile(client, {
        binding: esgxBinding,
        apiBinding,
      });

      const target = await this.resolveExecutionTarget(client, {
        binding: esgxBinding,
        apiBinding,
        environmentScope: input.environmentScope ?? null,
      });

      const command = this.buildZcpCommand({
        binding: esgxBinding,
        institution,
        grant,
        opcodeCompatibility,
        apiBinding,
        rulesetProfileCode,
        target,
        outputProfile,
        effectivePayload,
      });

      return {
        esgxBinding,
        institution,
        grant,
        opcodeCompatibility,
        apiBinding,
        rulesetProfileCode,
        target,
        outputProfile,
        effectivePayload,
        command,
      };
    } finally {
      client.release();
    }
  }
}

GitHub Repo Request for Change (RFC)

1. Purpose​

2. Core principle​

3. Inputs​

4. Resolver stages​

9.1. explicit_cmi​

9.2. artifact_type​

9.3. manifest_capability​

9.4. api_binding​

9.5. workflow_template​

5. Unified resolver SQL pattern​

6. Grant access count update​

7. Denial logging​

8. Failure classes summary​

9. Recommended execution flow pseudocode​

10. Strategic note​

APPENDIX A - Test and Production-Ready TypeScript Service Layer​

A.1. Execution plan​

APPENDIX B - Test Seed​

APPENDIX C - esgxResolver.ts service layer​

1. Purpose

2. Core principle

3. Inputs

4. Resolver stages

9.1. explicit_cmi

9.2. artifact_type

9.3. manifest_capability

9.4. api_binding

9.5. workflow_template

5. Unified resolver SQL pattern

6. Grant access count update

7. Denial logging

8. Failure classes summary

9. Recommended execution flow pseudocode

10. Strategic note

APPENDIX A - Test and Production-Ready TypeScript Service Layer

A.1. Execution plan

APPENDIX B - Test Seed

APPENDIX C - esgxResolver.ts service layer