Shared data dictionary for tenants, idempotency, audit, module dictionaries, and Incept provenance.

{
  "tenant_id": "0d4ce2f4-1c42-4f3c-9f0d-03fb7f5271d3",
  "tenant_key": "north-valley",
  "display_name": "North Valley School District",
  "status": "active",
  "metadata": {
    "region": "us-east",
    "externalRefs": [
      {
        "system": "crm",
        "ref": "acct_7x9"
      }
    ]
  },
  "created_at": "2026-05-21T12:00:00Z",
  "updated_at": "2026-05-21T12:00:00Z"
}

select tenant_id, status from platform.tenant where tenant_key = $1;

insert into platform.tenant (tenant_key, display_name, status, metadata) values ($1, $2, 'provisioning', '{}'::jsonb) returning tenant_id;

select t.tenant_key, count(a.audit_log_id) from platform.tenant t left join platform.audit_log a using (tenant_id) where t.status = 'active' group by t.tenant_key;

create table platform.tenant (
  tenant_id uuid primary key default gen_random_uuid(),
  tenant_key text not null unique,
  display_name text not null,
  status text not null default 'provisioning',
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint tenant_key_format_ck
    check (tenant_key ~ '^[a-z0-9]([a-z0-9-]{1,62}[a-z0-9])$'),
  constraint tenant_display_name_present_ck
    check (length(btrim(display_name)) between 1 and 160),
  constraint tenant_status_ck
    check (status in ('provisioning', 'active', 'suspended', 'archived')),
  constraint tenant_metadata_object_ck
    check (jsonb_typeof(metadata) = 'object'),
  constraint tenant_updated_after_created_ck
    check (updated_at >= created_at)
);

create index tenant_status_idx on platform.tenant(status);
create index tenant_updated_at_idx on platform.tenant(updated_at);

comment on table platform.tenant is 'A tenant is the platform-wide owner of content, learner runtime records, integrations, and API access. QTI used qti.tenant as the first-module pattern; this table promotes that boundary so QTI, OneRoster, Caliper, CASE, and future modules all point to the same customer/workspace row.';
comment on column platform.tenant.tenant_id is 'Stable database identifier for one customer/workspace boundary. This is the value module tables reference and tenant-scoped JWTs must match.';
comment on column platform.tenant.tenant_key is 'Human-stable lookup key for routes, local tooling, logs, and examples. It is a convenience key, not an authorization secret.';
comment on column platform.tenant.display_name is 'Customer-facing label shown in admin tools and documentation examples.';
comment on column platform.tenant.status is 'Tenant lifecycle state that tells modules whether ordinary tenant-scoped writes may proceed.';
comment on column platform.tenant.metadata is 'Small redacted operational facts about the tenant that do not deserve first-class columns yet.';
comment on column platform.tenant.created_at is 'Timestamp when the tenant row was inserted.';
comment on column platform.tenant.updated_at is 'Timestamp when the tenant row was last changed.';

{
  "idempotency_key_id": "b81db6f4-c7ea-4757-b5aa-87570f7ad119",
  "tenant_id": "0d4ce2f4-1c42-4f3c-9f0d-03fb7f5271d3",
  "module": "qti",
  "surface": "1edtech",
  "method": "POST",
  "route_template": "/tenants/{tenant_id}/qti/packages",
  "operation_id": "qti.packages.import",
  "idempotency_key": "pkg-upload-2026-05-21-001",
  "request_hash": "sha256:c3def4353e3f07428c94ff3675ed7f0007811dfd7fcbf7044df6c7800f8bd478",
  "canonical_request_hash": "sha256:c3def4353e3f07428c94ff3675ed7f0007811dfd7fcbf7044df6c7800f8bd478",
  "claim_protocol": null,
  "status": "completed",
  "response_status": 201,
  "response_body": {
    "packageId": "7f2e4dd2-c147-49ea-af77-41a6fdd70980",
    "importStatus": "imported"
  },
  "response_headers": {},
  "resource_type": "qti.content_package",
  "resource_id": "7f2e4dd2-c147-49ea-af77-41a6fdd70980",
  "first_request_id": "req_20260521_0001",
  "lease_token": null,
  "locked_until": null,
  "expires_at": "2026-05-22T12:01:00Z",
  "created_at": "2026-05-21T12:01:00Z",
  "updated_at": "2026-05-21T12:01:08Z"
}

