File size: 1,429 Bytes
a8b0be6
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Reproducible schema mirroring the SQLAlchemy ORM (app/models/*).
-- Applied automatically by app.core.db.init_db() on startup; kept here for
-- manual setup / inspection. bge-small-en-v1.5 → 384-dim vectors.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS videos (
    video_id       VARCHAR(1) PRIMARY KEY,        -- 'A' (YouTube) | 'B' (Instagram)
    source         VARCHAR(16) NOT NULL,
    url            TEXT NOT NULL,
    creator        TEXT,
    follower_count BIGINT,
    views          BIGINT,
    likes          BIGINT,
    comments       BIGINT,
    hashtags       TEXT[] DEFAULT '{}',
    upload_date    DATE,
    duration       DOUBLE PRECISION,             -- seconds
    title          TEXT,
    caption        TEXT,
    created_at     TIMESTAMPTZ DEFAULT now()
    -- engagement_rate is derived in code, never stored (FR-5, NFR-1).
);

CREATE TABLE IF NOT EXISTS chunks (
    chunk_id  SERIAL PRIMARY KEY,
    video_id  VARCHAR(1) NOT NULL REFERENCES videos(video_id) ON DELETE CASCADE,
    modality  VARCHAR(16) NOT NULL DEFAULT 'transcript',  -- transcript | hook | ocr | visual
    text      TEXT NOT NULL,
    start     DOUBLE PRECISION,
    "end"     DOUBLE PRECISION,
    embedding vector(384) NOT NULL
);

CREATE INDEX IF NOT EXISTS chunks_video_id_idx ON chunks (video_id);
CREATE INDEX IF NOT EXISTS chunks_embedding_hnsw
    ON chunks USING hnsw (embedding vector_cosine_ops);