PostgreSQL Schema Samples / song-centric 4 表 DDL 与样例

更新：2026-06-04
SQL 文件：acr-engine/sql/acr_pg_schema_songcentric_v1.sql

---

1. 一页结论

当前默认物理模型：

media_entity -> audio_object -> feature_fact -> set_membership

当前默认逻辑语义：

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

其中：

• audio_object 统一承载原始音频和切片
• feature_fact 统一承载 exact/semantic 特征
• set_membership 统一承载 reference/eval/hot 集关系

---

2. 切片 / 模型 / feature 落在哪张表

对象	表	关键字段	示例
song	media_entity	entity_type='song'	song_000001
asset	audio_object	object_type='asset'	一首歌的原始 wav/mp3/flac
window	audio_object	object_type='window'	0-5000ms, 2500-7500ms
fingerprint	feature_fact	feature_type='fingerprint'	chromaprint
embedding	feature_fact	feature_type='embedding'	MERT/MuQ/fallback vector
model	feature_fact	model_name, model_version	mert-v1-95m, muq-base, local_wavehash_embed
feature set	feature_fact	feature_set_name, feature_schema_ver	mert_5s_hop2.5_v1

---

3. DDL

3.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)
);

3.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)
        )
);

3.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))
        )
);

3.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()
);

---

4. 典型写入流程图

4.1 表写入顺序

flowchart TD
    A[insert song] --> B[insert asset]
    B --> C[insert windows]
    C --> D1[insert fingerprint facts]
    C --> D2[insert embedding facts]
    A --> E[insert set_membership]
    B --> E
    C --> E

4.2 查询回溯顺序

flowchart LR
    A[query features] --> B[feature_fact]
    B --> C[window]
    C --> D[asset]
    D --> E[song]

---

5. 样例数据

5.1 写 song

insert into media_entity (
    entity_type, biz_key, title, artist_name, metadata_json
) values (
    'song', 'song_000001', 'Song Alpha', 'Artist A',
    '{"source":"catalog_import","language":"zh"}'::jsonb
)
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, metadata_json
) values (
    'asset', :song_id, 'catalog_master',
    's3://bucket/song_alpha/clip1.wav', 's3',
    'sha256:asset001', 'wav', 44100, 2, 183000,
    '{"uploader":"pipeline_v1"}'::jsonb
)
returning object_id;

5.3 写 window

insert into audio_object (
    object_type, song_id, parent_object_id,
    start_ms, end_ms, duration_ms, metadata_json
) values
    ('window', :song_id, :asset_id, 0,    5000, 5000, '{"hop_ms":2500}'::jsonb),
    ('window', :song_id, :asset_id, 2500, 7500, 5000, '{"hop_ms":2500}'::jsonb)
returning object_id;

5.4 写 fingerprint

insert into feature_fact (
    feature_type, object_id, song_id,
    model_name, model_version, feature_set_name, feature_schema_ver,
    fingerprint_value, checksum, metadata_json
) values (
    'fingerprint', :window_id, :song_id,
    'chromaprint', '1.0', 'chromaprint_5s_v1', 'v1',
    'AQAAE0mUaEkSZSo...', 'sha256:fp001',
    '{"lane":"exact"}'::jsonb
);

5.5 写 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, checksum, metadata_json
) values (
    'embedding', :window_id, :song_id,
    'mert-v1-95m', 'hf-main', 'mert_5s_hop2.5_v1', 'v1',
    768, 's3://bucket/embeddings/song_alpha_win0001.npy', 'audio_embedding_vector_768',
    'sha256:emb001', '{"lane":"semantic"}'::jsonb
);

5.6 写 set membership

insert into set_membership (
    set_type, set_name, member_type, member_id, song_id, priority, metadata_json
) values
    ('reference_set', 'phase1_hot_reference_v1', 'song',   :song_id,   :song_id, 100, '{}'::jsonb),
    ('reference_set', 'phase1_hot_reference_v1', 'asset',  :asset_id,  :song_id, 100, '{}'::jsonb),
    ('reference_set', 'phase1_hot_reference_v1', 'window', :window_id, :song_id, 100, '{}'::jsonb);