-- Claim/replay/conflict decision for one retryable QTI POST. Run inside the same transaction before side effects.
-- The INSERT uses ON CONFLICT DO NOTHING on the seven-column unique scope. If an implementation uses plain INSERT instead,
-- catch PostgreSQL unique_violation SQLSTATE 23505 for that same constraint, roll back, and run this decision query before work.
-- If a concurrent uncommitted claim wins the unique race and ON CONFLICT DO NOTHING makes inserted/existing return no row,
-- roll back and retry this decision query once before doing work; if the retry still returns no row, return 409 idempotency_in_progress.
-- Branch response mapping:
--   claimed -> first claimant; perform the mutation and later finalize this row.
--   stale_lock_reclaimed -> same-hash stale lease was reset; perform the mutation only after the safe-retry precondition is true, then finalize this row.
--   replay -> return stored response_status/response_body.
--   conflict -> 409 Problem code idempotency_conflict.
--   already_in_progress -> 409 Problem code idempotency_in_progress with Retry-After from locked_until.
--   commit_uncertain -> 500 Problem code idempotency_commit_uncertain; support reconciliation required.
--   expired -> 409 Problem code idempotency_key_expired.
with inserted as (
  insert into platform.idempotency_key (
    tenant_id, module, surface, method, route_template, operation_id,
    idempotency_key, request_hash, first_request_id, locked_until, expires_at
  )
  values (
    $1, $2, $3, $4, $5, $6,
    $7, $8, $9, now() + interval '30 seconds', now() + interval '24 hours'
  )
  on conflict (tenant_id, module, surface, method, route_template, operation_id, idempotency_key)
  do nothing
  returning
    idempotency_key_id, request_hash, status, response_status, response_body,
    locked_until, 'claimed'::text as branch
),
existing as (
  select
    idempotency_key_id, request_hash, status, response_status, response_body,
    locked_until
  from platform.idempotency_key
  where tenant_id = $1
    and module = $2
    and surface = $3
    and method = $4
    and route_template = $5
    and operation_id = $6
    and idempotency_key = $7
    and not exists (select 1 from inserted)
  for update
),
classified as (
  select *,
    case
      when request_hash <> $8 then 'conflict'
      when status = 'commit_uncertain' then 'commit_uncertain'
      when status in ('completed', 'failed_permanent') then 'replay'
      when status = 'expired' then 'expired'
      when status in ('in_progress', 'failed_transient')
        and (locked_until is null or locked_until <= now()) then 'stale_lock_reclaimed'
      else 'already_in_progress'
    end as branch
  from existing
),
stale_reclaim as (
  update platform.idempotency_key target
  set status = 'in_progress',
      locked_until = now() + interval '30 seconds',
      updated_at = now()
  from classified
  where target.idempotency_key_id = classified.idempotency_key_id
    and classified.branch = 'stale_lock_reclaimed'
  returning
    target.idempotency_key_id, target.request_hash, target.status,
    target.response_status, target.response_body, target.locked_until,
    'stale_lock_reclaimed'::text as branch
),
decision as (
  select * from inserted
  union all
  select * from stale_reclaim
  union all
  select
    idempotency_key_id, request_hash, status, response_status, response_body,
    locked_until, branch
  from classified
  where branch <> 'stale_lock_reclaimed'
)
select
  idempotency_key_id,
  branch as idempotency_decision,
  case
    when branch = 'replay' then response_status
    when branch = 'commit_uncertain' then 500
    when branch in ('conflict', 'already_in_progress', 'expired') then 409
    else null
  end as replay_or_problem_status,
  case when branch = 'replay' then response_body else null end as replay_body,
  case
    when branch = 'conflict' then 'idempotency_conflict'
    when branch = 'already_in_progress' then 'idempotency_in_progress'
    when branch = 'commit_uncertain' then 'idempotency_commit_uncertain'
    when branch = 'expired' then 'idempotency_key_expired'
    else null
  end as problem_code
from decision;

update platform.idempotency_key set status = 'completed', response_status = $2, response_body = $3, resource_type = $4, resource_id = $5, locked_until = null, updated_at = now() where idempotency_key_id = $1 and status = 'in_progress';

update platform.idempotency_key set status = 'failed_permanent', response_status = $2, response_body = $3, locked_until = null, updated_at = now() where idempotency_key_id = $1 and status = 'in_progress';

update platform.idempotency_key set status = 'failed_transient', response_status = null, response_body = null, locked_until = null, updated_at = now() where idempotency_key_id = $1 and status = 'in_progress';

update platform.idempotency_key set status = 'commit_uncertain', response_status = null, response_body = null, locked_until = null, updated_at = now() where idempotency_key_id = $1 and status = 'in_progress';

