# Database Relationships & Entity Details

## 🗄️ **Entity Relationship Overview**

### **Core Entities**

#### **1. CLIENTS**
```sql
CREATE TABLE clients (
    id UUID PRIMARY KEY,
    name VARCHAR NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

#### **2. USER_LEVELS**
```sql
CREATE TABLE user_levels (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    alias_name VARCHAR NOT NULL,
    level_number INTEGER NOT NULL,  -- 1=Highest, 2=Mid, 3=Lowest
    parent_level_id INTEGER REFERENCES user_levels(id),
    province_id INTEGER,
    group VARCHAR,
    is_approval_active BOOLEAN DEFAULT false,
    client_id UUID REFERENCES clients(id),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

#### **3. USERS**
```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    email VARCHAR UNIQUE NOT NULL,
    user_level_id INTEGER REFERENCES user_levels(id),
    client_id UUID REFERENCES clients(id),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

### **Workflow Configuration Entities**

#### **4. APPROVAL_WORKFLOWS**
```sql
CREATE TABLE approval_workflows (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    description TEXT,
    is_default BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    client_id UUID REFERENCES clients(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

#### **5. APPROVAL_WORKFLOW_STEPS**
```sql
CREATE TABLE approval_workflow_steps (
    id SERIAL PRIMARY KEY,
    workflow_id INTEGER REFERENCES approval_workflows(id) ON DELETE CASCADE,
    step_order INTEGER NOT NULL,  -- 1, 2, 3, etc.
    step_name VARCHAR NOT NULL,
    required_user_level_id INTEGER REFERENCES user_levels(id),
    is_required BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

#### **6. CLIENT_APPROVAL_SETTINGS**
```sql
CREATE TABLE client_approval_settings (
    id SERIAL PRIMARY KEY,
    client_id UUID REFERENCES clients(id),
    workflow_id INTEGER REFERENCES approval_workflows(id),
    auto_approve_levels JSONB,  -- Array of level numbers
    notification_settings JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

### **Article & Approval Execution Entities**

#### **7. ARTICLES**
```sql
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR NOT NULL,
    slug VARCHAR UNIQUE,
    description TEXT,
    html_description TEXT,
    workflow_id INTEGER REFERENCES approval_workflows(id),
    current_approval_step INTEGER,
    status_id INTEGER,  -- 1=pending, 2=approved, 3=rejected
    is_publish BOOLEAN DEFAULT false,
    published_at TIMESTAMP,
    created_by_id INTEGER REFERENCES users(id),
    client_id UUID REFERENCES clients(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

#### **8. ARTICLE_APPROVAL_FLOWS**
```sql
CREATE TABLE article_approval_flows (
    id SERIAL PRIMARY KEY,
    article_id INTEGER REFERENCES articles(id) ON DELETE CASCADE,
    workflow_id INTEGER REFERENCES approval_workflows(id),
    current_step INTEGER DEFAULT 1,
    status_id INTEGER DEFAULT 1,  -- 1=pending, 2=approved, 3=rejected, 4=revision_requested
    submitted_by_id INTEGER REFERENCES users(id),
    submitted_at TIMESTAMP DEFAULT NOW(),
    completed_at TIMESTAMP,
    rejection_reason TEXT,
    revision_requested BOOLEAN DEFAULT false,
    revision_message TEXT,
    client_id UUID REFERENCES clients(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
```

#### **9. ARTICLE_APPROVAL_STEP_LOGS**
```sql
CREATE TABLE article_approval_step_logs (
    id SERIAL PRIMARY KEY,
    approval_flow_id INTEGER REFERENCES article_approval_flows(id) ON DELETE CASCADE,
    step_order INTEGER NOT NULL,
    step_name VARCHAR NOT NULL,
    approved_by_id INTEGER REFERENCES users(id),
    action VARCHAR NOT NULL,  -- approve, reject, auto_skip, request_revision
    message TEXT,
    processed_at TIMESTAMP DEFAULT NOW(),
    user_level_id INTEGER REFERENCES user_levels(id),
    created_at TIMESTAMP DEFAULT NOW()
);
```

#### **10. ARTICLE_APPROVALS (Legacy)**
```sql
CREATE TABLE article_approvals (
    id SERIAL PRIMARY KEY,
    article_id INTEGER REFERENCES articles(id) ON DELETE CASCADE,
    approval_by INTEGER REFERENCES users(id),
    status_id INTEGER,  -- 1=pending, 2=approved, 3=rejected
    message TEXT,
    approval_at_level INTEGER REFERENCES user_levels(id),
    approval_at TIMESTAMP DEFAULT NOW(),
    created_at TIMESTAMP DEFAULT NOW()
);
```

## 🔗 **Relationship Details**

### **One-to-Many Relationships**

```
CLIENTS (1) ──→ (N) USER_LEVELS
CLIENTS (1) ──→ (N) USERS
CLIENTS (1) ──→ (N) APPROVAL_WORKFLOWS
CLIENTS (1) ──→ (N) ARTICLES
CLIENTS (1) ──→ (N) CLIENT_APPROVAL_SETTINGS

USER_LEVELS (1) ──→ (N) USERS
USER_LEVELS (1) ──→ (N) APPROVAL_WORKFLOW_STEPS
USER_LEVELS (1) ──→ (N) ARTICLE_APPROVAL_STEP_LOGS

APPROVAL_WORKFLOWS (1) ──→ (N) APPROVAL_WORKFLOW_STEPS
APPROVAL_WORKFLOWS (1) ──→ (N) ARTICLE_APPROVAL_FLOWS
APPROVAL_WORKFLOWS (1) ──→ (N) ARTICLES

ARTICLES (1) ──→ (N) ARTICLE_APPROVAL_FLOWS
ARTICLES (1) ──→ (N) ARTICLE_APPROVALS

ARTICLE_APPROVAL_FLOWS (1) ──→ (N) ARTICLE_APPROVAL_STEP_LOGS

USERS (1) ──→ (N) ARTICLES (as creator)
USERS (1) ──→ (N) ARTICLE_APPROVAL_FLOWS (as submitter)
USERS (1) ──→ (N) ARTICLE_APPROVAL_STEP_LOGS (as approver)
USERS (1) ──→ (N) ARTICLE_APPROVALS (as approver)
```

### **Many-to-Many Relationships (Implicit)**

```
USERS ←→ USER_LEVELS (through user_level_id)
ARTICLES ←→ APPROVAL_WORKFLOWS (through workflow_id)
ARTICLE_APPROVAL_FLOWS ←→ APPROVAL_WORKFLOW_STEPS (through workflow_id and step_order)
```

## 📊 **Data Flow Examples**

### **Example 1: Complete Approval Process**

#### **Step 1: Setup Data**
```sql
-- Client
INSERT INTO clients (id, name) VALUES ('b1ce6602-07ad-46c2-85eb-0cd6decfefa3', 'Test Client');

-- User Levels
INSERT INTO user_levels (name, alias_name, level_number, is_approval_active, client_id) VALUES
('POLDAS', 'Poldas', 1, true, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3'),
('POLDAS', 'Poldas', 2, true, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3'),
('POLRES', 'Polres', 3, true, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3');

-- Users
INSERT INTO users (name, email, user_level_id, client_id) VALUES
('Admin Level 1', 'admin1@test.com', 1, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3'),
('Admin Level 2', 'admin2@test.com', 2, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3'),
('User Level 3', 'user3@test.com', 3, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3');

-- Workflow
INSERT INTO approval_workflows (name, description, is_default, client_id) VALUES
('Standard Approval', 'Standard 3-step approval process', true, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3');

-- Workflow Steps
INSERT INTO approval_workflow_steps (workflow_id, step_order, step_name, required_user_level_id) VALUES
(1, 1, 'Level 2 Review', 2),
(1, 2, 'Level 1 Final Approval', 1);
```

#### **Step 2: Article Creation**
```sql
-- Article created by User Level 3
INSERT INTO articles (title, slug, workflow_id, current_approval_step, status_id, created_by_id, client_id) VALUES
('Test Article', 'test-article', 1, 1, 1, 3, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3');

-- Approval Flow
INSERT INTO article_approval_flows (article_id, workflow_id, current_step, status_id, submitted_by_id, client_id) VALUES
(1, 1, 1, 1, 3, 'b1ce6602-07ad-46c2-85eb-0cd6decfefa3');

-- Legacy Approval
INSERT INTO article_approvals (article_id, approval_by, status_id, message, approval_at_level) VALUES
(1, 3, 1, 'Need Approval', 2);
```

#### **Step 3: Level 2 Approval**
```sql
-- Step Log
INSERT INTO article_approval_step_logs (approval_flow_id, step_order, step_name, approved_by_id, action, message, user_level_id) VALUES
(1, 1, 'Level 2 Review', 2, 'approve', 'Approved by Level 2', 2);

-- Update Flow
UPDATE article_approval_flows SET current_step = 2 WHERE id = 1;

-- Update Article
UPDATE articles SET current_approval_step = 2 WHERE id = 1;
```

#### **Step 4: Level 1 Final Approval**
```sql
-- Step Log
INSERT INTO article_approval_step_logs (approval_flow_id, step_order, step_name, approved_by_id, action, message, user_level_id) VALUES
(1, 2, 'Level 1 Final Approval', 1, 'approve', 'Final approval by Level 1', 1);

-- Complete Flow
UPDATE article_approval_flows SET 
    status_id = 2, 
    completed_at = NOW() 
WHERE id = 1;

-- Publish Article
UPDATE articles SET 
    status_id = 2, 
    is_publish = true, 
    published_at = NOW(),
    current_approval_step = NULL 
WHERE id = 1;
```

## 🔍 **Key Queries for Monitoring**

### **Active Approval Flows**
```sql
SELECT 
    aaf.id as flow_id,
    a.title as article_title,
    aaf.current_step,
    aws.step_name as current_step_name,
    ul.name as required_approver_level,
    u.name as submitted_by,
    aaf.submitted_at,
    aaf.status_id
FROM article_approval_flows aaf
JOIN articles a ON aaf.article_id = a.id
JOIN approval_workflow_steps aws ON aaf.workflow_id = aws.workflow_id 
    AND aaf.current_step = aws.step_order
JOIN user_levels ul ON aws.required_user_level_id = ul.id
JOIN users u ON aaf.submitted_by_id = u.id
WHERE aaf.status_id = 1  -- pending
ORDER BY aaf.submitted_at DESC;
```

### **Approval History**
```sql
SELECT 
    a.title as article_title,
    aasl.step_name,
    aasl.action,
    aasl.message,
    u.name as approver_name,
    ul.name as approver_level,
    aasl.processed_at
FROM article_approval_step_logs aasl
JOIN article_approval_flows aaf ON aasl.approval_flow_id = aaf.id
JOIN articles a ON aaf.article_id = a.id
JOIN users u ON aasl.approved_by_id = u.id
JOIN user_levels ul ON aasl.user_level_id = ul.id
WHERE aaf.article_id = ?  -- specific article
ORDER BY aasl.step_order, aasl.processed_at;
```

### **Workflow Performance**
```sql
SELECT 
    aw.name as workflow_name,
    COUNT(aaf.id) as total_flows,
    COUNT(CASE WHEN aaf.status_id = 2 THEN 1 END) as approved,
    COUNT(CASE WHEN aaf.status_id = 3 THEN 1 END) as rejected,
    AVG(EXTRACT(EPOCH FROM (aaf.completed_at - aaf.submitted_at))/3600) as avg_hours
FROM approval_workflows aw
LEFT JOIN article_approval_flows aaf ON aw.id = aaf.workflow_id
WHERE aw.client_id = ?
GROUP BY aw.id, aw.name;
```

## ⚠️ **Common Data Issues**

### **Issue 1: Orphaned Records**
```sql
-- Find articles without approval flows
SELECT a.id, a.title 
FROM articles a 
LEFT JOIN article_approval_flows aaf ON a.id = aaf.article_id 
WHERE aaf.id IS NULL AND a.workflow_id IS NOT NULL;

-- Find approval flows without articles
SELECT aaf.id, aaf.article_id 
FROM article_approval_flows aaf 
LEFT JOIN articles a ON aaf.article_id = a.id 
WHERE a.id IS NULL;
```

### **Issue 2: Inconsistent Step Data**
```sql
-- Find flows with invalid current_step
SELECT aaf.id, aaf.current_step, aws.step_order
FROM article_approval_flows aaf
JOIN approval_workflow_steps aws ON aaf.workflow_id = aws.workflow_id
WHERE aaf.current_step != aws.step_order;
```

### **Issue 3: Missing Step Logs**
```sql
-- Find approved flows without step logs
SELECT aaf.id, aaf.article_id, aaf.status_id
FROM article_approval_flows aaf
LEFT JOIN article_approval_step_logs aasl ON aaf.id = aasl.approval_flow_id
WHERE aaf.status_id = 2 AND aasl.id IS NULL;
```

## 🚀 **Performance Optimization**

### **Indexes**
```sql
-- Primary indexes
CREATE INDEX idx_article_approval_flows_status ON article_approval_flows(status_id);
CREATE INDEX idx_article_approval_flows_current_step ON article_approval_flows(current_step);
CREATE INDEX idx_article_approval_flows_client_id ON article_approval_flows(client_id);
CREATE INDEX idx_article_approval_step_logs_flow_id ON article_approval_step_logs(approval_flow_id);
CREATE INDEX idx_article_approval_step_logs_processed_at ON article_approval_step_logs(processed_at);
CREATE INDEX idx_articles_workflow_id ON articles(workflow_id);
CREATE INDEX idx_articles_status_id ON articles(status_id);
CREATE INDEX idx_users_user_level_id ON users(user_level_id);
CREATE INDEX idx_user_levels_level_number ON user_levels(level_number);

-- Composite indexes
CREATE INDEX idx_article_approval_flows_status_step ON article_approval_flows(status_id, current_step);
CREATE INDEX idx_approval_workflow_steps_workflow_order ON approval_workflow_steps(workflow_id, step_order);
```

### **Partitioning (for large datasets)**
```sql
-- Partition article_approval_step_logs by month
CREATE TABLE article_approval_step_logs_2025_01 PARTITION OF article_approval_step_logs
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
```

## 📝 **Data Validation Rules**

### **Business Rules**
1. **Workflow Steps**: Must be sequential (1, 2, 3, etc.)
2. **User Levels**: Level number must be unique per client
3. **Approval Flows**: Can only have one active flow per article
4. **Step Logs**: Must have corresponding workflow step
5. **Status Transitions**: Only valid transitions allowed

### **Database Constraints**
```sql
-- Ensure step order is sequential
ALTER TABLE approval_workflow_steps 
ADD CONSTRAINT chk_step_order_positive CHECK (step_order > 0);

-- Ensure level number is positive
ALTER TABLE user_levels 
ADD CONSTRAINT chk_level_number_positive CHECK (level_number > 0);

-- Ensure status values are valid
ALTER TABLE article_approval_flows 
ADD CONSTRAINT chk_status_valid CHECK (status_id IN (1, 2, 3, 4));

-- Ensure current step is valid
ALTER TABLE article_approval_flows 
ADD CONSTRAINT chk_current_step_positive CHECK (current_step > 0);
```

## 🎯 **Summary**

Database design untuk sistem approval workflow MEDOLS menggunakan:

1. **Normalized Structure**: Memisahkan konfigurasi dari eksekusi
2. **Audit Trail**: Step logs untuk tracking lengkap
3. **Flexibility**: Workflow dapat dikonfigurasi per client
4. **Performance**: Indexes untuk query yang efisien
5. **Data Integrity**: Constraints dan validasi
6. **Scalability**: Partitioning untuk data besar

Dengan struktur ini, sistem dapat menangani berbagai skenario approval dengan efisien dan dapat diandalkan.