Database Schema

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  last_login_at TIMESTAMP WITH TIME ZONE,
  is_active BOOLEAN DEFAULT true,
  CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
 
-- Indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = true;

-- Create a new user
INSERT INTO users (email, password_hash, full_name)
VALUES ('user@example.com', '$2b$10$...', 'John Doe')
RETURNING id, email, created_at;
 
-- Find user by email
SELECT id, email, full_name, created_at, last_login_at
FROM users
WHERE email = 'user@example.com' AND is_active = true;
 
-- Update last login
UPDATE users
SET last_login_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE id = '123e4567-e89b-12d3-a456-426614174000';

CREATE TABLE articles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(500) NOT NULL,
  slug VARCHAR(500) NOT NULL UNIQUE,
  content TEXT,
  excerpt TEXT,
  author_id UUID NOT NULL,
  status VARCHAR(50) DEFAULT 'draft',
  published_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  view_count INTEGER DEFAULT 0,
  CONSTRAINT fk_author FOREIGN KEY (author_id) 
    REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT valid_status CHECK (status IN ('draft', 'published', 'archived'))
);
 
-- Indexes
CREATE INDEX idx_articles_author_id ON articles(author_id);
CREATE INDEX idx_articles_status ON articles(status);
CREATE INDEX idx_articles_published_at ON articles(published_at) WHERE status = 'published';
CREATE INDEX idx_articles_slug ON articles(slug);
CREATE INDEX idx_articles_full_text ON articles USING gin(to_tsvector('english', title || ' ' || content));

-- Create a new article
INSERT INTO articles (title, slug, content, author_id, status)
VALUES (
  'Getting Started with PostgreSQL',
  'getting-started-postgresql',
  'PostgreSQL is a powerful open-source database...',
  '123e4567-e89b-12d3-a456-426614174000',
  'draft'
)
RETURNING id, title, created_at;
 
-- Publish an article
UPDATE articles
SET status = 'published',
    published_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE id = '987fcdeb-51a2-43f7-9abc-123456789012';
 
-- Get published articles by author with user info
SELECT 
  a.id,
  a.title,
  a.slug,
  a.excerpt,
  a.published_at,
  a.view_count,
  u.full_name AS author_name,
  u.email AS author_email
FROM articles a
INNER JOIN users u ON a.author_id = u.id
WHERE a.author_id = '123e4567-e89b-12d3-a456-426614174000'
  AND a.status = 'published'
ORDER BY a.published_at DESC
LIMIT 10;
 
-- Full-text search
SELECT id, title, excerpt,
       ts_rank(to_tsvector('english', title || ' ' || content), 
               plainto_tsquery('english', 'database schema')) AS rank
FROM articles
WHERE status = 'published'
  AND to_tsvector('english', title || ' ' || content) @@ plainto_tsquery('english', 'database schema')
ORDER BY rank DESC
LIMIT 20;

CREATE TABLE folders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  parent_id UUID,
  owner_id UUID NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  position INTEGER DEFAULT 0,
  CONSTRAINT fk_parent FOREIGN KEY (parent_id) 
    REFERENCES folders(id) ON DELETE CASCADE,
  CONSTRAINT fk_owner FOREIGN KEY (owner_id) 
    REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT no_self_reference CHECK (id != parent_id)
);
 
-- Indexes
CREATE INDEX idx_folders_parent_id ON folders(parent_id);
CREATE INDEX idx_folders_owner_id ON folders(owner_id);
CREATE INDEX idx_folders_position ON folders(position);

-- Create a root folder
INSERT INTO folders (name, owner_id, parent_id)
VALUES ('Documents', '123e4567-e89b-12d3-a456-426614174000', NULL)
RETURNING id, name, created_at;
 
-- Create a subfolder
INSERT INTO folders (name, owner_id, parent_id)
VALUES (
  'Work Projects',
  '123e4567-e89b-12d3-a456-426614174000',
  'abc12345-6789-0def-ghij-klmnopqrstuv'
)
RETURNING id, name, parent_id;
 
-- Get folder hierarchy (recursive query)
WITH RECURSIVE folder_tree AS (
  -- Base case: root folders
  SELECT id, name, parent_id, owner_id, 0 AS depth, 
         ARRAY[name] AS path
  FROM folders
  WHERE parent_id IS NULL
    AND owner_id = '123e4567-e89b-12d3-a456-426614174000'
  
  UNION ALL
  
  -- Recursive case: child folders
  SELECT f.id, f.name, f.parent_id, f.owner_id, ft.depth + 1,
         ft.path || f.name
  FROM folders f
  INNER JOIN folder_tree ft ON f.parent_id = ft.id
)
SELECT id, name, parent_id, depth, 
       array_to_string(path, ' > ') AS full_path
FROM folder_tree
ORDER BY path;
 
-- Move folder to new parent
UPDATE folders
SET parent_id = 'new-parent-uuid',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 'folder-to-move-uuid';
 
-- Get all descendants of a folder
WITH RECURSIVE descendants AS (
  SELECT id, name, parent_id
  FROM folders
  WHERE id = 'parent-folder-uuid'
  
  UNION ALL
  
  SELECT f.id, f.name, f.parent_id
  FROM folders f
  INNER JOIN descendants d ON f.parent_id = d.id
)
SELECT * FROM descendants;