create table platform.idempotency_key (
  idempotency_key_id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references platform.tenant(tenant_id),
  module text not null,
  surface text not null,
  method text not null,
  route_template text not null,
  operation_id text not null,
  idempotency_key text not null,
  request_hash text not null,
  canonical_request_hash text,
  claim_protocol text,
  status text not null default 'in_progress',
  response_status integer,
  response_body jsonb,
  response_headers jsonb not null default '{}'::jsonb,
  resource_type text,
  resource_id text,
  first_request_id text not null,
  lease_token text,
  locked_until timestamptz,
  expires_at timestamptz not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint idempotency_module_ck
    check (module in ('platform', 'incept', 'qti', 'oneroster', 'caliper', 'case', 'nweamap', 'ed_fi', 'people_and_orgs', 'curriculum', 'content', 'events', 'results', 'analytics')),
  constraint idempotency_surface_ck
    check (surface in ('platform', '1edtech', 'alpha')),
  constraint idempotency_method_ck
    check (method in ('POST', 'PUT', 'PATCH', 'DELETE')),
  constraint idempotency_route_template_ck
    check (route_template like '/%' and position('?' in route_template) = 0),
  constraint idempotency_operation_id_ck
    check (operation_id ~ '^[a-z0-9][a-z0-9._-]{0,119}$'),
  constraint idempotency_key_length_ck
    check (length(btrim(idempotency_key)) between 1 and 128),
  constraint idempotency_request_hash_ck
    check (request_hash ~ '^sha256:[0-9a-f]{64}$'),
  constraint idempotency_status_ck
    check (status in ('in_progress', 'completed', 'failed_permanent', 'failed_transient', 'commit_uncertain', 'expired')),
  constraint idempotency_response_status_ck
    check (response_status is null or response_status between 100 and 599),
  constraint idempotency_response_body_shape_ck
    check (response_body is null or jsonb_typeof(response_body) in ('object', 'array')),
  constraint idempotency_response_headers_shape_ck
    check (jsonb_typeof(response_headers) = 'object'),
  constraint idempotency_expires_after_created_ck
    check (expires_at > created_at),
  constraint idempotency_updated_after_created_ck
    check (updated_at >= created_at),
  unique (tenant_id, module, surface, method, route_template, operation_id, idempotency_key)
);

create index idempotency_tenant_expires_idx on platform.idempotency_key(tenant_id, expires_at);
create index idempotency_status_lock_idx on platform.idempotency_key(status, locked_until);
create index idempotency_request_hash_idx on platform.idempotency_key(request_hash);
create unique index case_1edtech_idempotency_key_uidx
  on platform.idempotency_key(tenant_id, module, surface, idempotency_key)
  where module = 'case' and surface = '1edtech';

comment on table platform.idempotency_key is 'This table records the first request to claim an Idempotency-Key within a precise Platform-owned scope. The default is tenant/module/surface/operation; explicit route profiles such as CASE 1EdTech may enforce a stricter tenant/module/surface/key scope. Later matching requests replay the original safe result, while key reuse with a different canonical request returns 409 before side effects.';
comment on column platform.idempotency_key.idempotency_key_id is 'Stable identifier for the retry ledger row. Audit rows refer to this value rather than repeating replay internals.';
comment on column platform.idempotency_key.tenant_id is 'Tenant that owns the retry scope and the mutation being protected.';
comment on column platform.idempotency_key.module is 'Module namespace whose operation claimed the idempotency key.';
comment on column platform.idempotency_key.surface is 'Surface whose API contract produced the retryable operation.';
comment on column platform.idempotency_key.method is 'HTTP method for the mutation protected by the key.';
comment on column platform.idempotency_key.route_template is 'Stable route pattern from the customer website or OpenAPI operation, with variable path segments expressed as braces.';
comment on column platform.idempotency_key.operation_id is 'Stable operation identifier used by docs, OpenAPI, logs, audit, and idempotency middleware.';
comment on column platform.idempotency_key.idempotency_key is 'Opaque customer-supplied Idempotency-Key header value for one retryable operation.';
comment on column platform.idempotency_key.request_hash is 'Digest of the canonical replay identity for the first request. It lets middleware distinguish a safe retry from key reuse with different content.';
comment on column platform.idempotency_key.canonical_request_hash is 'Immutable canonical request identity retained when claim_protocol needs request_hash as a rolling-deployment or active-owner fence.';
comment on column platform.idempotency_key.claim_protocol is 'Versioned ownership protocol that defines which token, stale-state, finalization, and rolling-deployment rules govern the row.';
comment on column platform.idempotency_key.status is 'Replay lifecycle state of the idempotency row.';
comment on column platform.idempotency_key.response_status is 'HTTP status originally returned for a final replayable outcome.';
comment on column platform.idempotency_key.response_body is 'Redacted JSON body safe to replay to the same tenant for completed or failed_permanent outcomes.';
comment on column platform.idempotency_key.response_headers is 'Redacted terminal response headers needed to replay the original HTTP contract, including ETag and documented retry metadata.';
comment on column platform.idempotency_key.resource_type is 'Optional type of resource created, imported, deleted, or accepted by the operation.';
comment on column platform.idempotency_key.resource_id is 'Optional identifier of the primary resource associated with the replayable outcome.';
comment on column platform.idempotency_key.first_request_id is 'Request identifier of the first request that claimed this key.';
comment on column platform.idempotency_key.lease_token is 'Server-generated claimant fence used by versioned protocols to prove which request may mutate or finalize the ledger row.';
comment on column platform.idempotency_key.locked_until is 'Temporary lock deadline used while an in-progress operation is executing.';
comment on column platform.idempotency_key.expires_at is 'Timestamp after which the key is no longer promised to replay the original response.';
comment on column platform.idempotency_key.created_at is 'Timestamp when the first request claimed the idempotency key.';
comment on column platform.idempotency_key.updated_at is 'Timestamp when the row last changed status, replay body, lock, or expiry.';

