F-7 — Your First Real Application Schema

From Concepts to Real Design

A schema for a real application starts with questions, not SQL:

1. What are the entities? The "things" your application manages — users, tasks, projects, comments
2. What attributes does each entity have? The data that describes each thing
3. What are the relationships? How entities relate — a task belongs to a project, a user can have many tasks

This process is called data modelling. You do not need formal methodology — just answer these three questions before writing any SQL.

Example: A Task Management Application

Entities:

• Users (who uses the app)
• Projects (containers for tasks)
• Tasks (the work items)
• Comments (discussion on tasks)

Attributes:

• User: username, email, password hash, created date
• Project: name, description, owner user, created date
• Task: title, description, status, assignee, project, due date, created date
• Comment: content, author, task, created date

Relationships:

• A user owns zero or more projects (one-to-many)
• A project has zero or more tasks (one-to-many)
• A task belongs to exactly one project (many-to-one)
• A task can be assigned to zero or one user (optional many-to-one)
• A task has zero or more comments (one-to-many)
• A user can be a member of multiple projects, and a project has multiple members (many-to-many)

The Complete Schema

sql
CREATE DATABASE taskmanager;
\c taskmanager

-- Users of the application
CREATE TABLE users (
  id            BIGSERIAL     PRIMARY KEY,
  username      TEXT          NOT NULL UNIQUE,
  email         TEXT          NOT NULL UNIQUE,
  password_hash TEXT          NOT NULL,       -- never store plain passwords
  display_name  TEXT,
  created_at    TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  last_login_at TIMESTAMPTZ
);

-- Projects group tasks together
CREATE TABLE projects (
  id          BIGSERIAL   PRIMARY KEY,
  name        TEXT        NOT NULL,
  description TEXT,
  owner_id    BIGINT      NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  -- Restrict: can't delete a user who owns projects
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  archived_at TIMESTAMPTZ           -- NULL = active, non-NULL = archived
);

-- Many-to-many: project membership (who can see and work in a project)
CREATE TABLE project_members (
  project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  user_id    BIGINT NOT NULL REFERENCES users(id)    ON DELETE CASCADE,
  role       TEXT   NOT NULL DEFAULT 'member'
             CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  joined_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (project_id, user_id)  -- a user can only be in a project once
);

-- Tasks are the work items
CREATE TABLE tasks (
  id           BIGSERIAL   PRIMARY KEY,
  project_id   BIGINT      NOT NULL REFERENCES projects(id)  ON DELETE CASCADE,
  -- If a project is deleted, all its tasks are deleted too
  created_by   BIGINT      NOT NULL REFERENCES users(id)     ON DELETE RESTRICT,
  assigned_to  BIGINT               REFERENCES users(id)     ON DELETE SET NULL,
  -- assigned_to is optional; if the assignee is deleted, set to NULL
  title        TEXT        NOT NULL,
  description  TEXT,
  status       TEXT        NOT NULL DEFAULT 'todo'
               CHECK (status IN ('todo', 'in_progress', 'done', 'cancelled')),
  priority     INTEGER     NOT NULL DEFAULT 2
               CHECK (priority BETWEEN 1 AND 5),  -- 1=lowest, 5=highest
  due_date     DATE,
  completed_at TIMESTAMPTZ,          -- NULL until task is marked done
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Comments on tasks
CREATE TABLE comments (
  id         BIGSERIAL   PRIMARY KEY,
  task_id    BIGINT      NOT NULL REFERENCES tasks(id)  ON DELETE CASCADE,
  author_id  BIGINT      NOT NULL REFERENCES users(id)  ON DELETE RESTRICT,
  content    TEXT        NOT NULL CHECK (LENGTH(TRIM(content)) > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  edited_at  TIMESTAMPTZ           -- NULL until edited
);

Verifying the Schema

sql
-- List all tables
\dt

-- Check the structure of each table
\d users
\d projects
\d tasks
\d comments
\d project_members

-- See all foreign key relationships
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name  AS foreign_table,
  ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name;

Seeding Test Data

sql
-- Add users
INSERT INTO users (username, email, password_hash, display_name) VALUES
  ('alice',   'alice@example.com',  'hash1', 'Alice Johnson'),
  ('bob',     'bob@example.com',    'hash2', 'Bob Smith'),
  ('carol',   'carol@example.com',  'hash3', 'Carol Davis');

-- Add a project
INSERT INTO projects (name, description, owner_id) VALUES
  ('Website Redesign', 'Redesign the company website', 1);
-- Project id = 1

-- Add project members
INSERT INTO project_members (project_id, user_id, role) VALUES
  (1, 1, 'owner'),   -- Alice is owner
  (1, 2, 'member'),  -- Bob is a member
  (1, 3, 'viewer');  -- Carol can view

-- Add tasks
INSERT INTO tasks (project_id, created_by, assigned_to, title, status, priority) VALUES
  (1, 1, 2, 'Design homepage mockup', 'in_progress', 4),
  (1, 1, 1, 'Set up CI/CD pipeline',  'todo',        3),
  (1, 2, 3, 'Write content copy',     'todo',        2),
  (1, 1, 2, 'Review SEO strategy',    'done',        2);

-- Add a comment
INSERT INTO comments (task_id, author_id, content) VALUES
  (1, 2, 'I will have the mockup ready by Friday.');

Querying the Schema

sql
-- All tasks in the project with assignee names
SELECT
  t.title,
  t.status,
  t.priority,
  u.display_name AS assigned_to
FROM tasks t
LEFT JOIN users u ON t.assigned_to = u.id
WHERE t.project_id = 1
ORDER BY t.priority DESC, t.created_at;

-- Project members and their roles
SELECT
  u.display_name,
  u.email,
  pm.role,
  pm.joined_at
FROM project_members pm
JOIN users u ON pm.user_id = u.id
WHERE pm.project_id = 1;

-- Tasks with comment count
SELECT
  t.title,
  t.status,
  COUNT(c.id) AS comment_count
FROM tasks t
LEFT JOIN comments c ON t.id = c.task_id
WHERE t.project_id = 1
GROUP BY t.id, t.title, t.status
ORDER BY t.created_at;

-- Summary: tasks per status in this project
SELECT status, COUNT(*) AS count
FROM tasks
WHERE project_id = 1
GROUP BY status;

Connecting From Application Code

PostgreSQL stores your data — but your application needs to connect to it. Here is the minimal setup for the two most common stacks.

Node.js with pg

bash
npm install pg

javascript
// db.js
import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  // Or explicitly:
  // host: 'localhost',
  // port: 5432,
  // database: 'taskmanager',
  // user: 'postgres',
  // password: 'yourpassword',
  max: 10,                // connection pool size
  idleTimeoutMillis: 30000,
});

