PostgreSQL DB Schema Samples / 融合优先 DDL 草案与查询样例

更新：2026-06-04 目标：把当前 song-centric + 融合优先 设计落成一版可以直接评审和继续实现的 PostgreSQL DDL 草案。 SQL 文件：acr-engine/sql/acr_pg_schema_songcentric_v1.sql live smoke：acr-engine/scripts/smoke_songcentric_schema_live.py

---

一页结论

当前默认物理模型只看 4 张表：

media_entity -> audio_object -> feature_fact -> set_membership

对应逻辑语义：

song -> asset -> window -> fingerprint / embedding

其中：

• media_entity：当前默认只承载 song
• audio_object：统一承载 asset 与 window
• feature_fact：统一承载 fingerprint 与 embedding
• set_membership：统一承载 reference / hot / eval 等集合关系

---

1. 4 张表分别存什么

表	当前主要 type	存什么	为什么存在
media_entity	song	歌曲主实体	最终归属对象是 song_id
audio_object	asset, window	原始音频文件 + 切片	同一个 song 下可有多个音频，切片仍需 evidence
feature_fact	fingerprint, embedding	模型、feature set、特征结果	统一 exact/semantic 特征事实
set_membership	reference_set, eval_set, hot_set	谁属于哪个集合	管理 reference 与评测范围

---

2. 当前推荐 DDL 草案

2.1 media_entity

create table if not exists media_entity (
    entity_id           bigserial primary key,
    entity_type         text not null check (entity_type in ('song', 'work', 'recording')),
    root_song_id        bigint,
    parent_entity_id    bigint,
    biz_key             text,
    title               text not null,
    artist_name         text,
    entity_status       text not null default 'active',
    metadata_json       jsonb not null default '{}'::jsonb,
    created_at          timestamptz not null default now(),
    updated_at          timestamptz not null default now(),
    constraint fk_media_entity_root_song
        foreign key (root_song_id) references media_entity(entity_id),
    constraint fk_media_entity_parent
        foreign key (parent_entity_id) references media_entity(entity_id)
);

create unique index if not exists uq_media_entity_song_biz_key
    on media_entity(entity_type, biz_key)
    where biz_key is not null;

create index if not exists idx_media_entity_root_song
    on media_entity(root_song_id);

2.2 audio_object

create table if not exists audio_object (
    object_id             bigserial primary key,
    object_type           text not null check (object_type in ('asset', 'window')),
    song_id               bigint not null references media_entity(entity_id),
    parent_object_id      bigint references audio_object(object_id),
    source_type           text,
    storage_uri           text,
    storage_scheme        text,
    checksum              text,
    codec                 text,
    sample_rate           integer,
    channels              integer,
    duration_ms           integer,
    start_ms              integer,
    end_ms                integer,
    object_status         text not null default 'ready',
    metadata_json         jsonb not null default '{}'::jsonb,
    created_at            timestamptz not null default now(),
    updated_at            timestamptz not null default now(),
    constraint ck_audio_object_window_parent
        check (
            (object_type = 'asset' and parent_object_id is null)
            or (object_type = 'window' and parent_object_id is not null)
        )
);

create index if not exists idx_audio_object_song_type
    on audio_object(song_id, object_type);

create index if not exists idx_audio_object_parent
    on audio_object(parent_object_id);

create unique index if not exists uq_audio_object_asset_checksum
    on audio_object(song_id, checksum)
    where object_type = 'asset' and checksum is not null;

create unique index if not exists uq_audio_object_window_range
    on audio_object(parent_object_id, start_ms, end_ms)
    where object_type = 'window';

2.3 feature_fact

create table if not exists feature_fact (
    feature_id            bigserial primary key,
    feature_type          text not null check (feature_type in ('fingerprint', 'embedding')),
    object_id             bigint not null references audio_object(object_id),
    song_id               bigint not null references media_entity(entity_id),
    model_name            text not null,
    model_version         text not null,
    feature_set_name      text not null,
    feature_schema_ver    text not null default 'v1',
    embedding_dim         integer,
    fingerprint_value     text,
    embedding_uri         text,
    vector_table_name     text,
    checksum              text,
    feature_status        text not null default 'ready',
    metadata_json         jsonb not null default '{}'::jsonb,
    created_at            timestamptz not null default now(),
    updated_at            timestamptz not null default now(),
    constraint ck_feature_payload
        check (
            (feature_type = 'fingerprint' and fingerprint_value is not null)
            or (feature_type = 'embedding' and (embedding_uri is not null or vector_table_name is not null))
        )
);