{
  "audit_log_id": "a597b7de-b1dd-4c9e-93a3-9623cb90bc34",
  "tenant_id": "0d4ce2f4-1c42-4f3c-9f0d-03fb7f5271d3",
  "module": "qti",
  "surface": "1edtech",
  "operation_id": "qti.packages.import",
  "actor_subject": "userhash:1bd7b5bd0d77",
  "actor_roles": [
    "teacher",
    "content-admin"
  ],
  "resource_type": "qti.content_package",
  "resource_id": "7f2e4dd2-c147-49ea-af77-41a6fdd70980",
  "action": "import",
  "outcome": "succeeded",
  "http_status": 201,
  "request_id": "req_20260521_0001",
  "trace_id": "trace_20260521_a1f4",
  "idempotency_key_id": "b81db6f4-c7ea-4757-b5aa-87570f7ad119",
  "occurred_at": "2026-05-21T12:01:08Z",
  "redacted_metadata": {
    "packageHash": "sha256:6a8c1f58c16f4b0b4f0a0d8c6be8d226e3d5d80f0b2f7d2f49e6d7d9e9d4f1cb",
    "resourceCount": 18,
    "fileCount": 42
  }
}

insert into platform.audit_log (tenant_id, module, surface, operation_id, actor_subject, actor_roles, resource_type, resource_id, action, outcome, http_status, request_id, trace_id, idempotency_key_id, redacted_metadata) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15);

select occurred_at, actor_subject, action, outcome, http_status from platform.audit_log where tenant_id = $1 and resource_type = $2 and resource_id = $3 order by occurred_at desc;

select module, surface, operation_id, outcome, count(*) from platform.audit_log where tenant_id = $1 and occurred_at >= now() - interval '7 days' group by module, surface, operation_id, outcome order by count(*) desc;

create table platform.audit_log (
  audit_log_id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references platform.tenant(tenant_id),
  module text not null,
  surface text not null,
  operation_id text not null,
  actor_subject text not null,
  actor_roles text[] not null default '{}'::text[],
  resource_type text not null,
  resource_id text not null,
  action text not null,
  outcome text not null,
  http_status integer not null,
  request_id text not null,
  trace_id text not null,
  idempotency_key_id uuid references platform.idempotency_key(idempotency_key_id) on delete set null,
  occurred_at timestamptz not null default now(),
  redacted_metadata jsonb not null default '{}'::jsonb,
  constraint audit_module_ck
    check (module in ('platform', 'incept', 'qti', 'oneroster', 'caliper', 'case', 'nweamap', 'ed_fi', 'people_and_orgs', 'curriculum', 'content', 'events', 'results', 'analytics')),
  constraint audit_surface_ck
    check (surface in ('platform', '1edtech', 'alpha')),
  constraint audit_action_ck
    check (action in ('create', 'update', 'delete', 'import', 'export', 'read_privileged', 'runtime_delete', 'conformance_change', 'trust_change', 'authz_denied', 'maintenance')),
  constraint audit_outcome_ck
    check (outcome in ('accepted', 'succeeded', 'failed_validation', 'failed_authorization', 'failed_conflict', 'failed_not_found', 'failed_server')),
  constraint audit_http_status_ck
    check (http_status between 100 and 599),
  constraint audit_metadata_object_ck
    check (jsonb_typeof(redacted_metadata) = 'object'),
  constraint audit_required_text_ck
    check (
      length(btrim(operation_id)) > 0 and
      length(btrim(actor_subject)) > 0 and
      length(btrim(resource_type)) > 0 and
      length(btrim(resource_id)) > 0 and
      length(btrim(request_id)) > 0 and
      length(btrim(trace_id)) > 0
    )
);

