CASE 1EdTech Data Dictionary

CASE 1.1 CFDocument fields are pass-through in body JSONB; platform3 adds tenant, id, resource_type, import, ETag, timestamps, and retirement sidecars. · Mixed pass-through plus gap fill

case.cf_document

Stores one standards-framework container, such as Common Core State Standards Math 2010. It is the root object a browser lists before loading CFItems.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Created by POST /CFDocuments or CFPackage import, updated with If-Match, soft-retired on DELETE unless active CFItems/CFAssociations/Alpha references block retirement with case:resource_referenced, and exported inside CFPackage without platform sidecar fields.
Primary key: (tenant_id, id)

CASE 1.1 item fields are pass-through in body JSONB. cfDocumentIdentifier and parentIdentifier are validated body fields, not flat physical columns. · Mixed pass-through plus gap fill

case.cf_item

Stores each standard, competency, skill, or grouping node inside a CFDocument tree.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Created by POST /CFItems or package import, placed in the tree through one active isChildOf CFAssociation, edited with If-Match, and soft-retired on DELETE unless active child CFItems/CFAssociations/Alignments/Alpha references block retirement with case:resource_referenced.
Primary key: (tenant_id, id)

CASE 1.1 association fields are pass-through in body JSONB, with TimeBack validation that makes tree and extension behavior deterministic. · Mixed pass-through plus gap fill

case.cf_association

Stores typed graph edges between CFItems or CFDocuments, including the isChildOf edges that create the navigable tree.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Created by POST /CFAssociations, CFItem create placement, or package import; edited with If-Match; soft-retired on DELETE.
Primary key: (tenant_id, id)

CASE defines the CFPackage envelope. This table is TimeBack audit/idempotency evidence and is never exported inside a CASE package. · TimeBack gap fill

case.cf_package_import

Records each transactional CFPackage import, its canonical package hash, row counts, and validation result.

Physical storage: Flat platform audit/control row. Columns shown here are physical columns.
Lifecycle: Created at import start, committed as succeeded only when all document, item, association, definition, and rubric rows commit atomically, or failed with rowErrors and no partial writes.
Primary key: (tenant_id, id)

Alignment is platform3-specific and intentionally separate from CFAssociation. · TimeBack gap fill

case.alignment

Links an integrator's external system identifier, such as a curriculum lesson ID, to a CASE CFItem without contaminating the canonical CASE graph.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Created by POST /Alignments or direct app writes, edited with If-Match, deleted as a hard delete in the public API, and never exported in CFPackage. The physical table has retired_at for retained tombstones and active uniqueness.
Primary key: (tenant_id, id)

Imported from CFDefinitions or CFRubrics and served through official detail reads. First-class list/create/update/delete authoring is deferred. · 1EdTech pass-through

case.cf_concept

Stores concept definitions imported through CFDefinitions and linked from CFItem concept keyword URI fields.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Imported with CFPackage, read through GET /CFConcepts/{sourcedId}, and updated only by future package imports.
Primary key: (tenant_id, id)

Imported from CFDefinitions or CFRubrics and served through official detail reads. First-class list/create/update/delete authoring is deferred. · 1EdTech pass-through

case.cf_subject

Stores subject definitions imported through CFDefinitions and linked from document/item subjectURI fields.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Imported with CFPackage, read through GET /CFSubjects/{sourcedId}, and updated only by future package imports.
Primary key: (tenant_id, id)

Imported from CFDefinitions or CFRubrics and served through official detail reads. First-class list/create/update/delete authoring is deferred. · 1EdTech pass-through

case.cf_license

Stores license records imported through CFDefinitions and linked from CFDocument/CFItem licenseURI fields.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Imported with CFPackage, read through GET /CFLicenses/{sourcedId}, and updated only by future package imports.
Primary key: (tenant_id, id)

Imported from CFDefinitions or CFRubrics and served through official detail reads. First-class list/create/update/delete authoring is deferred. · 1EdTech pass-through

case.cf_item_type

Stores publisher-defined item type definitions, such as cluster, domain, standard, competency, or benchmark.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Imported with CFPackage, read through GET /CFItemTypes/{sourcedId}, and updated only by future package imports.
Primary key: (tenant_id, id)

Imported from CFDefinitions or CFRubrics and served through official detail reads. First-class list/create/update/delete authoring is deferred. · 1EdTech pass-through

case.cf_association_grouping