---

6. 典型查询

6.1 查看某首歌有哪些 asset

select object_id, storage_uri, checksum, duration_ms
from audio_object
where song_id = :song_id
  and object_type = 'asset'
order by object_id;

6.2 查看某个 asset 切了哪些 window

select object_id, start_ms, end_ms, duration_ms
from audio_object
where parent_object_id = :asset_id
  and object_type = 'window'
order by start_ms;

6.3 查看某个 window 被哪些模型编码过

select feature_type, model_name, model_version, feature_set_name, embedding_dim,
       fingerprint_value, embedding_uri, vector_table_name
from feature_fact
where object_id = :window_id
order by feature_type, model_name, model_version;

6.4 从 feature 回查 song

select ff.feature_id,
       ff.feature_type,
       ff.model_name,
       w.object_id as window_id,
       w.start_ms,
       w.end_ms,
       a.object_id as asset_id,
       a.storage_uri,
       s.entity_id as song_id,
       s.title,
       s.artist_name
from feature_fact ff
join audio_object w
  on w.object_id = ff.object_id
 and w.object_type = 'window'
join audio_object a
  on a.object_id = w.parent_object_id
 and a.object_type = 'asset'
join media_entity s
  on s.entity_id = ff.song_id
where ff.feature_id = :feature_id;

6.5 查询 reference set 中的全部 window

select sm.set_name,
       sm.member_id as window_id,
       sm.song_id,
       ao.parent_object_id as asset_id,
       ao.start_ms,
       ao.end_ms
from set_membership sm
join audio_object ao
  on ao.object_id = sm.member_id
 and sm.member_type = 'window'
where sm.set_type = 'reference_set'
  and sm.set_name = 'phase1_hot_reference_v1'
  and sm.is_active = true
order by sm.song_id, ao.start_ms;

---

7. 一个最小存储样例怎么理解

song(Song Alpha)
  -> asset(clip1.wav)
    -> window(0-5000ms)
      -> fingerprint(chromaprint)
      -> embedding(mert-v1-95m)
    -> window(2500-7500ms)
      -> fingerprint(chromaprint)
      -> embedding(mert-v1-95m)

落表后意味着：

• song 在 media_entity
• asset/window 都在 audio_object
• chromaprint/mert 都在 feature_fact
• 它们是否属于 hot reference 在 set_membership

---

8. 设计意图总结

这套结构主要解决：

• 同一 song 下多个音频文件
• 同一 asset 下多个切片窗口
• 同一 window 被多个模型重复编码
• fingerprint / embedding 统一落库
• reference/eval/hot 统一集合治理
• 查询后快速归属到 song_id

---

9. 当前最该关注的后续演进点

1. 保持 4 表主链不变
2. 给 semantic lane 接真实 MERT / MuQ adapter
3. 继续复用 feature_fact.model_name/model_version/feature_set_name 做模型演进
4. 必要时再补更重的 registry / vector table 治理

---

10. 相关文档

• README.md
• start-here.md
• session-handoff.md
• postgresql-data-model.md

---

11. 在线检索回溯样例

11.1 从命中的 feature 回查 song

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

11.2 典型在线查询 SQL

select ff.feature_id,
       ff.feature_type,
       ff.model_name,
       ff.feature_set_name,
       w.object_id as window_id,
       w.start_ms,
       w.end_ms,
       a.object_id as asset_id,
       a.storage_uri,
       s.entity_id as song_id,
       s.title,
       s.artist_name
from feature_fact ff
join audio_object w
  on w.object_id = ff.object_id
 and w.object_type = 'window'
join audio_object a
  on a.object_id = w.parent_object_id
 and a.object_type = 'asset'
join media_entity s
  on s.entity_id = ff.song_id
where ff.feature_id = :feature_id;

11.3 典型 song-level 聚合 SQL

select ff.song_id,
       s.title,
       s.artist_name,
       count(*) as matched_windows
from feature_fact ff
join media_entity s
  on s.entity_id = ff.song_id
where ff.feature_id = any(:matched_feature_ids)
group by ff.song_id, s.title, s.artist_name
order by matched_windows desc
limit 20;