I'm building a content workflow tool and need relational tables plus views

Sure — below is a solid relational schema for a content workflow tool, along with a few useful SQL views to support reporting and operational dashboards.

I’ll assume a fairly standard workflow with:

• users
• teams
• content items
• assignments
• statuses / workflow stages
• comments
• approvals
• publishing targets
• history/audit trail

If you want, I can also tailor this for a specific database engine like PostgreSQL, MySQL, or SQL Server.

---

1) Core Tables

users

Stores people who use the system.

CREATE TABLE users (
    user_id        BIGSERIAL PRIMARY KEY,
    email          VARCHAR(255) NOT NULL UNIQUE,
    full_name      VARCHAR(200) NOT NULL,
    job_title      VARCHAR(150),
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at     TIMESTAMP NOT NULL DEFAULT NOW()
);

---

teams

Groups users together.

CREATE TABLE teams (
    team_id        BIGSERIAL PRIMARY KEY,
    team_name      VARCHAR(200) NOT NULL UNIQUE,
    created_at     TIMESTAMP NOT NULL DEFAULT NOW()
);

---

team_members

Many-to-many relationship between users and teams.

CREATE TABLE team_members (
    team_id        BIGINT NOT NULL REFERENCES teams(team_id) ON DELETE CASCADE,
    user_id        BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    role_name      VARCHAR(100),
    joined_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (team_id, user_id)
);

---

workflow_stages

Defines statuses/stages in your content lifecycle.

CREATE TABLE workflow_stages (
    stage_id       BIGSERIAL PRIMARY KEY,
    stage_name     VARCHAR(100) NOT NULL UNIQUE,
    stage_order    INT NOT NULL,
    is_final      BOOLEAN NOT NULL DEFAULT FALSE
);

Example values:

• Draft
• In Review
• Needs Edits
• Approved
• Scheduled
• Published
• Archived

---

content_items

The main content entity.

CREATE TABLE content_items (
    content_id      BIGSERIAL PRIMARY KEY,
    title           VARCHAR(500) NOT NULL,
    slug            VARCHAR(600) UNIQUE,
    content_type    VARCHAR(100) NOT NULL,
    body            TEXT,
    current_stage_id BIGINT REFERENCES workflow_stages(stage_id),
    owner_user_id   BIGINT REFERENCES users(user_id),
    team_id         BIGINT REFERENCES teams(team_id),
    priority        INT NOT NULL DEFAULT 3,
    due_date        DATE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    published_at    TIMESTAMP,
    archived_at     TIMESTAMP
);

---

content_assignees

Allows multiple assignees per content item.

CREATE TABLE content_assignees (
    content_id      BIGINT NOT NULL REFERENCES content_items(content_id) ON DELETE CASCADE,
    user_id         BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    assigned_at     TIMESTAMP NOT NULL DEFAULT NOW(),
    assigned_by     BIGINT REFERENCES users(user_id),
    PRIMARY KEY (content_id, user_id)
);

---

content_comments

Comments and review notes.

CREATE TABLE content_comments (
    comment_id      BIGSERIAL PRIMARY KEY,
    content_id      BIGINT NOT NULL REFERENCES content_items(content_id) ON DELETE CASCADE,
    author_user_id  BIGINT NOT NULL REFERENCES users(user_id),
    comment_text    TEXT NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    is_internal     BOOLEAN NOT NULL DEFAULT TRUE
);

---

content_approvals

Approval tracking.

