schema.sql

-- D1 Database Schema for Knowledge Assistant

-- Conversation history table
CREATE TABLE IF NOT EXISTS conversations (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('user', 'assistant')),
    content TEXT NOT NULL,
    source TEXT NOT NULL CHECK(source IN ('chat', 'voice')),
    timestamp INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_conversations_user_id ON conversations(user_id);
CREATE INDEX idx_conversations_timestamp ON conversations(timestamp);

-- User preferences table
CREATE TABLE IF NOT EXISTS user_preferences (
    user_id TEXT PRIMARY KEY,
    timezone TEXT DEFAULT 'UTC',
    summary_frequency TEXT DEFAULT 'daily' CHECK(summary_frequency IN ('daily', 'weekly', 'manual')),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Knowledge metadata table (additional metadata beyond Vectorize)
CREATE TABLE IF NOT EXISTS knowledge_metadata (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    category TEXT,
    tags TEXT, -- JSON array stored as text
    source TEXT NOT NULL CHECK(source IN ('chat', 'voice')),
    timestamp INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_knowledge_user_id ON knowledge_metadata(user_id);
CREATE INDEX idx_knowledge_category ON knowledge_metadata(category);

-- Summaries table
CREATE TABLE IF NOT EXISTS summaries (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    content TEXT NOT NULL,
    time_range TEXT,
    timestamp INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_summaries_user_id ON summaries(user_id);
CREATE INDEX idx_summaries_timestamp ON summaries(timestamp);