create index if not exists idx_feature_fact_object_type
    on feature_fact(object_id, feature_type);

create index if not exists idx_feature_fact_song_type
    on feature_fact(song_id, feature_type);

create unique index if not exists uq_feature_fact_embedding
    on feature_fact(object_id, model_name, model_version, feature_set_name, feature_type)
    where feature_type = 'embedding';

create unique index if not exists uq_feature_fact_fingerprint
    on feature_fact(object_id, model_name, model_version, feature_set_name, feature_type)
    where feature_type = 'fingerprint';

2.4 set_membership

create table if not exists set_membership (
    membership_id         bigserial primary key,
    set_type              text not null check (set_type in ('reference_set', 'eval_set', 'hot_set')),
    set_name              text not null,
    member_type           text not null check (member_type in ('song', 'asset', 'window', 'feature')),
    member_id             bigint not null,
    song_id               bigint references media_entity(entity_id),
    is_active             boolean not null default true,
    priority              integer not null default 100,
    metadata_json         jsonb not null default '{}'::jsonb,
    created_at            timestamptz not null default now(),
    updated_at            timestamptz not null default now()
);

create unique index if not exists uq_set_membership_unique
    on set_membership(set_type, set_name, member_type, member_id);

create index if not exists idx_set_membership_set_lookup
    on set_membership(set_type, set_name, is_active, priority);

---

3. 切片 / 模型 / feature 到底落哪张表

对象	落表	关键字段
song	media_entity	entity_type='song'
原始音频	audio_object	object_type='asset'
切片窗口	audio_object	object_type='window', parent_object_id=<asset_id>
指纹特征	feature_fact	feature_type='fingerprint'
embedding 特征	feature_fact	feature_type='embedding'
模型名/版本	feature_fact	model_name, model_version
feature set	feature_fact	feature_set_name, feature_schema_ver
reference 集归属	set_membership	set_type='reference_set'

---

4. 流程图

4.1 落库流程

flowchart TD
    A[media_entity\nentity_type=song] --> B[audio_object\nobject_type=asset]
    B --> C[audio_object\nobject_type=window]
    C --> D1[feature_fact\nfeature_type=fingerprint]
    C --> D2[feature_fact\nfeature_type=embedding]
    B --> E[set_membership\nreference_set]
    C --> E

4.2 查询回溯流程

flowchart LR
    A[feature_fact] --> B[audio_object window]
    B --> C[audio_object asset]
    C --> D[media_entity song]

4.3 写入时序图

sequenceDiagram
    participant ING as Ingest/Extract Job
    participant DB as PostgreSQL

    ING->>DB: insert media_entity(song)
    ING->>DB: insert audio_object(asset)
    ING->>DB: insert audio_object(window)
    ING->>DB: insert feature_fact(fingerprint)
    ING->>DB: insert feature_fact(embedding)
    ING->>DB: insert set_membership(reference_set)

4.4 Phase-1 bootstrap 流程

flowchart TD
    A[bootstrap_songcentric_phase1_live.py] --> B[media_entity song x N]
    B --> C[audio_object asset x N]
    C --> D[audio_object window x N]
    D --> E1[feature_fact fingerprint x N]
    D --> E2[feature_fact embedding x N]
    C --> F[set_membership reference_set x N]

当前 live bootstrap 脚本：acr-engine/scripts/bootstrap_songcentric_phase1_live.py

4.5 Manifest 导入流程

flowchart TD
    A[songcentric_manifest_sample.jsonl] --> B[import_songcentric_manifest_live.py]
    B --> C[media_entity song]
    B --> D[audio_object asset]
    B --> E[audio_object window x N]
    B --> F[feature_fact]
    B --> G[set_membership]

当前样例 manifest：acr-engine/data/pgvector_eval/music20/songcentric_manifest_sample.jsonl 当前导入脚本：acr-engine/scripts/import_songcentric_manifest_live.py

当前带 feature 样例 manifest：acr-engine/data/pgvector_eval/music20/songcentric_feature_manifest_sample.jsonl

4.6 真实目录生成 manifest 流程

