Schema: mus/aust

claim core docs folio item link link_type page schema_property schema_table str str_tr term term_set

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

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
uniq_core_code	folio_code, code	yes
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_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

CREATE TABLE item (
    id VARCHAR(260) NOT NULL --Composite: folioCode:coreCode:localId,
    local_id VARCHAR(180) NOT NULL --Original ID from source data,
    label VARCHAR(500) DEFAULT NULL --Display label,
    dto_type VARCHAR(255) DEFAULT NULL --DTO type identifier (e.g. drawing, photograph),
    dto_data CLOB DEFAULT NULL --Canonical DTO-shaped JSON,
    extras CLOB DEFAULT NULL --Non-DTO source fields,
    raw CLOB DEFAULT NULL --Optional raw source copy,
    core_id VARCHAR(180) NOT NULL --Composite: folioCode:coreCode,
    PRIMARY KEY (id),
    CONSTRAINT FK_1F1B251EC87A9C35 FOREIGN KEY (core_id) REFERENCES core (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

Indexes

Name	Columns	Unique
uniq_item_core_local	core_id, local_id	yes
idx_item_label	label
IDX_1F1B251EC87A9C35	core_id

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
uniq_link_type	folio_code, left_core, code, right_core	yes
IDX_3D99C5803073E4E7	folio_code

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
uniq_schema_property_table_name	table_id, name	yes
IDX_3211F28A56B9BA28	facet
IDX_3211F28A94CD8C0D	searchable
IDX_3211F28A5E237E06	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_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
uniq_term_path	term_set_id, path	yes
uniq_term_code	term_set_id, code	yes
IDX_A50FE78D727ACA70	parent_id
IDX_A50FE78DF3AD3475	term_set_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,
    "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
uniq_term_set_code	folio_code, code	yes
IDX_39C5DF4D3073E4E7	folio_code

Survos folio schema

Entity-Linkship Diagram

claim

Indexes

core

Indexes

docs

Indexes

folio

item

Indexes

link

Indexes

link_type

Indexes

page

Indexes

schema_property

Indexes

schema_table

Indexes

str

Indexes

str_tr

Indexes

term

Indexes

term_set

Indexes