export async function query(text, params) {
  const result = await pool.query(text, params);
  return result;
}

javascript
// Using it in your application
import { query } from './db.js';

// Get all tasks for a project
const result = await query(
  'SELECT * FROM tasks WHERE project_id = $1 ORDER BY created_at',
  [projectId]
);
const tasks = result.rows;

// Insert a new task (use parameterised queries — never string concatenation)
const newTask = await query(
  `INSERT INTO tasks (project_id, created_by, title, status, priority)
   VALUES ($1, $2, $3, $4, $5)
   RETURNING *`,
  [projectId, userId, title, 'todo', 3]
);
const task = newTask.rows[0];

Never build SQL by string concatenation with user input:

javascript
// ❌ SQL INJECTION VULNERABILITY — never do this
const result = await query(
  `SELECT * FROM users WHERE email = '${userInputEmail}'`
);
// An attacker can input: ' OR '1'='1 and read all users

// ✅ ALWAYS use parameterised queries
const result = await query(
  'SELECT * FROM users WHERE email = $1',
  [userInputEmail]
);
// $1 is a placeholder — user input is always treated as data, never as SQL

Python with psycopg2

bash
pip install psycopg2-binary

python
import psycopg2
import psycopg2.extras

conn = psycopg2.connect(
    host="localhost",
    database="taskmanager",
    user="postgres",
    password="yourpassword"
)
conn.autocommit = True  # or manage transactions manually

with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
    # Parameterised query with %s placeholders
    cur.execute(
        "SELECT * FROM tasks WHERE project_id = %s ORDER BY created_at",
        (project_id,)
    )
    tasks = cur.fetchall()  # list of dict-like rows

Schema Migrations: Managing Change

Schemas change. You add columns, rename things, add constraints. In development, you can DROP DATABASE and start over. In production, you cannot — there is live data there.

Schema migrations are versioned SQL scripts that transform the schema from one state to another.

Manual Migrations (the concept)

sql
-- migrations/001_initial_schema.sql
-- Create the initial schema (the tables we defined above)

-- migrations/002_add_task_labels.sql
CREATE TABLE labels (
  id      BIGSERIAL PRIMARY KEY,
  name    TEXT      NOT NULL UNIQUE,
  color   TEXT      NOT NULL DEFAULT '#888888'
);

CREATE TABLE task_labels (
  task_id  BIGINT NOT NULL REFERENCES tasks(id)  ON DELETE CASCADE,
  label_id BIGINT NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, label_id)
);

-- migrations/003_add_task_position.sql
ALTER TABLE tasks ADD COLUMN position INTEGER NOT NULL DEFAULT 0;
CREATE INDEX idx_tasks_project_position ON tasks (project_id, position);

Migration Tools

In practice, teams use migration tools that:

• Track which migrations have been applied
• Run new migrations automatically on deploy
• Allow rollback if something goes wrong

Popular options by stack:

For a beginner Node.js project, node-pg-migrate is simple:

bash
npm install node-pg-migrate

bash
# Create a new migration
node-pg-migrate create add-task-labels

# Run all pending migrations
node-pg-migrate up

# Roll back the last migration
node-pg-migrate down

pg_dump — Your Safety Net

Before any schema change on a real database, take a backup:

bash
# Backup the entire database
pg_dump taskmanager > backup_$(date +%Y%m%d_%H%M%S).sql

# Restore from a backup
psql taskmanager < backup_20260517_103045.sql

# Backup with compression (for large databases)
pg_dump -Fc taskmanager > backup.dump
pg_restore -d taskmanager backup.dump

Get in the habit of pg_dump before anything that modifies schema structure.

What Phase 1 Has Given You

By completing Modules F-1 through F-7, you can:

✅ Install PostgreSQL and use psql confidently
✅ Design a schema from a real-world problem
✅ Use correct data types for every situation
✅ Write every fundamental SQL operation
✅ Filter, sort, and aggregate data
✅ Join data across multiple tables
✅ Enforce data integrity with constraints
✅ Connect from application code safely (parameterised queries)
✅ Understand schema migrations conceptually

This is the foundation that every PostgreSQL engineer builds on. Phase 2 — The Practitioner takes you from isolated scripts to real production application patterns: advanced SQL, indexing, transactions, JSON, full-text search, access control, and deployment.

Summary

Phase 1 is complete. Phase 2 — The Practitioner — begins with Module P-1: Advanced SQL, covering CTEs, window functions, upserts, and the patterns every production engineer uses weekly.

Next: P-1 — Advanced SQL — The Patterns You Will Use Every Week →