create index audit_tenant_time_idx on platform.audit_log(tenant_id, occurred_at desc);
create index audit_resource_idx on platform.audit_log(tenant_id, resource_type, resource_id, occurred_at desc);
create index audit_request_idx on platform.audit_log(request_id);
create index audit_trace_idx on platform.audit_log(trace_id);
create index audit_operation_idx on platform.audit_log(module, surface, operation_id, occurred_at desc);
create index audit_idempotency_idx on platform.audit_log(idempotency_key_id);

comment on table platform.audit_log is 'This table gives support, compliance, release, and future AI agents one durable account of who did what, under which tenant and module, with what result. Module tables remain the source of record for domain state; audit rows are the redacted narrative that explains sensitive platform actions across modules.';
comment on column platform.audit_log.audit_log_id is 'Stable identifier for one append-only audit event.';
comment on column platform.audit_log.tenant_id is 'Tenant affected by the audited action.';
comment on column platform.audit_log.module is 'Module responsible for the operation being audited.';
comment on column platform.audit_log.surface is 'Surface through which the action was initiated or exposed.';
comment on column platform.audit_log.operation_id is 'Stable operation identifier from the customer website, OpenAPI, or platform maintenance command.';
comment on column platform.audit_log.actor_subject is 'Pseudonymous actor identifier for the user, service, or release process that attempted the action.';
comment on column platform.audit_log.actor_roles is 'Roles or scopes that justified the action or explain why authorization failed.';
comment on column platform.audit_log.resource_type is 'Stable resource class affected by the action.';
comment on column platform.audit_log.resource_id is 'Identifier of the primary resource affected by the action, or a documented sentinel when authorization failed before resource resolution.';
comment on column platform.audit_log.action is 'Behavioral category of the audited action.';
comment on column platform.audit_log.outcome is 'Final result category for the action.';
comment on column platform.audit_log.http_status is 'HTTP status returned for the request or the HTTP-equivalent status assigned to a background/service operation.';
comment on column platform.audit_log.request_id is 'Per-request identifier exposed in Problem responses and support logs.';
comment on column platform.audit_log.trace_id is 'Trace identifier that links logs, metrics, audit rows, and downstream spans for one request or job.';
comment on column platform.audit_log.idempotency_key_id is 'Optional link to the idempotency row that governed the audited operation.';
comment on column platform.audit_log.occurred_at is 'Timestamp when the audited action reached the recorded outcome.';
comment on column platform.audit_log.redacted_metadata is 'Small, safe, structured context that helps explain the audit event without storing raw sensitive data.';

{
  "identity_binding_id": "41877f4a-d9ce-4b3c-82a4-9cf437212765",
  "tenant_id": "0d4ce2f4-1c42-4f3c-9f0d-03fb7f5271d3",
  "issuer": "https://cognito-idp.us-east-1.amazonaws.com/us-east-1_example",
  "subject": "0a1b2c3d-opaque-cognito-subject",
  "person_id": "person_student_001",
  "created_by_subject": "operator:7d29b03c917f1a8ef83c6efb7763ca12",
  "created_at": "2026-08-04T14:00:00Z",
  "revoked_at": null,
  "revoked_by_subject": null,
  "revocation_reason": null
}

select identity_binding_id, person_id from platform.end_user_identity_binding where tenant_id = $1 and issuer = $2 and subject = $3 and revoked_at is null;

update platform.end_user_identity_binding set revoked_at = now(), revoked_by_subject = $3, revocation_reason = $4 where tenant_id = $1 and identity_binding_id = $2 and revoked_at is null returning identity_binding_id;

create table platform.end_user_identity_binding (
  identity_binding_id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references platform.tenant(tenant_id),
  issuer text not null,
  subject text not null,
  person_id text not null,
  created_by_subject text not null,
  created_at timestamptz not null default now(),
  revoked_at timestamptz,
  revoked_by_subject text,
  revocation_reason text,
  constraint end_user_identity_issuer_ck check (issuer ~ '^https://[^[:space:]]{1,480}$'),
  constraint end_user_identity_subject_ck check (length(subject) between 1 and 255),
  constraint end_user_identity_person_ck check (length(person_id) between 1 and 255),
  constraint end_user_identity_revocation_ck check (
    (revoked_at is null and revoked_by_subject is null and revocation_reason is null)
    or (revoked_at is not null and revoked_by_subject is not null and revocation_reason is not null)
  ),
  unique (tenant_id, identity_binding_id)
);
create unique index end_user_identity_active_subject_uidx on platform.end_user_identity_binding(tenant_id, issuer, subject) where revoked_at is null;
create unique index end_user_identity_active_person_uidx on platform.end_user_identity_binding(tenant_id, issuer, person_id) where revoked_at is null;

