Data models

The SQLite schema used by the asset system. The runtime currently uses the database for assets only; other subsystems may join later. The full source is app/assets/database/models.py and app/database/models.py.

Naming conventions

app/database/models.py pins SQLAlchemy naming conventions so Alembic-generated migrations have stable names:

NAMING_CONVENTION = {
    "ix": "ix_%(table_name)s_%(column_0_N_name)s",
    "uq": "uq_%(table_name)s_%(column_0_N_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

All tables and indexes follow these patterns.

Tables

assets

One row per content-distinct file. Hash-keyed, no path information.

Column	Type	Notes
id	String(36) PK	UUID, default-generated
hash	String(256)	Content hash (BLAKE3 by default)
size_bytes	BigInteger	>= 0 enforced via ck_assets_size_nonneg
mime_type	String(255)
created_at	DateTime	UTC

Indexes:

• uq_assets_hash (unique on hash)
• ix_assets_mime_type

asset_references

A name + path + metadata for an asset. One asset can have many references.

Column	Type	Notes
id	String(36) PK	UUID
asset_id	String(36) FK	→ assets.id, ON DELETE CASCADE
file_path	Text	Absolute path. NULL means API-only (no on-disk presence)
mtime_ns	BigInteger	mtime in nanoseconds; nullable
needs_verify	Boolean	"Re-hash next time you see this"
is_missing	Boolean	True when the scanner couldn't find file_path
enrichment_level	Integer	0/1/2 (basic / mime+structural / full hash + thumbnail)
owner_id	String(128)	"" by default; non-empty in --multi-user mode
name	String(512)	User-facing label
preview_id	String(36) FK	→ asset_references.id, ON DELETE SET NULL — self-referential
user_metadata	JSON	User-editable
system_metadata	JSON	Extracted by services/metadata_extract.py
job_id	String(36)	Prompt that produced this output (for output assets)
created_at	DateTime
updated_at	DateTime
last_access_time	DateTime
deleted_at	DateTime	Soft delete timestamp

Indexes:

• uq_asset_references_file_path (unique on file_path)
• ix_asset_references_asset_id
• ix_asset_references_owner_id
• ix_asset_references_name
• ix_asset_references_is_missing
• ix_asset_references_enrichment_level
• ix_asset_references_created_at
• ix_asset_references_last_access_time
• ix_asset_references_deleted_at
• ix_asset_references_preview_id
• ix_asset_references_owner_name — composite (owner_id, name)

Constraints:

• ck_ar_mtime_nonneg — mtime_ns IS NULL OR mtime_ns >= 0
• ck_ar_enrichment_level_range — 0 <= enrichment_level <= 2

asset_reference_meta

Free-form key/value metadata. Composite primary key allows multiple values per key (ordinal).

Column	Type	Notes
asset_reference_id	String(36) FK PK	→ asset_references.id, ON DELETE CASCADE
key	String(256) PK
ordinal	Integer PK	Default 0
val_str	String(2048)	One of these four must be non-NULL (has_value check constraint)
val_num	Numeric(38,10)
val_bool	Boolean
val_json	JSON

Indexes:

• ix_asset_reference_meta_key
• ix_asset_reference_meta_key_val_str — (key, val_str)
• ix_asset_reference_meta_key_val_num — (key, val_num)
• ix_asset_reference_meta_key_val_bool — (key, val_bool)

Constraint:

• has_value — at least one of val_str/val_num/val_bool/val_json is non-NULL.

tags

Tag dictionary.

Column	Type	Notes
name	String(512) PK
tag_type	String(32)	Default "user". Other types: "system", etc.

Indexes:

• ix_tags_tag_type

asset_reference_tags

Many-to-many between references and tags.

Column	Type	Notes
asset_reference_id	String(36) FK PK	→ asset_references.id, ON DELETE CASCADE
tag_name	String(512) FK PK	→ tags.name, ON DELETE RESTRICT
origin	String(32)	Default "manual". E.g., "system", "automatic"
added_at	DateTime

Indexes:

• ix_asset_reference_tags_tag_name
• ix_asset_reference_tags_asset_reference_id

Relationships

erDiagram
    ASSETS ||--o{ ASSET_REFERENCES : "1..n by asset_id"
    ASSET_REFERENCES ||--o{ ASSET_REFERENCE_META : "0..n"
    ASSET_REFERENCES ||--o{ ASSET_REFERENCE_TAGS : "0..n"
    TAGS ||--o{ ASSET_REFERENCE_TAGS : "0..n"
    ASSET_REFERENCES ||--o| ASSET_REFERENCES : "preview_id self-ref"

A reference's preview_id points at another reference (typically a thumbnail) so previews are themselves first-class assets. ON DELETE SET NULL on this FK lets you drop a thumbnail without nuking the reference that points at it.

Migrations

Migrations live in alembic_db/versions/. The runtime applies pending migrations on startup via alembic upgrade head in app/database/db.py. Failed migrations roll back to a .bkp of the database.

In-memory SQLite (sqlite:///:memory:) skips Alembic and uses Base.metadata.create_all directly because each connection in an in-memory DB is a separate database — Alembic's connection-using migration would create tables that vanish.