142 lines
4.2 KiB
PL/PgSQL
142 lines
4.2 KiB
PL/PgSQL
-- ============ JARVIS DATABASE INITIALIZATION ============
|
|
|
|
-- "jarvis" is already created by the postgres image via POSTGRES_DB;
|
|
-- creating it again here would abort the whole init script.
|
|
CREATE DATABASE n8n;
|
|
|
|
-- Connect to jarvis database
|
|
\c jarvis;
|
|
|
|
-- ============ CORE TABLES ============
|
|
|
|
-- Users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
api_key VARCHAR(255) UNIQUE,
|
|
role VARCHAR(50) DEFAULT 'user',
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Conversations/Chat history
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
title VARCHAR(255),
|
|
context JSONB,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_conversations_user_id ON conversations(user_id);
|
|
|
|
-- Chat messages
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id SERIAL PRIMARY KEY,
|
|
conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role VARCHAR(50) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
tokens_used INTEGER,
|
|
metadata JSONB,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
|
|
CREATE INDEX idx_messages_user_id ON messages(user_id);
|
|
|
|
-- Business automation tasks
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
task_type VARCHAR(100),
|
|
status VARCHAR(50) DEFAULT 'pending',
|
|
priority INTEGER DEFAULT 0,
|
|
data JSONB,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
completed_at TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
|
|
CREATE INDEX idx_tasks_status ON tasks(status);
|
|
|
|
-- Knowledge base documents
|
|
CREATE TABLE IF NOT EXISTS documents (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
document_type VARCHAR(100),
|
|
metadata JSONB,
|
|
embedding_id VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_documents_user_id ON documents(user_id);
|
|
CREATE INDEX idx_documents_embedding_id ON documents(embedding_id);
|
|
|
|
-- Audit log
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
action VARCHAR(255) NOT NULL,
|
|
resource_type VARCHAR(100),
|
|
resource_id INTEGER,
|
|
changes JSONB,
|
|
ip_address INET,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
|
|
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);
|
|
|
|
-- ============ TRIGGERS ============
|
|
|
|
-- Auto-update timestamp function
|
|
CREATE OR REPLACE FUNCTION update_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Apply trigger to tables with updated_at
|
|
CREATE TRIGGER trigger_update_timestamp_users
|
|
BEFORE UPDATE ON users
|
|
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
|
|
|
|
CREATE TRIGGER trigger_update_timestamp_conversations
|
|
BEFORE UPDATE ON conversations
|
|
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
|
|
|
|
CREATE TRIGGER trigger_update_timestamp_tasks
|
|
BEFORE UPDATE ON tasks
|
|
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
|
|
|
|
CREATE TRIGGER trigger_update_timestamp_documents
|
|
BEFORE UPDATE ON documents
|
|
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
|
|
|
|
-- ============ DEFAULT DATA ============
|
|
|
|
-- Insert admin user (password: admin - CHANGE IN PRODUCTION!)
|
|
INSERT INTO users (username, email, password_hash, role)
|
|
VALUES (
|
|
'admin',
|
|
'admin@jarvis.local',
|
|
'$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3djPvga3jaK',
|
|
'admin'
|
|
) ON CONFLICT DO NOTHING;
|
|
|
|
-- Permissions/Roles would go here
|
|
-- Add your own initialization data
|