Spaces:
Sleeping
Sleeping
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);
|