Add durable per-file metadata (attributes, not JSONB)

Directive: when you need to persist a new, durable per-file fact — a tempo, a detected key, a summary, an “extended region” highlight, an editable display name — write it to the normalized storage.attributes table via ctx.set_attributes(...). Do not add it to the storage.files.metadata JSONB bag. The team is migrating off the single JSONB blob to a scoped entity-attribute store, and every new key you drop in the bag is one more thing to migrate later.

Deploy status

The base side tables (file_lyrics / file_provenance / file_lineage and the original file-only file_attributes) shipped on 2026-06-26. The generalization that this page documents — file_attributes → the entity-scoped storage.attributes, plus the editable display-name / cover read path — is in code but not yet deployed. The deploy gate is a proto republish (soundverse-proto → -go/-py/-web) plus the out-of-band migration scripts/db/migration/migrate-attributes-scoped-store.sql, which drops the empty old table and creates the new one. Run the migration before deploying the regenerated core-database — its file-returning queries LEFT JOIN the new table.

Why not the JSONB bag

storage.files.metadata is a single unindexed jsonb column. storage.attributes is a normalized EAV store keyed by (scope, scope_id, namespace, key). The difference that matters:

• Queryable & per-key — every fact is its own row with a numeric mirror (value_num) for future range queries, not a nested key buried in one blob.
• Ownership-scoped — denormalized user_id / workspace_id / project_id columns (with partial indexes) let reads narrow to an owner.
• Editable & audited — a set_by column records who last wrote (user / agent / tool name / system), so a user rename and a tool write are distinguishable.
• Any scope, zero DDL — the same table holds file, project, workspace, and user attributes. Adding a new attribute is a data change, never a schema change.

The table shape

The authoritative DDL lives in storage/v1/schema.sql:

storage.attributes (trimmed)

CREATE TABLE storage.attributes (
    scope        VARCHAR(16)  NOT NULL,  -- 'file' | 'project' | 'workspace' | 'user'
    scope_id     VARCHAR(255) NOT NULL,  -- file uuid (as text) / project / workspace / user id
    namespace    VARCHAR(40)  NOT NULL,  -- 'display' | 'cover' | 'summary' | 'audio' | 'extend' | ...
    key          VARCHAR(80)  NOT NULL,  -- 'name','tempo_bpm','file_id','blob_hash',...
    value_text   TEXT,                   -- string / serialized value
    value_num    DOUBLE PRECISION,       -- numeric mirror (NULL when non-numeric)
    value_json   JSONB,                  -- structured value (NULL when scalar)
    user_id      VARCHAR(255) NOT NULL DEFAULT '',  -- denormalized owner for scoped reads
    workspace_id VARCHAR(255) NOT NULL DEFAULT '',
    project_id   VARCHAR(255) NOT NULL DEFAULT '',
    set_by       VARCHAR(60)  NOT NULL DEFAULT '',   -- audit: who last wrote
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    PRIMARY KEY (scope, scope_id, namespace, key)
);

scope_id is VARCHAR (not uuid) on purpose — it holds file UUIDs and the string user / workspace / project ids that storage.files already uses. There is deliberately no FK to storage.files: the scope is polymorphic, and files are soft-deleted anyway, so the old ON DELETE CASCADE never fired in practice.

Write it from a tool

Inside a worker’s process() you already hold a TaskContext. It carries the attribute helpers — no direct RPC plumbing:

1. Persist the fact. ctx.set_attributes(file_id, namespace, mapping) merges a batch of keys under one namespace, scope file. It stamps the file’s owner / workspace / project onto every row so the store stays ownership-scopable.

   app/tools/my_tool.py (inside process)

   # after you've uploaded the file and have its file_id
   ctx.set_attributes(file_id, "audio", {"tempo_bpm": 128, "musical_key": "F#m"})
   
   # sugar wrappers for the common namespaces:
   ctx.set_audio_analysis(file_id, tempo_bpm=128, musical_key="F#m")
   ctx.set_summary(file_id, "Uptempo synthwave instrumental")
   ctx.set_display_name(file_id, "Neon Drive")      # display.name — what the library shows
   ctx.link_cover(song_file_id, image_file_id)      # cover.file_id / cover.blob_hash

   Every helper is best-effort — it swallows errors and logs, because metadata polish must never fail a generation. See context.py.