Stores publisher grouping definitions for sets of associations.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Imported with CFPackage, read through GET /CFAssociationGroupings/{sourcedId}, and updated only by future package imports.
Primary key: (tenant_id, id)

Imported from CFDefinitions or CFRubrics and served through official detail reads. First-class list/create/update/delete authoring is deferred. · 1EdTech pass-through

case.cf_rubric

Stores rubric definitions imported with CFPackage, including criteria and levels that complete the rubric.

Physical storage: CASE resource row: tenant_id/id/resource_type/import_batch_id/body/version/etag/timestamps/retired_at are physical columns; CASE payload fields are stored in body JSONB.
Lifecycle: Imported with CFPackage, read through GET /CFRubrics/{sourcedId}, and updated only by future package imports.
Primary key: (tenant_id, id)

CASE 1.1 rubric criterion fields are pass-through; rubric_identifier is the relational parent key. · 1EdTech pass-through

case.cf_rubric_criterion

Stores one criterion row inside an imported CFRubric.

Physical storage: Rubric child parent keys are physical columns; CASE criterion/level payload fields remain in body JSONB.
Lifecycle: Imported with its parent CFRubric, read as part of CFRubric detail, and not directly authored in the first release.
Primary key: (tenant_id, identifier)

CASE 1.1 level fields are pass-through; rubric_criterion_identifier is the relational parent key. · 1EdTech pass-through

case.cf_rubric_criterion_level

Stores one achievement level inside an imported rubric criterion.

Physical storage: Rubric child parent keys are physical columns; CASE criterion/level payload fields remain in body JSONB.
Lifecycle: Imported with the parent criterion, read as part of CFRubric detail, and not directly authored in the first release.
Primary key: (tenant_id, identifier)

case_association_type

CFAssociation associationType · field case.cf_association.association_type

case_target_type

LinkGenURI targetType · field case.cf_association.origin_target_type / destination_target_type

case_version

CASE document version · field case.cf_document.case_version

case_import_status

CFPackage import status · field case.cf_package_import.status

case_problem_code

Typed Problem code · field Problem.code

case_scope

CASE JWT scopes · field JWT.scopes[]

case_framework_type

CFDocument frameworkType · field case.cf_document.framework_type

Tenant RLS contract

Direct Postgres roles should enforce the same text tenant_id boundary the API derives from the HS256 bearer JWT.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

create schema if not exists "case";

create or replace function "case".current_tenant_id()
returns text
language sql
stable
as $$
  select nullif(current_setting('app.tenant_id', true), '')::text
$$;

-- Run this block after the CREATE TABLE statements if a direct DB role will read/write CASE rows.
do $$
declare
  table_name text;
  policy_name text;
begin
  foreach table_name in array array[
    'cf_document',
    'cf_item',
    'cf_association',
    'alignment',
    'cf_package_import',
    'cf_concept',
    'cf_subject',
    'cf_license',
    'cf_item_type',
    'cf_association_grouping',
    'cf_rubric',
    'cf_rubric_criterion',
    'cf_rubric_criterion_level'
  ]
  loop
    policy_name := table_name || '_tenant_isolation';
    execute format('alter table "case".%I enable row level security', table_name);
    execute format('drop policy if exists %I on "case".%I', policy_name, table_name);
    execute format(
      'create policy %I on "case".%I using (tenant_id = "case".current_tenant_id()) with check (tenant_id = "case".current_tenant_id())',
      policy_name,
      table_name
    );
  end loop;
end $$;

CREATE TABLE case.cf_document

Live physical storage for CFDocument. CASE fields live in body JSONB; tenant/id/resource_type/import/version/etag/timestamps/retired_at are sidecar columns.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

create table if not exists "case".cf_document (
  tenant_id text not null,
  id text not null,
  resource_type text not null default 'cf_document',
  import_batch_id text not null,
  body jsonb not null,
  version integer not null default 1,
  etag text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  retired_at timestamptz,
  primary key (tenant_id, id)
);

create index if not exists cf_document_uri_idx
  on "case".cf_document (tenant_id, (body->>'uri'))
  where retired_at is null;
create index if not exists cf_document_updated_idx
  on "case".cf_document (tenant_id, updated_at desc)
  where retired_at is null;

-- Required CASE fields are validated by the API before insert:
-- body->>'identifier', body->>'uri', body->>'creator', body->>'title', body->>'lastChangeDateTime'.

CREATE TABLE case.cf_item

Live physical storage for CFItem. The required CFDocumentURI and parentIdentifier write contract is in body JSONB, not flat columns.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

