Scanseum

Diagram

claim core docs folio item_facet item_facet_count item_fts_data item_fts_docsize link link_type schema_property schema_table term term_set

dto_photograph

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,
    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_A769DE27126F525E	item_id

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 normalized profile,
    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

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_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 (
    field TEXT NOT NULL,
    value TEXT NOT NULL,
    total INTEGER NOT NULL,
    PRIMARY KEY (field, value)
);

Indexes

Name	Columns	Unique
idx_item_facet_count_field_total	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

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_3211F28AECFF285C	table_id

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_9E0FD57F3073E4E7	folio_code

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_A50FE78DF3AD3475	term_set_id
IDX_A50FE78D727ACA70	parent_id

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,
    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

dto_photograph

CREATE VIEW "dto_photograph" AS
SELECT
    local_id,
    label,
    dto_type,
    substr(core_id, instr(core_id, ':') + 1) AS core_code,
    json_extract(dto_data, '$.sourceId') AS "sourceId",
    json_extract(dto_data, '$.contentType') AS "contentType",
    json_extract(dto_data, '$.aggregator') AS "aggregator",
    json_extract(dto_data, '$.dctermsDate') AS "dctermsDate",
    json_extract(dto_data, '$.dctermsDescription') AS "dctermsDescription",
    json_extract(dto_data, '$.dctermsSource') AS "dctermsSource",
    json_extract(dto_data, '$.dctermsLicense') AS "dctermsLicense",
    --IIIF Image API base URL — use for AI vision and imgProxy resizing
    json_extract(dto_data, '$.iiifBase') AS "iiifBase",
    json_extract(dto_data, '$.country') AS "country",
    json_extract(dto_data, '$.city') AS "city",
    json_extract(dto_data, '$.schemaLatitude') AS "schemaLatitude",
    json_extract(dto_data, '$.schemaLongitude') AS "schemaLongitude",
    json_extract(dto_data, '$.sourceTags') AS "sourceTags",
    json_extract(dto_data, '$.sourceTagsHu') AS "sourceTagsHu",
    json_extract(dto_data, '$.donorId') AS "donorId",
    json_extract(dto_data, '$.countryId') AS "countryId",
    json_extract(dto_data, '$.cityId') AS "cityId",
    --dcterms:date — display string, may be fuzzy ("ca. 1920")
    json_extract(dto_data, '$.date') AS "date",
    --dcterms:description — short curatorial text from the source institution
    json_extract(dto_data, '$.description') AS "description",
    --dcterms:license URI (rightsstatements.org)
    json_extract(dto_data, '$.rightsUri') AS "rightsUri",
    json_extract(dto_data, '$.sourceUrl') AS "sourceUrl",
    json_extract(dto_data, '$.latitude') AS "latitude",
    json_extract(dto_data, '$.longitude') AS "longitude",
    --Source of this data: import | ai | ocr | human
    json_extract(dto_data, '$.source') AS "source",
    --dcterms:title
    json_extract(dto_data, '$.title') AS "title",
    json_extract(dto_data, '$.ai:denseSummary') AS "ai:denseSummary",
    --dcterms:language
    json_extract(dto_data, '$.language') AS "language",
    json_extract(dto_data, '$.content_type') AS "content_type",
    --ai:denseSummary — ≤ 400 char retrieval-optimised summary. Entity-rich, factual, no filler. Used by Meilisearch /chat, RAG, chatbots. /
    json_extract(dto_data, '$.denseSummary') AS "denseSummary",
    --Confidence 0.0–1.0 (null = certain, i.e. human-entered)
    json_extract(dto_data, '$.confidence') AS "confidence"
FROM item
WHERE dto_type = 'photograph'
  AND substr(core_id, instr(core_id, ':') + 1) = 'obj';

survos_folio_schema

Schema: fortepan/hu

Entity-Linkship Diagram

claim

Indexes

core

Indexes

docs

Indexes

folio

item_facet

Indexes

item_facet_count

Indexes

item_fts_data

item_fts_docsize

link

Indexes

link_type

Indexes

schema_property

Indexes

schema_table

Indexes

term

Indexes

term_set

Indexes

dto_photograph