kontenhumas
/
kontenhumas-be
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
kontenhumas-be/plan/database-relationships-deta...

14 KiB
Raw Permalink Blame History

Database Relationships & Entity Details

🗄️ Entity Relationship Overview

Core Entities

1. CLIENTS

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

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

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

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

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

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

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

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

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)

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

-- 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

-- 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

-- 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

-- 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

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

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

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

-- 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

-- 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

-- 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

-- 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)

-- 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

-- 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.