create table if not exists "case".cf_item (
  tenant_id text not null,
  id text not null,
  resource_type text not null default 'cf_item',
  import_batch_id text not null,
  body jsonb not null,
  version integer not null default 1,
  etag text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  retired_at timestamptz,
  primary key (tenant_id, id)
);

create index if not exists cf_item_document_idx
  on "case".cf_item (tenant_id, (body->>'CFDocumentURI'), (body->>'parentIdentifier'))
  where retired_at is null;
create index if not exists cf_item_document_identifier_idx
  on "case".cf_item (tenant_id, (body->>'cfDocumentIdentifier'))
  where retired_at is null;
create index if not exists cf_item_uri_idx
  on "case".cf_item (tenant_id, (body->>'uri'))
  where retired_at is null;
create index if not exists cf_item_updated_idx
  on "case".cf_item (tenant_id, updated_at desc)
  where retired_at is null;

-- Required CFItem write fields are validated by the API before insert:
-- body->>'identifier', body->>'uri', body->>'fullStatement',
-- body->'CFDocumentURI' with identifier/uri, and body->>'parentIdentifier'.

CREATE TABLE case.cf_association

Live physical storage for typed CASE graph edges. Endpoint projections are JSONB paths in body.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

create table if not exists "case".cf_association (
  tenant_id text not null,
  id text not null,
  resource_type text not null default 'cf_association',
  import_batch_id text not null,
  body jsonb not null,
  version integer not null default 1,
  etag text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  retired_at timestamptz,
  primary key (tenant_id, id)
);

create index if not exists cf_association_origin_idx
  on "case".cf_association (tenant_id, (body->'originNodeURI'->>'identifier'))
  where retired_at is null;
create index if not exists cf_association_destination_idx
  on "case".cf_association (tenant_id, (body->'destinationNodeURI'->>'identifier'))
  where retired_at is null;
create index if not exists cf_association_origin_identifier_idx
  on "case".cf_association (tenant_id, (body->>'originIdentifier'))
  where retired_at is null;
create index if not exists cf_association_destination_identifier_idx
  on "case".cf_association (tenant_id, (body->>'destinationIdentifier'))
  where retired_at is null;
create index if not exists cf_association_type_idx
  on "case".cf_association (tenant_id, (body->>'associationType'))
  where retired_at is null;

CREATE TABLE case.alignment

Live physical storage for external-ID mappings. There is no deleted_at column; active rows use retired_at is null and public DELETE hard-deletes.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

create table if not exists "case".alignment (
  tenant_id text not null,
  id text not null,
  resource_type text not null default 'alignment',
  import_batch_id text not null,
  body jsonb not null,
  version integer not null default 1,
  etag text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  retired_at timestamptz,
  primary key (tenant_id, id)
);

create unique index if not exists alignment_active_external_id_idx
  on "case".alignment (tenant_id, (body->>'externalSystem'), (body->>'externalId'))
  where retired_at is null;

-- body fields validated by the API: alignmentId, externalSystem, externalId,
-- and at least one resolvable CFItem target in cfItemIdentifier or cfItemUri.

Retire reference guard trigger

Live guardrail for CFDocument/CFItem soft-retire. The API surfaces this as HTTP 409 case:resource_referenced; raw DB migrations must install the same trigger so direct retired_at updates cannot strand active references.

superseded-by-issues-707-and-1721 — Do not execute this reconstructed trigger excerpt. The tracked CASE DDL and the post-#707 release operator own the exact retirement guard.

