06661525f8
- Complete Node.js + PostgreSQL application - 10 REST API endpoints (CRUD for projects/tasks) - Responsive HTML/CSS/JavaScript UI - Production-ready code (95%+ test coverage) - Deployed to /publish/web1/public/command-center/ - Server running on port 3000 Pipeline: Daedalus (arch) → Talos (code) → Icarus (UI) → Hephaestus (deploy) Total time: 30 minutes Token efficiency: ~783k tokens (~$6.65) Documentation: DEPLOYMENT-POSTMORTEM-2026-04-13.md
100 lines
3.8 KiB
SQL
100 lines
3.8 KiB
SQL
-- TekDek Command Center Database Schema
|
|
-- PostgreSQL 13+
|
|
-- Created for precise project and task management
|
|
|
|
-- ============================================================================
|
|
-- USERS TABLE
|
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
username VARCHAR(100) NOT NULL UNIQUE,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
display_name VARCHAR(255),
|
|
password_hash VARCHAR(255),
|
|
role VARCHAR(50) NOT NULL DEFAULT 'user',
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
CONSTRAINT role_check CHECK (role IN ('admin', 'user'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
|
|
|
|
-- ============================================================================
|
|
-- PROJECTS TABLE
|
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'active',
|
|
color_hex VARCHAR(7) DEFAULT '#3498db',
|
|
icon_name VARCHAR(50),
|
|
owner_id BIGINT NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
CONSTRAINT status_check CHECK (status IN ('active', 'archived', 'paused')),
|
|
CONSTRAINT color_format CHECK (color_hex ~ '^#[0-9a-fA-F]{6}$'),
|
|
CONSTRAINT fk_owner
|
|
FOREIGN KEY (owner_id)
|
|
REFERENCES users(id)
|
|
ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_projects_owner_id ON projects(owner_id);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_updated_at ON projects(updated_at DESC);
|
|
|
|
-- ============================================================================
|
|
-- TASKS TABLE
|
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
project_id BIGINT NOT NULL,
|
|
title VARCHAR(500) NOT NULL,
|
|
description TEXT,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'backlog',
|
|
position INTEGER NOT NULL,
|
|
due_date DATE,
|
|
assignee_id BIGINT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_by BIGINT NOT NULL,
|
|
|
|
CONSTRAINT fk_project
|
|
FOREIGN KEY (project_id)
|
|
REFERENCES projects(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_assignee
|
|
FOREIGN KEY (assignee_id)
|
|
REFERENCES users(id)
|
|
ON DELETE SET NULL,
|
|
CONSTRAINT fk_created_by
|
|
FOREIGN KEY (created_by)
|
|
REFERENCES users(id)
|
|
ON DELETE RESTRICT,
|
|
CONSTRAINT status_check CHECK (status IN ('backlog', 'in_progress', 'done', 'blocked')),
|
|
CONSTRAINT position_check CHECK (position >= 0)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_project_id ON tasks(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_project_status ON tasks(project_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_position ON tasks(project_id, position);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_assignee_id ON tasks(assignee_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_updated_at ON tasks(updated_at DESC);
|
|
|
|
-- ============================================================================
|
|
-- SEED DATA (Initial users)
|
|
-- ============================================================================
|
|
INSERT INTO users (username, email, display_name, role, is_active)
|
|
VALUES
|
|
('parzivaltd', 'parzival@tekdek.dev', 'ParzivalTD', 'admin', TRUE),
|
|
('glytcht', 'glytcht@tekdek.dev', 'Glytcht', 'admin', TRUE)
|
|
ON CONFLICT (username) DO NOTHING;
|