flowchart TD
    A[real audio directory] --> B[build_songcentric_manifest_from_directory.py]
    B --> C[songcentric_directory_manifest.jsonl]
    C --> D[import_songcentric_manifest_live.py]
    D --> E[media_entity]
    D --> F[audio_object]
    D --> G[set_membership]

当前目录构建脚本：acr-engine/scripts/build_songcentric_manifest_from_directory.py

4.7 真实目录补特征再导入流程

flowchart TD
    A[real audio directory] --> B[build_songcentric_manifest_from_directory.py]
    B --> C[songcentric_directory_manifest.jsonl]
    C --> D[enrich_songcentric_manifest_with_local_features.py]
    D --> E[songcentric_directory_manifest_with_features.jsonl]
    E --> F[import_songcentric_manifest_live.py]
    F --> G[feature_fact]

当前特征补全脚本：acr-engine/scripts/enrich_songcentric_manifest_with_local_features.py

4.8 目录链中的 exact lane 提升

当前 enrich_songcentric_manifest_with_local_features.py 已优先复用仓库内 ChromaprintMatcher 生成 fingerprint；只有失败时才回退到 local_wavehash。

本轮 fresh evidence：

• wav_windows_seen = 5
• matcher_fingerprint_count = 5
• fallback_fingerprint_count = 0

这说明当前目录链里的 exact lane 已经不只是临时 hash，而是优先接上了仓库现有 fingerprint 提取能力。

---

5. 最常用 SQL 样例

5.1 写一首歌

insert into media_entity (entity_type, biz_key, title, artist_name)
values ('song', 'song-10001', 'Song 10001', 'Artist A')
returning entity_id;

5.2 写一个 asset

insert into audio_object (
    object_type, song_id, source_type, storage_uri, storage_scheme,
    checksum, codec, sample_rate, channels, duration_ms
) values (
    'asset', :song_id, 'official', 's3://bucket/song10001/master.wav', 's3',
    'sha256:xxx', 'wav', 44100, 2, 215000
) returning object_id;

5.3 写一个 window

insert into audio_object (
    object_type, song_id, parent_object_id, start_ms, end_ms, duration_ms
) values (
    'window', :song_id, :asset_id, 30000, 35000, 5000
) returning object_id;

5.4 写一条 embedding

insert into feature_fact (
    feature_type, object_id, song_id,
    model_name, model_version, feature_set_name,
    feature_schema_ver, embedding_dim, embedding_uri, vector_table_name
) values (
    'embedding', :window_id, :song_id,
    'mert', 'v1-95m', 'mert_5s_hop2.5_meanpool',
    'v1', 768, 's3://bucket/emb/song10001_win0001.npy', 'audio_embedding_vector_768'
);

5.5 把 asset 挂到 reference 集

insert into set_membership (
    set_type, set_name, member_type, member_id, song_id, priority
) values (
    'reference_set', 'phase1_hot_reference_v1', 'asset', :asset_id, :song_id, 100
);

5.6 从 embedding 回查 song

select ff.feature_id,
       ff.model_name,
       ff.model_version,
       ff.feature_set_name,
       win.object_id  as window_id,
       ast.object_id  as asset_id,
       song.entity_id as song_id,
       song.title,
       song.artist_name
from feature_fact ff
join audio_object win
  on win.object_id = ff.object_id
 and win.object_type = 'window'
join audio_object ast
  on ast.object_id = win.parent_object_id
 and ast.object_type = 'asset'
join media_entity song
  on song.entity_id = ff.song_id
 and song.entity_type = 'song'
where ff.feature_id = :feature_id;

---

6. 当前设计意图

为什么切片和原始音频统一用 audio_object

• 新同学更容易理解
• asset/window 共用大量字段
• 减少专用表数量

为什么模型和特征统一用 feature_fact

• 不再一模型一张表
• 不再 fingerprint 一张表、embedding 一张表后继续扩散
• 更适合未来继续换 MERT / MuQ / 新模型

为什么 reference 集用 set_membership

• song / asset / window / feature 都可以挂集合
• reference / eval / hot 切换统一处理

---

7. 当前最推荐的实现顺序

1. 先建 media_entity
2. 再建 audio_object
3. 再建 feature_fact
4. 最后建 set_membership
5. 先打通 song -> asset -> window -> embedding/fingerprint
6. 再继续补更重的治理能力