75 lines
2.2 KiB
MySQL
75 lines
2.2 KiB
MySQL
|
-- workspace-api database schema
|
||
|
|
-- SQLite 3.x compatible
|
||
|
|
|
||
|
|
-- Users table
|
||
|
|
CREATE TABLE IF NOT EXISTS users (
|
||
|
|
id TEXT PRIMARY KEY NOT NULL,
|
||
|
|
email TEXT NOT NULL UNIQUE,
|
||
|
|
password_hash TEXT NOT NULL,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
role TEXT NOT NULL DEFAULT 'user',
|
||
|
|
tenant_id TEXT,
|
||
|
|
created_at TEXT NOT NULL,
|
||
|
|
updated_at TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Checklist items (tasks)
|
||
|
|
CREATE TABLE IF NOT EXISTS checklist_items (
|
||
|
|
id TEXT PRIMARY KEY NOT NULL,
|
||
|
|
project_id TEXT NOT NULL,
|
||
|
|
phase TEXT,
|
||
|
|
task_id TEXT,
|
||
|
|
description TEXT NOT NULL,
|
||
|
|
completed BOOLEAN NOT NULL DEFAULT 0,
|
||
|
|
completed_at TEXT,
|
||
|
|
created_at TEXT NOT NULL,
|
||
|
|
parent_task_id TEXT,
|
||
|
|
assignee_id TEXT,
|
||
|
|
deadline TEXT,
|
||
|
|
priority TEXT,
|
||
|
|
tags TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Task attachments
|
||
|
|
CREATE TABLE IF NOT EXISTS task_attachments (
|
||
|
|
id TEXT PRIMARY KEY NOT NULL,
|
||
|
|
task_id TEXT NOT NULL,
|
||
|
|
filename TEXT NOT NULL,
|
||
|
|
file_size INTEGER NOT NULL,
|
||
|
|
mime_type TEXT NOT NULL,
|
||
|
|
storage_path TEXT NOT NULL,
|
||
|
|
uploaded_by TEXT NOT NULL,
|
||
|
|
uploaded_at TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Task comments
|
||
|
|
CREATE TABLE IF NOT EXISTS task_comments (
|
||
|
|
id TEXT PRIMARY KEY NOT NULL,
|
||
|
|
task_id TEXT NOT NULL,
|
||
|
|
user_id TEXT NOT NULL,
|
||
|
|
content TEXT NOT NULL,
|
||
|
|
created_at TEXT NOT NULL,
|
||
|
|
updated_at TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Filter presets
|
||
|
|
CREATE TABLE IF NOT EXISTS filter_presets (
|
||
|
|
id TEXT PRIMARY KEY NOT NULL,
|
||
|
|
user_id TEXT NOT NULL,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
filters_json TEXT NOT NULL,
|
||
|
|
is_default BOOLEAN NOT NULL DEFAULT 0,
|
||
|
|
created_at TEXT NOT NULL,
|
||
|
|
updated_at TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create indexes for common queries
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_checklist_items_project ON checklist_items(project_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_checklist_items_task ON checklist_items(task_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_checklist_items_parent ON checklist_items(parent_task_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_checklist_items_assignee ON checklist_items(assignee_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_task_attachments_task ON task_attachments(task_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_task_comments_task ON task_comments(task_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_filter_presets_user ON filter_presets(user_id);
|