53 lines
1.9 KiB
SQL
53 lines
1.9 KiB
SQL
-- Initial schema for messaging-center
|
|
|
|
CREATE TABLE messages (
|
|
id TEXT PRIMARY KEY,
|
|
source TEXT NOT NULL, -- email, whatsapp, signal, sms, voicemail
|
|
direction TEXT NOT NULL, -- inbound, outbound
|
|
from_contact TEXT NOT NULL, -- JSON: {id, name, type}
|
|
to_contact TEXT NOT NULL, -- JSON: {id, name, type}
|
|
timestamp DATETIME NOT NULL,
|
|
type TEXT NOT NULL, -- text, voice, image, document, email
|
|
subject TEXT,
|
|
body TEXT,
|
|
body_html TEXT,
|
|
status TEXT NOT NULL, -- received, processing, delivered, failed
|
|
raw TEXT, -- original message JSON
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_messages_source ON messages(source);
|
|
CREATE INDEX idx_messages_direction ON messages(direction);
|
|
CREATE INDEX idx_messages_timestamp ON messages(timestamp);
|
|
CREATE INDEX idx_messages_type ON messages(type);
|
|
CREATE INDEX idx_messages_status ON messages(status);
|
|
|
|
CREATE TABLE attachments (
|
|
id TEXT PRIMARY KEY,
|
|
message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
type TEXT NOT NULL, -- mime type
|
|
filename TEXT,
|
|
size INTEGER NOT NULL,
|
|
local_path TEXT,
|
|
transcription TEXT,
|
|
status TEXT NOT NULL -- stored, processing, routed, deleted
|
|
);
|
|
|
|
CREATE INDEX idx_attachments_message_id ON attachments(message_id);
|
|
CREATE INDEX idx_attachments_status ON attachments(status);
|
|
|
|
CREATE TABLE commands (
|
|
id TEXT PRIMARY KEY,
|
|
type TEXT NOT NULL, -- send, route, delete, archive, forward
|
|
payload TEXT, -- JSON
|
|
status TEXT NOT NULL, -- pending, executing, completed, failed
|
|
error TEXT,
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_commands_type ON commands(type);
|
|
CREATE INDEX idx_commands_status ON commands(status);
|
|
CREATE INDEX idx_commands_created_at ON commands(created_at);
|