CREATE TABLE content_approvals (
    approval_id     BIGSERIAL PRIMARY KEY,
    content_id      BIGINT NOT NULL REFERENCES content_items(content_id) ON DELETE CASCADE,
    approver_user_id BIGINT NOT NULL REFERENCES users(user_id),
    approval_status VARCHAR(30) NOT NULL, -- pending, approved, rejected
    decision_note   TEXT,
    decided_at      TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

---

publish_targets

Where content gets published.

CREATE TABLE publish_targets (
    target_id       BIGSERIAL PRIMARY KEY,
    target_name     VARCHAR(200) NOT NULL UNIQUE,
    target_type     VARCHAR(100) NOT NULL, -- web, linkedin, email, cms, etc.
    config_json     JSONB,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

---

content_publications

Tracks publication attempts and outcomes.

CREATE TABLE content_publications (
    publication_id  BIGSERIAL PRIMARY KEY,
    content_id      BIGINT NOT NULL REFERENCES content_items(content_id) ON DELETE CASCADE,
    target_id       BIGINT NOT NULL REFERENCES publish_targets(target_id),
    scheduled_for   TIMESTAMP,
    published_at    TIMESTAMP,
    publication_status VARCHAR(50) NOT NULL, -- scheduled, published, failed, canceled
    external_url    TEXT,
    error_message   TEXT,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

---

content_history

Audit trail for lifecycle changes.

CREATE TABLE content_history (
    history_id      BIGSERIAL PRIMARY KEY,
    content_id      BIGINT NOT NULL REFERENCES content_items(content_id) ON DELETE CASCADE,
    changed_by      BIGINT REFERENCES users(user_id),
    changed_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    field_name      VARCHAR(100) NOT NULL,
    old_value       TEXT,
    new_value       TEXT
);

---

2) Useful Indexes

CREATE INDEX idx_content_items_stage_id ON content_items(current_stage_id);
CREATE INDEX idx_content_items_owner_user_id ON content_items(owner_user_id);
CREATE INDEX idx_content_items_team_id ON content_items(team_id);
CREATE INDEX idx_content_items_due_date ON content_items(due_date);

CREATE INDEX idx_comments_content_id ON content_comments(content_id);
CREATE INDEX idx_approvals_content_id ON content_approvals(content_id);
CREATE INDEX idx_publications_content_id ON content_publications(content_id);
CREATE INDEX idx_history_content_id ON content_history(content_id);

---

3) Helpful Views

A. v_content_overview

Shows the main content record with owner, team, and stage.

CREATE VIEW v_content_overview AS
SELECT
    c.content_id,
    c.title,
    c.slug,
    c.content_type,
    c.priority,
    c.due_date,
    c.created_at,
    c.updated_at,
    c.published_at,
    c.archived_at,
    u.full_name AS owner_name,
    u.email AS owner_email,
    t.team_name,
    s.stage_name AS current_stage
FROM content_items c
LEFT JOIN users u ON c.owner_user_id = u.user_id
LEFT JOIN teams t ON c.team_id = t.team_id
LEFT JOIN workflow_stages s ON c.current_stage_id = s.stage_id;

---

B. v_content_assignment_summary

Lists content with assigned users.

CREATE VIEW v_content_assignment_summary AS
SELECT
    c.content_id,
    c.title,
    COUNT(ca.user_id) AS assignee_count,
    STRING_AGG(u.full_name, ', ' ORDER BY u.full_name) AS assignees
FROM content_items c
LEFT JOIN content_assignees ca ON c.content_id = ca.content_id
LEFT JOIN users u ON ca.user_id = u.user_id
GROUP BY c.content_id, c.title;

---

C. v_content_review_status

Summarizes approval and comment activity.

CREATE VIEW v_content_review_status AS
SELECT
    c.content_id,
    c.title,
    COUNT(DISTINCT com.comment_id) AS comment_count,
    COUNT(DISTINCT a.approval_id) AS approval_count,
    SUM(CASE WHEN a.approval_status = 'approved' THEN 1 ELSE 0 END) AS approved_decisions,
    SUM(CASE WHEN a.approval_status = 'rejected' THEN 1 ELSE 0 END) AS rejected_decisions
FROM content_items c
LEFT JOIN content_comments com ON c.content_id = com.content_id
LEFT JOIN content_approvals a ON c.content_id = a.content_id
GROUP BY c.content_id, c.title;

---

D. v_publication_status

Shows publication details per content item and target.

CREATE VIEW v_publication_status AS
SELECT
    c.content_id,
    c.title,
    pt.target_name,
    pt.target_type,
    cp.publication_status,
    cp.scheduled_for,
    cp.published_at,
    cp.external_url,
    cp.error_message
FROM content_publications cp
JOIN content_items c ON cp.content_id = c.content_id
JOIN publish_targets pt ON cp.target_id = pt.target_id;

---

E. v_overdue_content

Finds content that is overdue and not in a final stage.

CREATE VIEW v_overdue_content AS
SELECT
    c.content_id,
    c.title,
    c.due_date,
    s.stage_name,
    u.full_name AS owner_name
FROM content_items c
LEFT JOIN workflow_stages s ON c.current_stage_id = s.stage_id
LEFT JOIN users u ON c.owner_user_id = u.user_id
WHERE c.due_date < CURRENT_DATE
  AND COALESCE(s.is_final, FALSE) = FALSE;

---

F. v_stage_counts

Counts content by workflow stage.

CREATE VIEW v_stage_counts AS
SELECT
    s.stage_name,
    COUNT(c.content_id) AS content_count
FROM workflow_stages s
LEFT JOIN content_items c ON c.current_stage_id = s.stage_id
GROUP BY s.stage_name
ORDER BY MIN(s.stage_order);

---

4) Optional Enhancements

If your tool is more advanced, you may also want:

• tags + content_tags
• attachments
• versions
• custom fields
• notifications
• automation rules
• workflow transitions table to control allowed stage changes

Example for tags:

CREATE TABLE tags (
    tag_id      BIGSERIAL PRIMARY KEY,
    tag_name    VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE content_tags (
    content_id  BIGINT NOT NULL REFERENCES content_items(content_id) ON DELETE CASCADE,
    tag_id      BIGINT NOT NULL REFERENCES tags(tag_id) ON DELETE CASCADE,
    PRIMARY KEY (content_id, tag_id)
);

---

5) Suggested Workflow Flow

A simple lifecycle could be:

1. Draft
2. In Review
3. Needs Edits
4. Approved
5. Scheduled
6. Published
7. Archived

Your application logic can enforce transitions such as:

• Draft → In Review
• In Review → Needs Edits / Approved
• Approved → Scheduled
• Scheduled → Published

---

6) If You Want a Cleaner Reporting Layer

I can also generate:

• a star schema version for BI/reporting
• materialized views
• stored procedures for transitions
• PostgreSQL DDL with enums
• ERD diagram description
• sample seed data

If you want, I can next provide a PostgreSQL-ready version with enums, constraints, and trigger-based updated_at maintenance.