Schema: dc/ws85h122t

Diagram

claim claim_run core docs folio item_facet item_facet_count item_fts_data item_fts_docsize link link_type page schema_property schema_table str str_tr term term_set

dto_coll_collection dto_document

Entity-Linkship Diagram

claim

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,
    run_id VARCHAR(26) DEFAULT NULL --ClaimRun ULID for audit detail,
    item_id VARCHAR(260) NOT NULL --Composite: folioCode:coreCode:localId,
    PRIMARY KEY (id),
    CONSTRAINT FK_A769DE27126F525E FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

Name	Columns	Unique
idx_claim_source	source
idx_claim_predicate	predicate
IDX_A769DE27126F525E	item_id

claim_run

CREATE TABLE claim_run (
    id TEXT PRIMARY KEY,
    scope TEXT,
    subject_type TEXT,
    subject_id TEXT,
    source TEXT,
    model TEXT,
    prompt TEXT,
    response TEXT,
    input_tokens INTEGER,
    output_tokens INTEGER,
    image_tokens INTEGER,
    duration_ms INTEGER,
    claim_count INTEGER,
    created_at TEXT
);

core

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

Name	Columns	Unique
IDX_6B8D854F3073E4E7	folio_code
uniq_core_code	folio_code, code	yes

docs

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

Name	Columns	Unique
idx_docs_type	type
IDX_51572BB7FDCD9418	audience
IDX_51572BB78CDE5729	type
IDX_51572BB73073E4E7	folio_code

folio

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)
);

item_facet

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

Indexes

Name	Columns	Unique
idx_item_facet_rowid_field	item_rowid, field
idx_item_facet_field_value_rowid	field, value, item_rowid

item_facet_count

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

Name	Columns	Unique
idx_item_facet_count_core_field_total	core, field, total

item_fts_data

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

item_fts_docsize

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

link

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

Name	Columns	Unique
idx_link_right	right_core, right_id
idx_link_left	left_core, left_id
IDX_36AC99F1B82AD666	link_type_id

link_type

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

Name	Columns	Unique
IDX_3D99C5803073E4E7	folio_code
uniq_link_type	folio_code, left_core, code, right_core	yes

page

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,
    htr 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,
    PRIMARY KEY (id),
    CONSTRAINT FK_140AB62083A269F2 FOREIGN KEY (row_id) REFERENCES item (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

Name	Columns	Unique
idx_page_media	media_id
idx_page_row	row_id

schema_property

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

Name	Columns	Unique
idx_schema_property_name	name
IDX_3211F28A56B9BA28	facet
IDX_3211F28A94CD8C0D	searchable
IDX_3211F28A5E237E06	name
IDX_3211F28AECFF285C	table_id
uniq_schema_property_table_name	table_id, name	yes

schema_table

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

Name	Columns	Unique
idx_schema_table_core	core_code
IDX_9E0FD57FC7DFE8B0	dto_type
IDX_9E0FD57F45F0F3AD	core_code
IDX_9E0FD57F3073E4E7	folio_code

str

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

Name	Columns	Unique
str_code_uq	code	yes

str_tr

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

Name	Columns	Unique
str_tr_uq	str_code, target_locale, engine	yes

term

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

Name	Columns	Unique
idx_term_path	term_set_id, path
IDX_A50FE78D727ACA70	parent_id
IDX_A50FE78DF3AD3475	term_set_id
uniq_term_path	term_set_id, path	yes
uniq_term_code	term_set_id, code	yes

term_set

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

Name	Columns	Unique
IDX_39C5DF4D3073E4E7	folio_code
uniq_term_set_code	folio_code, code	yes

dto_coll_collection

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",
    --Pipeline processing stage (raw / normalized / enriched). A facet so you can filter to enriched items; later this can be made dev-only.
    json_extract(dto_data, '$.stage') AS "stage",
    --dcterms:title
    json_extract(dto_data, '$.title') AS "title",
    --Number of pages in the document.
    json_extract(dto_data, '$.pageCount') AS "pageCount",
    --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';

dto_document

CREATE VIEW "dto_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",
    --Pipeline processing stage (raw / normalized / enriched). A facet so you can filter to enriched items; later this can be made dev-only.
    json_extract(dto_data, '$.stage') AS "stage",
    --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",
    --dcterms:date — display string, may be fuzzy ("ca. 1920")
    json_extract(dto_data, '$.date') AS "date",
    --Coverage/production year.
    json_extract(dto_data, '$.year') AS "year",
    --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:rights
    json_extract(dto_data, '$.rights') AS "rights",
    --dcterms:license URI (rightsstatements.org)
    json_extract(dto_data, '$.rightsUri') AS "rightsUri",
    --dcterms:accessRights — e.g. "no restrictions", "creative commons"
    json_extract(dto_data, '$.reuseAllowed') AS "reuseAllowed",
    --dcterms:language
    json_extract(dto_data, '$.language') AS "language",
    --dcterms:identifier — local accession number
    json_extract(dto_data, '$.identifierLocal') AS "identifierLocal",
    --Holding institution
    json_extract(dto_data, '$.institution') AS "institution",
    --Collection name(s)
    json_extract(dto_data, '$.collections') AS "collections",
    --dcterms:spatial — geographic subjects
    json_extract(dto_data, '$.subjectsGeographic') AS "subjectsGeographic",
    --Broad genre/form terms (MODS genre @basic) — e.g. ["Photographs"]. A sidebar facet.
    json_extract(dto_data, '$.genreBasic') AS "genreBasic",
    --MODS typeOfResource — coarse resource class, e.g. ["Still image"], ["Text"]. A facet.
    json_extract(dto_data, '$.typeOfResource') AS "typeOfResource",
    json_extract(dto_data, '$.country') AS "country",
    json_extract(dto_data, '$.state') AS "state",
    json_extract(dto_data, '$.county') AS "county",
    json_extract(dto_data, '$.city') AS "city",
    --IIIF Image API base URL — use for AI vision and imgProxy resizing
    json_extract(dto_data, '$.iiifBase') AS "iiifBase",
    json_extract(dto_data, '$.iiifManifest') AS "iiifManifest",
    json_extract(dto_data, '$.thumbnailUrl') AS "thumbnailUrl",
    json_extract(dto_data, '$.largeImageUrl') AS "largeImageUrl",
    --Number of pages in the document.
    json_extract(dto_data, '$.pageCount') AS "pageCount",
    --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) = 'obj';

Survos folio schema

Entity-Linkship Diagram

claim

Indexes

claim_run

core

Indexes

docs

Indexes

folio

item_facet

Indexes

item_facet_count

Indexes

item_fts_data

item_fts_docsize

link

Indexes

link_type

Indexes

page

Indexes

schema_property

Indexes

schema_table

Indexes

str

Indexes

str_tr

Indexes

term

Indexes

term_set

Indexes

dto_coll_collection

dto_document