Database Schema Documentation

Overview

MS-Project uses PostgreSQL as its primary database with GORM as the ORM layer. The schema is designed for scalability, data integrity, and performance with support for soft deletes, audit trails, and flexible attribute storage.

Database Configuration

Database: PostgreSQL 15+
Character Set: UTF8
Collation: en_US.UTF-8
Timezone: UTC
Connection Pool:
  Max Idle: 10
  Max Open: 100
  Lifetime: 1 hour

Core Design Principles

1. ULID for Primary Keys: Sortable, unique identifiers
2. Soft Deletes: All main entities support soft deletion
3. Audit Trail: History tables track all changes
4. EAV Pattern: Flexible attributes for products
5. Indexes: Strategic indexes for query performance
6. Foreign Keys: Referential integrity enforcement

Entity Relationship Diagram

┌──────────────┐        ┌──────────────┐        ┌──────────────┐
│   Projects   │───────<│    Tasks     │───────<│  TaskComments│
└──────────────┘        └──────────────┘        └──────────────┘
       │                       │                        │
       │                       │                        │
       ▼                       ▼                        ▼
┌──────────────┐        ┌──────────────┐        ┌──────────────┐
│   Products   │        │TaskActivities│        │  Attachments │
└──────────────┘        └──────────────┘        └──────────────┘
       │                       │
       │                       │
       ▼                       ▼
┌──────────────┐        ┌──────────────┐
│ProductAttribs│        │ TaskHistory  │
└──────────────┘        └──────────────┘

Table Specifications

1. Base Tables

employees

Stores user/employee information.

Indexes:

CREATE UNIQUE INDEX idx_employees_email ON employees(email) WHERE deleted_at IS NULL;
CREATE INDEX idx_employees_department ON employees(department_id);
CREATE INDEX idx_employees_deleted_at ON employees(deleted_at);

customers

Stores customer/client information.

2. Project Tables

projects

Core project entity.

Indexes:

CREATE UNIQUE INDEX idx_projects_unique_code ON projects(unique_code);
CREATE INDEX idx_projects_status ON projects(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_projects_customer ON projects(customer_id);
CREATE INDEX idx_projects_creator ON projects(creator_id);
CREATE INDEX idx_projects_reply_date ON projects(reply_date) WHERE deleted_at IS NULL;

Enums:

-- ProjectStatusEnum
CREATE TYPE project_status AS ENUM (
    'pending',           -- Initial state
    'order_placed',      -- Order confirmed
    'canceled',          -- Project canceled
    'delivered',         -- Delivered to customer
    'awaiting_payment',  -- Pending payment
    'completed',         -- Fully completed
    'need_update',       -- Requires updates
    'approved'           -- Approved by management
);

-- ProjectProgressEnum
CREATE TYPE project_progress AS ENUM (
    'technical_negotiating',  -- Technical discussion
    'bidding',               -- Bidding phase
    'waiting_for_contract',  -- Contract pending
    'contract_negotiating',  -- Contract negotiation
    'production_planning',   -- Planning production
    'in_production',         -- Active production
    'delivering'             -- Delivery phase
);

project_participants

Many-to-many relationship for project team members.

Indexes:

CREATE UNIQUE INDEX idx_project_participants_unique ON project_participants(project_id, employee_id);
CREATE INDEX idx_project_participants_employee ON project_participants(employee_id);

project_documents

Project file attachments.

3. Task Tables

tasks

Task management entity.

Indexes:

CREATE UNIQUE INDEX idx_tasks_unique_code ON tasks(unique_code);
CREATE INDEX idx_tasks_project ON tasks(project_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_tasks_status ON tasks(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_tasks_priority ON tasks(priority) WHERE deleted_at IS NULL;
CREATE INDEX idx_tasks_department ON tasks(department_id);
CREATE INDEX idx_tasks_parent ON tasks(parent_id);
CREATE INDEX idx_tasks_end_date ON tasks(end_date) WHERE deleted_at IS NULL;
CREATE INDEX idx_tasks_tags ON tasks USING gin(tags);

Enums:

-- TaskStatusEnum
CREATE TYPE task_status AS ENUM (
    'pending',         -- Not started
    'in_progress',     -- Currently working
    'on_hold',         -- Temporarily paused
    'blocked',         -- Blocked by dependency
    'pending_review',  -- Awaiting review
    'completed',       -- Task completed
    'canceled',        -- Task canceled
    'deferred',        -- Postponed
    'archived'         -- Archived
);

-- PriorityEnum
CREATE TYPE priority AS ENUM (
    'low',
    'medium',
    'high',
    'urgent'
);

task_participants

Task assignees and watchers.

Indexes:

CREATE UNIQUE INDEX idx_task_participants_unique ON task_participants(task_id, employee_id);
CREATE INDEX idx_task_participants_employee ON task_participants(employee_id);

task_comments

Task discussion thread.

Indexes:

CREATE INDEX idx_task_comments_task ON task_comments(task_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_task_comments_parent ON task_comments(parent_id);
CREATE INDEX idx_task_comments_actor ON task_comments(actor_id);

task_comment_reactions

User reactions to comments.

Indexes:

CREATE UNIQUE INDEX idx_comment_reactions_unique ON task_comment_reactions(comment_id, user_id);
CREATE INDEX idx_comment_reactions_user ON task_comment_reactions(user_id);

task_activities

Activity log for tasks.

Indexes:

CREATE INDEX idx_task_activities_task ON task_activities(task_id);
CREATE INDEX idx_task_activities_history ON task_activities(history_id);
CREATE INDEX idx_task_activities_comment ON task_activities(comment_id);

task_history

Audit trail for task changes.

Indexes:

CREATE INDEX idx_task_history_task ON task_history(task_id);
CREATE INDEX idx_task_history_actor ON task_history(actor_id);
CREATE INDEX idx_task_history_type ON task_history(type);
CREATE INDEX idx_task_history_created ON task_history(created_at DESC);

4. Product Tables

products

Generic product/deliverable entity.

Note: Legacy manufacturing fields are being phased out in favor of the EAV pattern.

Indexes:

CREATE INDEX idx_products_project ON products(project_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_type ON products(product_type);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_delivery ON products(delivery_date) WHERE deleted_at IS NULL;

product_attributes

EAV storage for flexible product attributes.

Indexes:

CREATE UNIQUE INDEX idx_product_attributes_unique ON product_attributes(product_id, attribute_key);
CREATE INDEX idx_product_attributes_key ON product_attributes(attribute_key);
CREATE INDEX idx_product_attributes_key_value ON product_attributes(attribute_key, attribute_value);

5. Supporting Tables

expenses

Project expense tracking.

Indexes:

CREATE INDEX idx_expenses_project ON expenses(project_id);
CREATE INDEX idx_expenses_category ON expenses(category);
CREATE INDEX idx_expenses_date ON expenses(incurred_date);

notifications

System notifications.

Indexes:

CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_notifications_unread ON notifications(user_id, read) WHERE read = FALSE;
CREATE INDEX idx_notifications_created ON notifications(created_at DESC);

settings

System configuration and schema storage.

Indexes:

CREATE UNIQUE INDEX settings_key_unique ON settings(key);

Key Settings:

• product_schema_registry: JSON registry of available product schemas
• product_schema:software:v1.0.0: JSON Schema for software products
• product_schema:service:v1.0.0: JSON Schema for service products

Migration Strategy

Initial Schema Creation

-- 001_initial_schema.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For text search

-- Create all tables in dependency order
CREATE TABLE employees...
CREATE TABLE customers...
CREATE TABLE projects...
-- etc.

Product Attributes Migration

-- 001_create_product_attributes.sql
CREATE TABLE IF NOT EXISTS product_attributes (
    id TEXT PRIMARY KEY,
    product_id TEXT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    attribute_key TEXT NOT NULL,
    attribute_value TEXT NOT NULL,
    attribute_type TEXT NOT NULL CHECK (attribute_type IN ('string', 'number', 'boolean', 'date', 'json')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 002_migrate_product_data.sql
-- Migrate legacy fields to attributes
INSERT INTO product_attributes (id, product_id, attribute_key, attribute_value, attribute_type)
SELECT
    generate_ulid() as id,
    id as product_id,
    'diameter_millimeter' as attribute_key,
    diameter_millimeter::TEXT as attribute_value,
    'number' as attribute_type
FROM products
WHERE diameter_millimeter IS NOT NULL;
-- Repeat for other fields...

Product Schema System

-- 003_insert_product_schemas.sql
-- Insert product schema registry
INSERT INTO settings (key, value, "isPublic") VALUES
('product_schema_registry', '{
  "version": "1.0.0",
  "schemas": [
    {"type": "software", "key": "product_schema:software:v1.0.0", "active": true},
    {"type": "service", "key": "product_schema:service:v1.0.0", "active": true}
  ]
}', true);

-- Insert individual schemas (JSON Schema Draft-07)
INSERT INTO settings (key, value, "isPublic") VALUES
('product_schema:software:v1.0.0', '{"$schema": "http://json-schema.org/draft-07/schema#", ...}', false),
('product_schema:service:v1.0.0', '{"$schema": "http://json-schema.org/draft-07/schema#", ...}', false);

Dynamic Product Type System:

• Product types are dynamically loaded from settings table
• JSON Schema validation for product attributes
• No hardcoded product types in application code
• Add new product types by inserting into settings table

Performance Optimization

Query Optimization Examples

1. Project List with Counts

-- Efficient query with CTEs
WITH project_tasks AS (
    SELECT project_id, COUNT(*) as task_count
    FROM tasks
    WHERE deleted_at IS NULL
    GROUP BY project_id
),
project_products AS (
    SELECT project_id, COUNT(*) as product_count
    FROM products
    WHERE deleted_at IS NULL
    GROUP BY project_id
)
SELECT
    p.*,
    COALESCE(pt.task_count, 0) as task_count,
    COALESCE(pp.product_count, 0) as product_count
FROM projects p
LEFT JOIN project_tasks pt ON p.id = pt.project_id
LEFT JOIN project_products pp ON p.id = pp.project_id
WHERE p.deleted_at IS NULL
ORDER BY p.created_at DESC
LIMIT 10;

2. Task Search with Full-Text

-- Full-text search setup
CREATE INDEX idx_tasks_search ON tasks
USING gin(to_tsvector('english', name || ' ' || COALESCE(description, '')));

-- Search query
SELECT * FROM tasks
WHERE to_tsvector('english', name || ' ' || COALESCE(description, ''))
      @@ plainto_tsquery('english', 'authentication bug')
  AND deleted_at IS NULL
  AND project_id = 'project123'
ORDER BY ts_rank(to_tsvector('english', name || ' ' || COALESCE(description, '')),
                 plainto_tsquery('english', 'authentication bug')) DESC;

Maintenance Scripts

Vacuum and Analyze

-- Regular maintenance (daily)
VACUUM ANALYZE projects;
VACUUM ANALYZE tasks;
VACUUM ANALYZE products;
VACUUM ANALYZE product_attributes;

-- Full vacuum (weekly)
VACUUM FULL ANALYZE;

Index Maintenance

-- Check index usage
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan;

-- Rebuild bloated indexes
REINDEX INDEX CONCURRENTLY idx_tasks_project;

Backup and Recovery

Backup Strategy

# Daily backup with compression
pg_dump -h localhost -U postgres -d ms_project \
  --format=custom --compress=9 \
  --file="backup_$(date +%Y%m%d).dump"

# Point-in-time recovery setup
archive_mode = on
archive_command = 'cp %p /backup/archive/%f'
wal_level = replica

Recovery Procedures

# Restore from backup
pg_restore -h localhost -U postgres -d ms_project_restore \
  --clean --if-exists --verbose backup_20240101.dump

# Point-in-time recovery
recovery_target_time = '2024-01-01 12:00:00'
recovery_target_action = 'promote'

Security Considerations

Row-Level Security

-- Enable RLS for multi-tenant scenarios
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Policy for project access
CREATE POLICY project_access ON projects
    FOR ALL
    USING (
        creator_id = current_user_id() OR
        id IN (
            SELECT project_id FROM project_participants
            WHERE employee_id = current_user_id()
        )
    );

Encryption

-- Encrypt sensitive columns
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Example: Encrypt customer contact info
UPDATE customers
SET contact_email = pgp_sym_encrypt(contact_email, 'encryption_key')
WHERE contact_email IS NOT NULL;

Monitoring Queries

Database Health

-- Connection count
SELECT count(*) FROM pg_stat_activity;

-- Long-running queries
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 4) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Conclusion

This database schema provides:

• Flexibility: EAV pattern for dynamic attributes
• Integrity: Foreign keys and constraints
• Performance: Strategic indexing and optimization
• Auditability: Complete history tracking
• Scalability: Designed for growth

The schema supports all business requirements while maintaining data consistency and query performance.