2. Understand value packing. pack_attributes shapes each value: a number → value_num (indexable), a bool / str → value_text, anything structured → JSON-encoded into value_text. None values are dropped. The value_json column exists for structured payloads but is only populated by direct UpsertAttributes callers — the ctx helpers do not emit it today.

3. Beyond files. For a project / workspace / user attribute, use ctx.set_entity_attributes(scope, scope_id, namespace, mapping) — same store, any scope.

upload_file(metadata=…) still writes the JSONB bag

Do not route durable data through ctx.upload_file(metadata=…) or live_audio_ingest().finalize(metadata=…). Those persist to storage.files.metadata (the bag) via InsertFile — verified. Keep upload metadata minimal (e.g. duration_ms) and send everything else through ctx.set_attributes.

Reserved keys & precedence

Two namespaces carry first-class, resolvable keys:

• display / name — the editable display name shown in the library in place of the immutable original_filename.
• cover / file_id and cover / blob_hash — the relinkable album-art cover on a song.

The backend resolves a file’s shown name in one place (mappers.resolve_display_name) with this precedence: display.name → metadata.song_name (legacy JSONB) → original_filename → "Untitled".

Read it back

The attribute store now has a consumer read surface (this closed an earlier gap):

• File list — ListFiles / GetFileById / GetFiles surface display_name, cover_file_id, and cover_blob_hash via LEFT JOINs, so the library shows editable names and covers without a second fetch.
• File detail — GetFileInfo (in library.py) fans out to ListAttributes(scope="file") and returns every file-scoped row in consumer.v1 FileInfoView.attributes (field 14). Callers filter by namespace — e.g. the extend namespace carries the extended-region highlight.

The underlying data-service RPCs are UpsertAttributes / ListAttributes on StorageDataService (batch-merge via json_to_recordset; list filtered by scope + scope_id + optional user_id), defined in storage.proto.

The codegen trap: JOINs in SELECT, subqueries only in RETURNING

A subquery in a SELECT projection breaks the scanner

The DB codegen plugin’s column extractor (projectedColumnsFromSQL in cmd/protoc-gen-soundverse-db/main.go) finds a SELECT’s columns by slicing up to the first " from ". A correlated subquery in the SELECT list contains its own FROM, so the extractor truncates the column list there and the generated row scanner mismatches its db_column fields.

The rule when you fold an attribute into a data-service query: use a LEFT JOIN in a SELECT (as ListFiles does for display_name / cover_*), and reserve correlated subqueries for the RETURNING clause — the extractor takes everything after " returning " without scanning for a FROM, so subqueries are safe there (see InsertFile / DeleteFile / UpdateFileMetadata). This is the same discipline as the $N placeholder rule.

Adding a whole new attribute-backed read

If a new key needs to reach the browser, the mechanics are the standard proto cascade:

1. Write the key from your tool with ctx.set_attributes.
2. If the frontend needs it structured, add a field to the relevant consumer.v1 view (e.g. another entry alongside FileInfoView.attributes), or read the generic attributes list and filter by namespace on the client.
3. Fold ListAttributes (or a LEFT JOIN) into the gateway servicer if it isn’t already surfaced.
4. Republish the proto and refresh stubs — see Drive the proto → deploy cascade and Refresh proto stubs.

Related

• Add a new generation tool worker — where process() lives
• TaskContext API reference — every ctx.* method, including the attribute helpers
• Add a field or RPC to a data service — the $N rule and the codegen guardrails
• How the DB codegen plugin works — the FROM-scan extractor in detail
• Data model overview — where the side tables sit
• Storage & media plane — the wider file/blob story