comment on table platform.end_user_identity_binding is 'The TimeBack bridge needs one stable, revocable answer to which current roster person an authenticated Cognito subject represents. It never binds by email and never stores a password or Cognito token.';
comment on column platform.end_user_identity_binding.identity_binding_id is 'Stable id for one historical binding row.';
comment on column platform.end_user_identity_binding.tenant_id is 'Canonical active Platform tenant UUID carried by the binding and local sessions.';
comment on column platform.end_user_identity_binding.issuer is 'Exact configured HTTPS Cognito issuer.';
comment on column platform.end_user_identity_binding.subject is 'Exact opaque Cognito subject returned by the operator invite and signed ID token.';
comment on column platform.end_user_identity_binding.person_id is 'Canonical People & Orgs person represented by the bound subject.';
comment on column platform.end_user_identity_binding.created_by_subject is 'Pseudonymous hash label for the operator who authorized the binding.';
comment on column platform.end_user_identity_binding.created_at is 'Time the binding history row was created.';
comment on column platform.end_user_identity_binding.revoked_at is 'Time the binding stopped authorizing new sessions/tokens.';
comment on column platform.end_user_identity_binding.revoked_by_subject is 'Pseudonymous hash label for the revoking operator.';
comment on column platform.end_user_identity_binding.revocation_reason is 'Bounded operational reason for revocation.';

{
  "end_user_session_id": "750b24b4-10b3-4625-94f7-5a91076418df",
  "tenant_id": "0d4ce2f4-1c42-4f3c-9f0d-03fb7f5271d3",
  "identity_binding_id": "41877f4a-d9ce-4b3c-82a4-9cf437212765",
  "handle_hash": "sha256:16ff5a9f6f981798c985bb7d32438e6ae2df1cd90c4c08f704d7839c57425913",
  "authenticated_at": "2026-08-04T14:00:00Z",
  "last_seen_at": "2026-08-04T14:05:00Z",
  "idle_expires_at": "2026-08-04T14:35:00Z",
  "absolute_expires_at": "2026-08-04T16:00:00Z",
  "revoked_at": null,
  "revocation_reason": null
}

select * from platform.end_user_session where tenant_id = $1 and handle_hash = $2;

update platform.end_user_session set revoked_at = now(), revocation_reason = 'identity_binding_revoked' where tenant_id = $1 and identity_binding_id = $2 and revoked_at is null;

create table platform.end_user_session (
  end_user_session_id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references platform.tenant(tenant_id),
  identity_binding_id uuid not null,
  handle_hash text not null unique,
  authenticated_at timestamptz not null,
  last_seen_at timestamptz not null,
  idle_expires_at timestamptz not null,
  absolute_expires_at timestamptz not null,
  revoked_at timestamptz,
  revocation_reason text,
  foreign key (tenant_id, identity_binding_id) references platform.end_user_identity_binding(tenant_id, identity_binding_id),
  constraint end_user_session_handle_hash_ck check (handle_hash ~ '^sha256:[0-9a-f]{64}$'),
  constraint end_user_session_lifetime_ck check (authenticated_at <= last_seen_at and last_seen_at < idle_expires_at and idle_expires_at <= absolute_expires_at),
  constraint end_user_session_revocation_ck check ((revoked_at is null and revocation_reason is null) or (revoked_at is not null and revocation_reason is not null))
);
create index end_user_session_binding_idx on platform.end_user_session(tenant_id, identity_binding_id, absolute_expires_at desc);
create index end_user_session_expiry_idx on platform.end_user_session(absolute_expires_at) where revoked_at is null;

comment on table platform.end_user_session is 'The TimeBack bridge keeps browser continuity without persisting Cognito tokens or allowing a cookie to authorize customer APIs. The browser exchanges this session for short-lived Bearer JWTs whose People & Orgs claims are re-derived each time.';
comment on column platform.end_user_session.end_user_session_id is 'Stable id for one local browser session.';
comment on column platform.end_user_session.tenant_id is 'Canonical Platform tenant UUID that scopes the local session and binding.';
comment on column platform.end_user_session.identity_binding_id is 'Active subject/person binding that authenticated this session.';
comment on column platform.end_user_session.handle_hash is 'SHA-256 of the random 256-bit HttpOnly cookie handle.';
comment on column platform.end_user_session.authenticated_at is 'Time Cognito callback validation created the session.';
comment on column platform.end_user_session.last_seen_at is 'Last successful session-token mint time.';
comment on column platform.end_user_session.idle_expires_at is 'Sliding inactivity deadline, normally 30 minutes after last_seen_at.';
comment on column platform.end_user_session.absolute_expires_at is 'Non-extendable deadline, exactly 2 hours after authentication for this contract.';
comment on column platform.end_user_session.revoked_at is 'Time logout, expiry, invalid state, or binding revocation closed the session.';
comment on column platform.end_user_session.revocation_reason is 'Safe operational reason the session stopped authorizing token mint.';

