Create Tables and Load Sample Data

Create the Database

CREATE DATABASE task_management;
\c task_management

Complete Schema Script

Save this as 01-schema.sql:

-- Users table
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    avatar_url VARCHAR(500),
    bio TEXT,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_login_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,
    CONSTRAINT chk_users_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')
);

-- Workspaces table
CREATE TABLE workspaces (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    owner_id BIGINT NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- Workspace members table
CREATE TABLE workspace_members (
    workspace_id BIGINT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(20) DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (workspace_id, user_id)
);

-- Projects table
CREATE TABLE projects (
    id BIGSERIAL PRIMARY KEY,
    workspace_id BIGINT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'archived', 'completed')),
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- Tasks table
CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'done', 'cancelled')),
    priority VARCHAR(10) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high')),
    due_date DATE,
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ
);

-- Task assignments table
CREATE TABLE task_assignments (
    task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    assigned_at TIMESTAMPTZ DEFAULT NOW(),
    assigned_by BIGINT REFERENCES users(id),
    PRIMARY KEY (task_id, user_id)
);

-- Tags table
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    workspace_id BIGINT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name VARCHAR(50) NOT NULL,
    color VARCHAR(7) DEFAULT '#808080',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (workspace_id, name)
);

-- Task tags table
CREATE TABLE task_tags (
    task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (task_id, tag_id)
);

-- Comments table
CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- Activity log table
CREATE TABLE activity_log (
    id BIGSERIAL PRIMARY KEY,
    workspace_id BIGINT REFERENCES workspaces(id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(id),
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50),
    entity_id BIGINT,
    details JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Run: psql -d task_management -f 01-schema.sql

Sample Data Script

Save this as 03-sample-data.sql:

-- Insert users
INSERT INTO users (email, password_hash, first_name, last_name, bio) VALUES
('alice@example.com', '$2b$10$...hash...', 'Alice', 'Smith', 'Project Manager'),
('bob@example.com', '$2b$10$...hash...', 'Bob', 'Jones', 'Developer'),
('carol@example.com', '$2b$10$...hash...', 'Carol', 'White', 'Designer'),
('dave@example.com', '$2b$10$...hash...', 'Dave', 'Wilson', 'QA Engineer');

-- Insert workspaces
INSERT INTO workspaces (name, description, owner_id) VALUES
('Acme Corp', 'Main company workspace', 1),
('Side Project', 'Weekend project workspace', 2);

-- Insert workspace members
INSERT INTO workspace_members (workspace_id, user_id, role) VALUES
(1, 1, 'owner'),
(1, 2, 'admin'),
(1, 3, 'member'),
(1, 4, 'member'),
(2, 2, 'owner'),
(2, 3, 'member');

-- Insert projects
INSERT INTO projects (workspace_id, name, description, created_by) VALUES
(1, 'Website Redesign', 'Complete website overhaul', 1),
(1, 'Mobile App', 'iOS and Android apps', 1),
(2, 'Personal Blog', 'Build a personal blog', 2);

-- Insert tasks
INSERT INTO tasks (project_id, title, description, status, priority, due_date, created_by) VALUES
(1, 'Design homepage mockup', 'Create Figma design', 'done', 'high', '2024-12-15', 1),
(1, 'Implement responsive layout', 'CSS Grid and Flexbox', 'in_progress', 'high', '2024-12-20', 1),
(1, 'Write content for About page', 'Company history and team', 'todo', 'medium', '2024-12-25', 1),
(2, 'Set up React Native project', 'Initialize with Expo', 'done', 'high', '2024-12-10', 2),
(2, 'Design app navigation', 'Bottom tabs and stack navigator', 'in_progress', 'medium', '2024-12-18', 2),
(3, 'Choose blogging platform', 'Research options', 'todo', 'low', '2025-01-05', 2);

-- Insert task assignments
INSERT INTO task_assignments (task_id, user_id, assigned_by) VALUES
(1, 3, 1),  -- Carol assigned to design
(2, 2, 1),  -- Bob assigned to implementation
(3, 1, 1),  -- Alice assigned to content
(4, 2, 2),  -- Bob assigned to setup
(5, 3, 2);  -- Carol assigned to design

-- Insert tags
INSERT INTO tags (workspace_id, name, color) VALUES
(1, 'frontend', '#3B82F6'),
(1, 'backend', '#10B981'),
(1, 'design', '#F59E0B'),
(1, 'urgent', '#EF4444'),
(2, 'research', '#8B5CF6');

-- Insert task tags
INSERT INTO task_tags (task_id, tag_id) VALUES
(1, 3),  -- Design tag
(2, 1),  -- Frontend tag
(3, 1),  -- Frontend tag
(4, 2),  -- Backend tag
(5, 3);  -- Design tag

-- Insert comments
INSERT INTO comments (task_id, user_id, content) VALUES
(1, 1, 'Great work on the mockup! Looks amazing.'),
(1, 3, 'Thanks! Let me know if you want any changes.'),
(2, 2, 'Working on mobile-first approach.'),
(2, 1, 'Perfect, that''s the right strategy.');

-- Insert activity log
INSERT INTO activity_log (workspace_id, user_id, action, entity_type, entity_id) VALUES
(1, 1, 'created_project', 'project', 1),
(1, 1, 'created_task', 'task', 1),
(1, 3, 'completed_task', 'task', 1),
(1, 2, 'commented', 'task', 2);

Run: psql -d task_management -f 03-sample-data.sql

Verify Data Loaded

-- Check row counts
SELECT 'users' AS table_name, COUNT(*) FROM users
UNION ALL SELECT 'workspaces', COUNT(*) FROM workspaces
UNION ALL SELECT 'projects', COUNT(*) FROM projects
UNION ALL SELECT 'tasks', COUNT(*) FROM tasks;

-- View sample tasks
SELECT t.id, t.title, t.status, u.first_name || ' ' || u.last_name AS assigned_to
FROM tasks t
LEFT JOIN task_assignments ta ON t.id = ta.task_id
LEFT JOIN users u ON ta.user_id = u.id;

Next Steps

In Lesson 8.4, you'll write business queries to answer real-world questions using this data!