CREATE TABLE claim (
    id VARCHAR(64) NOT NULL --Hash of item_id|predicate|source|value,
    predicate VARCHAR(120) NOT NULL --Field name this claim asserts (e.g. medium, description),
    value CLOB DEFAULT NULL --Asserted value (scalar as text, or JSON for complex),
    source VARCHAR(40) NOT NULL --Who/what made this claim: ai,
    ocr,
    human,
    import,
    confidence DOUBLE PRECISION DEFAULT NULL --Confidence 0.0–1.0 (null = certain),
    agent VARCHAR(120) DEFAULT NULL --Model or agent that produced this claim,
    claimed_at DATETIME DEFAULT NULL --When this claim was made,
    meta CLOB DEFAULT NULL --Extra provenance metadata,
    item_id VARCHAR(260) NOT NULL --Composite: folioCode:coreCode:localId,
    run_id VARCHAR(26) DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FK_A769DE27126F525E FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE core (
    id VARCHAR(180) NOT NULL --Composite: folioCode:coreCode,
    code VARCHAR(80) NOT NULL --Core code within the folio (e.g. obj, per),
    label VARCHAR(255) DEFAULT NULL --Human-readable display name,
    row_count INTEGER DEFAULT 0 NOT NULL --Number of rows in this core,
    field_summary CLOB DEFAULT NULL --Populated field names from source SQLite sidecar,
    folio_code VARCHAR(120) NOT NULL --Unique folio identifier,
    matches dataset key,
    PRIMARY KEY (id),
    CONSTRAINT FK_6B8D854F3073E4E7 FOREIGN KEY (folio_code) REFERENCES folio (code) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE docs (
    id VARCHAR(120) NOT NULL --Document slug (e.g. overview, schema, query-guide),
    title VARCHAR(255) NOT NULL --Document title,
    type VARCHAR(24) NOT NULL --Content type: md,
    json,
    html,
    text,
    audience VARCHAR(40) DEFAULT NULL --Target audience: human,
    developer,
    machine,
    body CLOB NOT NULL --Document content,
    metadata CLOB DEFAULT NULL --Generation metadata,
    position INTEGER DEFAULT 0 NOT NULL --Display order,
    generated_at DATETIME DEFAULT NULL --When this doc was last generated,
    folio_code VARCHAR(120) NOT NULL --Unique folio identifier,
    matches dataset key,
    PRIMARY KEY (id),
    CONSTRAINT FK_51572BB73073E4E7 FOREIGN KEY (folio_code) REFERENCES folio (code) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE folio (
    code VARCHAR(120) NOT NULL --Unique folio identifier,
    matches dataset key,
    label VARCHAR(255) DEFAULT NULL --Human-readable display name,
    dataset_key VARCHAR(180) DEFAULT NULL --Dataset key from data-bundle (e.g. mus/cleveland),
    row_count INTEGER DEFAULT 0 NOT NULL --Total rows across all cores,
    PRIMARY KEY (code)
);

CREATE TABLE item_facet (
    item_rowid INTEGER NOT NULL,
    field TEXT NOT NULL,
    value TEXT NOT NULL
);

Indexes

CREATE TABLE item_facet_count (
    core TEXT NOT NULL,
    field TEXT NOT NULL,
    value TEXT NOT NULL,
    total INTEGER NOT NULL,
    PRIMARY KEY (core, field, value)
);

Indexes

CREATE TABLE 'item_fts_data'(
    id INTEGER PRIMARY KEY,
    block BLOB
);

CREATE TABLE 'item_fts_docsize'(
    id INTEGER PRIMARY KEY,
    sz BLOB
);

CREATE TABLE link (
    id VARCHAR(32) NOT NULL --xxh128 hash of type|leftId|rightId,
    left_core VARCHAR(80) NOT NULL --Source core code,
    left_id VARCHAR(180) NOT NULL --Source row local_id,
    right_core VARCHAR(80) NOT NULL --Target core code,
    right_id VARCHAR(180) NOT NULL --Target row local_id,
    extras CLOB DEFAULT NULL --Extra link metadata,
    link_type_id VARCHAR(260) NOT NULL --Composite: folioCode:leftCore:code:rightCore,
    PRIMARY KEY (id),
    CONSTRAINT FK_36AC99F1B82AD666 FOREIGN KEY (link_type_id) REFERENCES link_type (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE link_type (
    id VARCHAR(260) NOT NULL --Composite: folioCode:leftCore:code:rightCore,
    left_core VARCHAR(80) NOT NULL --Source core code,
    code VARCHAR(120) NOT NULL --Link type code,
    right_core VARCHAR(80) NOT NULL --Target core code,
    reverse_code VARCHAR(120) DEFAULT NULL --Inverse link code,
    label VARCHAR(255) DEFAULT NULL --Human-readable label,
    reverse_label VARCHAR(255) DEFAULT NULL,
    folio_code VARCHAR(120) NOT NULL --Unique folio identifier,
    matches dataset key,
    PRIMARY KEY (id),
    CONSTRAINT FK_3D99C5803073E4E7 FOREIGN KEY (folio_code) REFERENCES folio (code) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE page (
    id VARCHAR(320) NOT NULL --Composite: {rowId}#{seq},
    seq INTEGER DEFAULT 1 NOT NULL,
    page_index INTEGER DEFAULT 0 NOT NULL,
    type VARCHAR(16) DEFAULT NULL,
    url CLOB NOT NULL,
    media_id VARCHAR(32) DEFAULT NULL,
    text CLOB DEFAULT NULL,
    dense_summary CLOB DEFAULT NULL,
    ledger CLOB DEFAULT NULL,
    layout CLOB DEFAULT NULL,
    width INTEGER DEFAULT NULL,
    height INTEGER DEFAULT NULL,
    row_id VARCHAR(260) NOT NULL --Composite: folioCode:coreCode:localId,
    htr CLOB DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FK_140AB62083A269F2 FOREIGN KEY (row_id) REFERENCES item (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE schema_property (
    id VARCHAR(420) NOT NULL --Composite: tableId:propertyName,
    name VARCHAR(180) NOT NULL --Field name as observed in dto_data,
    label VARCHAR(180) DEFAULT NULL --Human-readable label,
    description CLOB DEFAULT NULL --Field description from DTO or docblock,
    type VARCHAR(80) DEFAULT NULL --Inferred or declared data type,
    declaring_class VARCHAR(255) DEFAULT NULL --PHP class that declares this property,
    stats CLOB DEFAULT NULL --Profiler stats from jsonl-bundle,
    position INTEGER DEFAULT 0 NOT NULL --Display order,
    visible BOOLEAN DEFAULT 1 NOT NULL --Show in default UI views,
    searchable BOOLEAN DEFAULT 0 NOT NULL --Include in full-text search,
    filterable BOOLEAN DEFAULT 0 NOT NULL --Available as a filter,
    facet BOOLEAN DEFAULT 0 NOT NULL --Available as a facet,
    sortable BOOLEAN DEFAULT 0 NOT NULL --Available for sorting,
    table_id VARCHAR(260) NOT NULL --Composite: folioCode:tableName,
    PRIMARY KEY (id),
    CONSTRAINT FK_3211F28AECFF285C FOREIGN KEY (table_id) REFERENCES schema_table (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE schema_table (
    id VARCHAR(260) NOT NULL --Composite: folioCode:tableName,
    core_code VARCHAR(80) NOT NULL --Core this table describes,
    name VARCHAR(180) NOT NULL --Table/view name (e.g. dto_drawing),
    kind VARCHAR(32) NOT NULL --Table kind: core or dto,
    dto_type VARCHAR(255) DEFAULT NULL --DTO type identifier,
    dto_class VARCHAR(255) DEFAULT NULL --PHP DTO class (provenance hint),
    label VARCHAR(255) DEFAULT NULL --Human-readable display name,
    description CLOB DEFAULT NULL --Table description,
    row_count INTEGER DEFAULT 0 NOT NULL --Number of rows matching this table,
    folio_code VARCHAR(120) NOT NULL --Unique folio identifier,
    matches dataset key,
    PRIMARY KEY (id),
    CONSTRAINT FK_9E0FD57F3073E4E7 FOREIGN KEY (folio_code) REFERENCES folio (code) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE str (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    code VARCHAR(64) NOT NULL,
    source_locale VARCHAR(10) NOT NULL,
    source CLOB NOT NULL,
    context VARCHAR(80) DEFAULT NULL,
    meta CLOB DEFAULT '[]' NOT NULL
);

Indexes

CREATE TABLE str_tr (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    str_code VARCHAR(64) NOT NULL,
    target_locale VARCHAR(10) NOT NULL,
    engine VARCHAR(20) DEFAULT NULL,
    text CLOB DEFAULT NULL,
    status VARCHAR(16) DEFAULT 'new' NOT NULL,
    updated_at DATETIME DEFAULT NULL,
    meta CLOB DEFAULT '[]' NOT NULL
);

Indexes

CREATE TABLE term (
    id VARCHAR(260) NOT NULL --Composite: termSetId:code,
    code VARCHAR(180) NOT NULL --Term code within the vocabulary,
    path VARCHAR(512) NOT NULL --Materialized path for hierarchy,
    label VARCHAR(500) DEFAULT NULL --Human-readable display label,
    description CLOB DEFAULT NULL --Term description,
    rules CLOB DEFAULT NULL --Validation and constraint rules,
    meta CLOB DEFAULT NULL --Arbitrary term metadata,
    extras CLOB DEFAULT NULL --Extra non-canonical fields,
    enabled BOOLEAN DEFAULT 1 NOT NULL --Whether this term is active,
    sort INTEGER DEFAULT NULL --Sort order within parent,
    term_set_id VARCHAR(180) NOT NULL --Composite: folioCode:code,
    parent_id VARCHAR(260) DEFAULT NULL --Composite: termSetId:code,
    PRIMARY KEY (id),
    CONSTRAINT FK_A50FE78DF3AD3475 FOREIGN KEY (term_set_id) REFERENCES term_set (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT FK_A50FE78D727ACA70 FOREIGN KEY (parent_id) REFERENCES term (id) ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE TABLE term_set (
    id VARCHAR(180) NOT NULL --Composite: folioCode:code,
    code VARCHAR(120) NOT NULL --Vocabulary code within the folio,
    label VARCHAR(255) DEFAULT NULL --Human-readable display name,
    description CLOB DEFAULT NULL --Vocabulary description,
    rules CLOB DEFAULT NULL --Validation and constraint rules,
    meta CLOB DEFAULT NULL --Arbitrary vocabulary metadata,
    enabled BOOLEAN DEFAULT 1 NOT NULL --Whether this vocabulary is active,
    folio_code VARCHAR(120) NOT NULL --Unique folio identifier,
    matches dataset key,
    "source_locale" VARCHAR(10) DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FK_39C5DF4D3073E4E7 FOREIGN KEY (folio_code) REFERENCES folio (code) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

CREATE VIEW "dto_coll_collection" AS
SELECT
    local_id,
    label,
    dto_type,
    substr(core_id, instr(core_id, ':') + 1) AS core_code,
    json_extract(dto_data, '$.contentType') AS "contentType",
    --dcterms:title
    json_extract(dto_data, '$.title') AS "title",
    --Source of this data: import | ai | ocr | human
    json_extract(dto_data, '$.source') AS "source"
FROM item
WHERE dto_type = 'collection'
  AND substr(core_id, instr(core_id, ':') + 1) = 'coll';

CREATE VIEW "dto_doc_document" AS
SELECT
    local_id,
    label,
    dto_type,
    substr(core_id, instr(core_id, ':') + 1) AS core_code,
    json_extract(dto_data, '$.sourceUrl') AS "sourceUrl",
    json_extract(dto_data, '$.contentType') AS "contentType",
    --dcterms:title
    json_extract(dto_data, '$.title') AS "title",
    --ItemField::CITATION_URL — deep link back to the source record (e.g. the NARA catalog page). Rendered as the "Original" link on the folio item page.
    json_extract(dto_data, '$.citationUrl') AS "citationUrl",
    --Collection name(s)
    json_extract(dto_data, '$.collections') AS "collections",
    --IIIF Image API base URL — use for AI vision and imgProxy resizing
    json_extract(dto_data, '$.iiifBase') AS "iiifBase",
    json_extract(dto_data, '$.thumbnailUrl') AS "thumbnailUrl",
    json_extract(dto_data, '$.largeImageUrl') AS "largeImageUrl",
    --Number of images associated with the item.
    json_extract(dto_data, '$.imageCount') AS "imageCount",
    --Whether the item has at least one image; facet for filtering out image-less objects.
    json_extract(dto_data, '$.hasImages') AS "hasImages",
    --Source of this data: import | ai | ocr | human
    json_extract(dto_data, '$.source') AS "source",
    json_extract(dto_data, '$.hasTranscription') AS "hasTranscription"
FROM item
WHERE dto_type = 'document'
  AND substr(core_id, instr(core_id, ':') + 1) = 'doc';

CREATE VIEW "dto_doc_film" AS
SELECT
    local_id,
    label,
    dto_type,
    substr(core_id, instr(core_id, ':') + 1) AS core_code,
    json_extract(dto_data, '$.sourceUrl') AS "sourceUrl",
    json_extract(dto_data, '$.contentType') AS "contentType",
    --dcterms:title
    json_extract(dto_data, '$.title') AS "title",
    --dcterms:description — short curatorial text from the source institution
    json_extract(dto_data, '$.description') AS "description",
    --ItemField::CITATION_URL — deep link back to the source record (e.g. the NARA catalog page). Rendered as the "Original" link on the folio item page.
    json_extract(dto_data, '$.citationUrl') AS "citationUrl",
    --dcterms:identifier — local accession number
    json_extract(dto_data, '$.identifierLocal') AS "identifierLocal",
    --Collection name(s)
    json_extract(dto_data, '$.collections') AS "collections",
    --IIIF Image API base URL — use for AI vision and imgProxy resizing
    json_extract(dto_data, '$.iiifBase') AS "iiifBase",
    json_extract(dto_data, '$.thumbnailUrl') AS "thumbnailUrl",
    json_extract(dto_data, '$.largeImageUrl') AS "largeImageUrl",
    --Number of images associated with the item.
    json_extract(dto_data, '$.imageCount') AS "imageCount",
    --Whether the item has at least one image; facet for filtering out image-less objects.
    json_extract(dto_data, '$.hasImages') AS "hasImages",
    --Source of this data: import | ai | ocr | human
    json_extract(dto_data, '$.source') AS "source",
    json_extract(dto_data, '$.hasTranscription') AS "hasTranscription",
    --dcterms:subject — keyword/topical subjects (incl. AI keywords). A sidebar facet.
    json_extract(dto_data, '$.subjects') AS "subjects"
FROM item
WHERE dto_type = 'film'
  AND substr(core_id, instr(core_id, ':') + 1) = 'doc';

CREATE VIEW "dto_series_document" AS
SELECT
    local_id,
    label,
    dto_type,
    substr(core_id, instr(core_id, ':') + 1) AS core_code,
    json_extract(dto_data, '$.contentType') AS "contentType",
    --dcterms:title
    json_extract(dto_data, '$.title') AS "title",
    --Source of this data: import | ai | ocr | human
    json_extract(dto_data, '$.source') AS "source",
    json_extract(dto_data, '$.hasTranscription') AS "hasTranscription"
FROM item
WHERE dto_type = 'document'
  AND substr(core_id, instr(core_id, ':') + 1) = 'series';