File size: 1,860 Bytes
86be40b b2b6341 86be40b b2b6341 86be40b b2b6341 86be40b b2b6341 86be40b b2b6341 86be40b b2b6341 86be40b | 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 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | -- Schema for MultiSource-RAG
-- (Database creation is handled by the cloud provider)
CREATE TABLE IF NOT EXISTS sources (
id VARCHAR(36) PRIMARY KEY,
type VARCHAR(20) NOT NULL,
title VARCHAR(500) NOT NULL,
origin TEXT,
language VARCHAR(10) DEFAULT 'EN',
status VARCHAR(20) DEFAULT 'pending',
chunk_count INT DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS chunks (
id VARCHAR(36) PRIMARY KEY,
source_id VARCHAR(36),
chunk_index INT,
chunk_text TEXT,
chunk_type VARCHAR(20) DEFAULT 'text',
page_number INT DEFAULT NULL,
timestamp_s INT DEFAULT NULL,
unified_metadata JSON DEFAULT NULL,
url_ref TEXT DEFAULT NULL,
FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS chat_history (
id VARCHAR(36) PRIMARY KEY,
question TEXT NOT NULL,
answer TEXT NOT NULL,
sources_used JSON DEFAULT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
conversation_id VARCHAR(36) DEFAULT NULL
);
CREATE TABLE IF NOT EXISTS conversations (
id VARCHAR(36) PRIMARY KEY,
title VARCHAR(500) DEFAULT 'New Chat',
conv_type VARCHAR(20) DEFAULT 'general',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS image_jobs (
image_id VARCHAR(36) PRIMARY KEY,
source_id VARCHAR(36) NULL,
image_data LONGBLOB,
caption TEXT,
status VARCHAR(20) DEFAULT 'pending',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Indexes for performance (Standard syntax)
CREATE INDEX idx_chunks_source ON chunks(source_id);
CREATE INDEX idx_chunks_type ON chunks(chunk_type);
CREATE INDEX idx_sources_type ON sources(type);
CREATE INDEX idx_chat_created ON chat_history(created_at); |