{
  "grant_id": "8c9a4b6e-2f31-4a7d-9b0e-5d1c3e8f7a20",
  "tenant_id": "4fb95b74-06dc-4a8b-8f50-14ce56c93970",
  "producer_key": "ap-one",
  "bound_subject": "ap-one-producer",
  "allowed_scopes": [
    "author:content",
    "publish:content"
  ],
  "allowed_roles": [
    "service"
  ],
  "max_ttl_seconds": 900,
  "max_issuances_per_hour": 30,
  "created_by_subject": "operator-owner",
  "created_at": "2026-08-10T12:00:00Z",
  "revoked_at": null,
  "revoked_by_subject": null,
  "revocation_reason": null
}

select grant_id, allowed_scopes, allowed_roles, max_ttl_seconds, max_issuances_per_hour from platform.producer_credential_grant where tenant_id = $1 and bound_subject = $2 and revoked_at is null;

update platform.producer_credential_grant set revoked_at = now(), revoked_by_subject = $3, revocation_reason = $4 where tenant_id = $1 and grant_id = $2 and revoked_at is null returning grant_id;

create table platform.producer_credential_grant (
  grant_id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references platform.tenant(tenant_id),
  producer_key text not null,
  bound_subject text not null,
  allowed_scopes text[] not null,
  allowed_roles text[] not null default '{service}'::text[],
  max_ttl_seconds integer not null default 900,
  max_issuances_per_hour integer not null default 30,
  created_by_subject text not null,
  created_at timestamptz not null default now(),
  revoked_at timestamptz,
  revoked_by_subject text,
  revocation_reason text,
  constraint producer_grant_producer_key_ck check (producer_key ~ '^[a-z0-9]([a-z0-9-]{1,62}[a-z0-9])$'),
  constraint producer_grant_bound_subject_ck check (bound_subject ~ '^[A-Za-z0-9._:@/-]{1,160}$'),
  constraint producer_grant_scopes_enumerated_ck check (allowed_scopes <@ array['author:content', 'publish:content', 'read:content']::text[] and array_length(allowed_scopes, 1) between 1 and 20),
  constraint producer_grant_roles_enumerated_ck check (allowed_roles <@ array['service']::text[] and array_length(allowed_roles, 1) = 1),
  constraint producer_grant_ttl_ck check (max_ttl_seconds between 60 and 3600),
  constraint producer_grant_rate_ck check (max_issuances_per_hour between 1 and 1000),
  constraint producer_grant_revocation_ck check (
    (revoked_at is null and revoked_by_subject is null and revocation_reason is null)
    or (revoked_at is not null and revoked_by_subject is not null and revocation_reason is not null)
  )
);
create unique index producer_grant_active_producer_uidx on platform.producer_credential_grant(tenant_id, producer_key) where revoked_at is null;
create unique index producer_grant_active_subject_uidx on platform.producer_credential_grant(tenant_id, bound_subject) where revoked_at is null;

comment on table platform.producer_credential_grant is 'Producers (Incept/AP One-class content generators) must obtain scoped credentials such as publish:content through an authenticated API, never a ticket. The grant is the one-time operator onboarding act; everything after it — minting, rotation, self-revocation — is producer self-service bounded by this envelope.';
comment on column platform.producer_credential_grant.grant_id is 'Stable id for one historical grant envelope row.';
comment on column platform.producer_credential_grant.tenant_id is 'Canonical Platform tenant the envelope is bound to.';
comment on column platform.producer_credential_grant.producer_key is 'Human-stable lowercase producer identity (for example ap-one).';
comment on column platform.producer_credential_grant.bound_subject is 'Exact principal subject whose same-tenant Bearer requests may mint under this envelope.';
comment on column platform.producer_credential_grant.allowed_scopes is 'Exhaustive grantable scope list for this producer; requested scopes must be a subset.';
comment on column platform.producer_credential_grant.allowed_roles is 'Role class issued credentials may carry.';
comment on column platform.producer_credential_grant.max_ttl_seconds is 'Upper bound for issued-credential lifetime; also the revocation-convergence horizon for offline verifiers.';
comment on column platform.producer_credential_grant.max_issuances_per_hour is 'Bounded issuance rate enforced at mint time by counting ledger rows in the trailing hour.';
comment on column platform.producer_credential_grant.created_by_subject is 'Subject of the operator principal that created the envelope at onboarding.';
comment on column platform.producer_credential_grant.created_at is 'Time the envelope was created.';
comment on column platform.producer_credential_grant.revoked_at is 'Time the envelope stopped authorizing minting; cascades revocation to outstanding credentials.';
comment on column platform.producer_credential_grant.revoked_by_subject is 'Subject of the operator that revoked the envelope.';
comment on column platform.producer_credential_grant.revocation_reason is 'Bounded operational reason for revocation.';