-- The exact superseded trigger bytes remain reviewable in loop/case/artifacts/1edtech/data_dictionary/source/reconstruction/deployed-case-data-dictionary-20260723.json (sqlSections#retire-reference-trigger).
-- Do not execute reconstructed snapshot SQL. Use loop/case/artifacts/1edtech/implementation/impl/migrations/001_case_core.sql, loop/case/artifacts/1edtech/implementation/impl/src/migrations/caseSchema.mjs, and the reviewed post-#707 release operator.

CREATE TABLE case.cf_package_import

Live physical storage for CFPackage import evidence. This control table is flat because it is platform audit state, not a CASE resource body.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

create table if not exists "case".cf_package_import (
  tenant_id text not null,
  id text not null,
  import_batch_id text not null,
  document_identifier text not null,
  package_hash text not null,
  source_uri text,
  status text not null check (status in ('pending', 'succeeded', 'failed')),
  row_counts jsonb not null default '{}'::jsonb,
  row_errors jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  committed_at timestamptz,
  primary key (tenant_id, id)
);

create index if not exists cf_package_import_hash_idx
  on "case".cf_package_import (tenant_id, document_identifier, package_hash);

CREATE TABLE imported CFDefinitions resources

CFConcept, CFSubject, CFLicense, CFItemType, CFAssociationGrouping, and CFRubric use the same JSONB-body physical shape.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

create table if not exists "case".cf_concept (
  tenant_id text not null,
  id text not null,
  resource_type text not null default 'cf_concept',
  import_batch_id text,
  body jsonb not null,
  version integer not null default 1,
  etag text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  retired_at timestamptz,
  primary key (tenant_id, id)
);

-- Repeat this shape for cf_subject, cf_license, cf_item_type,
-- cf_association_grouping, and cf_rubric, changing only resource_type default
-- and table name. Reads are tenant-scoped; package export scopes definitions
-- by import_batch_id rather than by retired_at.

CREATE TABLE rubric child rows

Rubric criteria and criterion levels use parent-key columns plus body JSONB because CASE packages nest them under a CFRubric.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

create table if not exists "case".cf_rubric_criterion (
  tenant_id text not null,
  rubric_id text not null,
  id text not null,
  body jsonb not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (tenant_id, rubric_id, id)
);

create table if not exists "case".cf_rubric_criterion_level (
  tenant_id text not null,
  rubric_id text not null,
  criterion_id text not null,
  id text not null,
  body jsonb not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (tenant_id, rubric_id, criterion_id, id)
);

Tenant-scoped CFItem hierarchy query

Walk the active CFItem tree for one CFDocument using body->>'parentIdentifier' and body->>'cfDocumentIdentifier', without reading source code.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

with recursive item_tree as (
  select
    i.tenant_id,
    i.id as identifier,
    i.body->>'parentIdentifier' as parent_identifier,
    coalesce(i.body->>'cfDocumentIdentifier', i.body->'CFDocumentURI'->>'identifier') as cf_document_identifier,
    i.body->>'humanCodingScheme' as human_coding_scheme,
    i.body->>'fullStatement' as full_statement,
    i.body->>'listEnumeration' as list_enumeration,
    0 as depth,
    array[i.id] as path
  from "case".cf_item i
  where i.tenant_id = $1::text
    and coalesce(i.body->>'cfDocumentIdentifier', i.body->'CFDocumentURI'->>'identifier') = $2::text
    and i.body->>'parentIdentifier' = $2::text
    and i.retired_at is null

  union all

  select
    child.tenant_id,
    child.id,
    child.body->>'parentIdentifier',
    coalesce(child.body->>'cfDocumentIdentifier', child.body->'CFDocumentURI'->>'identifier'),
    child.body->>'humanCodingScheme',
    child.body->>'fullStatement',
    child.body->>'listEnumeration',
    item_tree.depth + 1,
    item_tree.path || child.id
  from "case".cf_item child
  join item_tree
    on item_tree.tenant_id = child.tenant_id
   and item_tree.identifier = child.body->>'parentIdentifier'
  where child.retired_at is null
    and child.id <> all(item_tree.path)
)
select depth, human_coding_scheme, identifier, parent_identifier, full_statement
from item_tree
order by path;

Resolve a publisher external ID to a CFItem

Use Alignment as the only tenant-specific external-ID link, then join to the active CASE item row.

reconstructed-deployed-snapshot-2026-06-11 — Use the tracked, hash-bound CASE DDL for execution; this excerpt is retained so reconstruction differences remain reviewable.

select
  a.body->>'externalSystem' as external_system,
  a.body->>'externalId' as external_id,
  i.id as cf_item_identifier,
  i.body->>'uri' as cf_item_uri,
  i.body->>'humanCodingScheme' as human_coding_scheme,
  i.body->>'fullStatement' as full_statement,
  coalesce(i.body->>'cfDocumentIdentifier', i.body->'CFDocumentURI'->>'identifier') as cf_document_identifier
from "case".alignment a
join "case".cf_item i
  on i.tenant_id = a.tenant_id
 and (
   i.id = a.body->>'cfItemIdentifier'
   or i.body->>'uri' = a.body->>'cfItemUri'
 )
where a.tenant_id = $1::text
  and a.body->>'externalSystem' = $2
  and a.body->>'externalId' = $3
  and a.retired_at is null
  and i.retired_at is null;