{
  "credential_id": "3f2b8c1d-7e4a-4b9f-8d2c-6a5e9f0b1c3d",
  "grant_id": "8c9a4b6e-2f31-4a7d-9b0e-5d1c3e8f7a20",
  "tenant_id": "4fb95b74-06dc-4a8b-8f50-14ce56c93970",
  "subject": "ap-one-producer",
  "roles": [
    "service"
  ],
  "scopes": [
    "publish:content"
  ],
  "credential_sha256": "sha256:ba216f040c3d109731eb370f6252a989d05e65a3344f88b7e89181ee2a80ba70",
  "issued_by_subject": "ap-one-producer",
  "issued_at": "2026-08-10T12:05:00Z",
  "expires_at": "2026-08-10T12:20:00Z",
  "revoked_at": null,
  "revoked_by_subject": null,
  "revocation_reason": null
}

select count(*) from platform.producer_credential where grant_id = $1 and issued_at >= now() - interval '1 hour';

update platform.producer_credential set revoked_at = now(), revoked_by_subject = $3, revocation_reason = $4 where tenant_id = $1 and credential_id = $2 returning credential_id;

create table platform.producer_credential (
  credential_id uuid primary key default gen_random_uuid(),
  grant_id uuid not null references platform.producer_credential_grant(grant_id),
  tenant_id uuid not null references platform.tenant(tenant_id),
  subject text not null,
  roles text[] not null,
  scopes text[] not null,
  credential_sha256 text not null unique,
  issued_by_subject text not null,
  issued_at timestamptz not null default now(),
  expires_at timestamptz not null,
  revoked_at timestamptz,
  revoked_by_subject text,
  revocation_reason text,
  constraint producer_credential_subject_ck check (subject ~ '^[A-Za-z0-9._:@/-]{1,160}$'),
  constraint producer_credential_hash_ck check (credential_sha256 ~ '^sha256:[0-9a-f]{64}$'),
  constraint producer_credential_scopes_ck check (array_length(scopes, 1) between 1 and 20),
  constraint producer_credential_roles_ck check (array_length(roles, 1) between 1 and 5),
  constraint producer_credential_expiry_ck check (expires_at > issued_at),
  constraint producer_credential_revocation_ck check (
    (revoked_at is null and revoked_by_subject is null and revocation_reason is null)
    or (revoked_at is not null and revoked_by_subject is not null and revocation_reason is not null)
  )
);
create index producer_credential_grant_issued_idx on platform.producer_credential(grant_id, issued_at desc);
create index producer_credential_tenant_issued_idx on platform.producer_credential(tenant_id, issued_at desc);

comment on table platform.producer_credential is 'Every self-service issuance must be auditable and individually revocable. The ledger row is the durable record the mint route checks before rotation, the revocation switch for one credential, and the join target for issuance/revocation audit rows — while the raw token exists only in the mint response.';
comment on column platform.producer_credential.credential_id is 'Stable id for one issued credential; also embedded in the JWT as the credential_id claim so rotation and revocation can find this row.';
comment on column platform.producer_credential.grant_id is 'Grant envelope the credential was minted under.';
comment on column platform.producer_credential.tenant_id is 'Canonical Platform tenant the credential is bound to.';
comment on column platform.producer_credential.subject is 'Principal subject carried by the issued JWT; always the grant bound_subject.';
comment on column platform.producer_credential.roles is 'Frozen role list minted into the JWT.';
comment on column platform.producer_credential.scopes is 'Frozen scope list minted into the JWT.';
comment on column platform.producer_credential.credential_sha256 is 'SHA-256 fingerprint of the issued JWT; the only stored representation of the secret.';
comment on column platform.producer_credential.issued_by_subject is 'Subject of the principal that requested the mint (the bootstrap principal or the rotating credential''s subject).';
comment on column platform.producer_credential.issued_at is 'Mint time; the rate-limit window counts rows by this column.';
comment on column platform.producer_credential.expires_at is 'JWT exp; the credential is useless everywhere after this time.';
comment on column platform.producer_credential.revoked_at is 'Time the credential stopped being accepted by platform producer-credential routes.';
comment on column platform.producer_credential.revoked_by_subject is 'Subject that revoked the credential: its own producer principal (self-revoke on suspected leak), an operator, or the grant revocation cascade.';
comment on column platform.producer_credential.revocation_reason is 'Bounded operational reason; the grant cascade writes grant_revoked.';