project-document-management-docs-000-12-publishing-workflow

---

title: Publishing Workflow Specification type: reference component_type: reference version: 1.0.0 created: '2025-12-27' updated: '2025-12-27' status: deprecated tags:

• ai-ml
• authentication
• security
• testing
• api
• architecture
• automation
• backend summary: 'Publishing Workflow Specification Document ID: 000.12 Version: 1.0.0 Status: Draft Last Updated: December 19, 2025 Owner: CODITECT Document Management Team --- Executive Summary This specification defines the document publishing workflow system for...' moe_confidence: 0.950 moe_classified: 2025-12-31

---

Publishing Workflow Specification

Document ID: 000.12 Version: 1.0.0 Status: Draft Last Updated: December 19, 2025 Owner: CODITECT Document Management Team

---

1. Executive Summary

This specification defines the document publishing workflow system for the CODITECT Document Management System, including document lifecycle states, multi-level approval workflows, and enterprise distribution controls.

1.1 Key Capabilities

Capability	Description	Priority
Document Lifecycle	9-state lifecycle management	P0
Approval Workflows	Multi-level, parallel/sequential	P0
Digital Signatures	E-signature integration	P0
Distribution Control	Role-based access, tracking	P0
Expiration Management	Time-limited access	P1
Watermarking	Dynamic watermarks	P2

1.2 Business Impact

• 80% reduction in approval cycle time (industry benchmark)
• 70% fewer compliance violations from controlled publishing
• 100% audit trail for all publishing activities

---

Workflow Steps

1. Initialize - Set up the environment
2. Configure - Apply settings
3. Execute - Run the process
4. Validate - Check results
5. Complete - Finalize workflow

2. Document Lifecycle States

2.1 State Machine Definition

class DocumentState(Enum):
    """Document lifecycle states."""

    # Creation phase
    DRAFT = "draft"                    # Initial creation, editable
    PENDING_REVIEW = "pending_review"  # Submitted for review

    # Review phase
    IN_REVIEW = "in_review"           # Under active review
    CHANGES_REQUESTED = "changes_requested"  # Reviewer requests edits

    # Approval phase
    PENDING_APPROVAL = "pending_approval"  # Awaiting approval
    APPROVED = "approved"              # Approved, ready for publish

    # Published phase
    PUBLISHED = "published"            # Actively published
    SUPERSEDED = "superseded"          # Replaced by newer version

    # End phase
    ARCHIVED = "archived"              # Retained but not active
    OBSOLETE = "obsolete"              # Marked for deletion


# Valid state transitions
STATE_TRANSITIONS = {
    DocumentState.DRAFT: [
        DocumentState.PENDING_REVIEW,
        DocumentState.ARCHIVED  # Abandon draft
    ],
    DocumentState.PENDING_REVIEW: [
        DocumentState.IN_REVIEW,
        DocumentState.DRAFT  # Recall
    ],
    DocumentState.IN_REVIEW: [
        DocumentState.CHANGES_REQUESTED,
        DocumentState.PENDING_APPROVAL,
        DocumentState.DRAFT  # Reject
    ],
    DocumentState.CHANGES_REQUESTED: [
        DocumentState.DRAFT,  # Author revises
        DocumentState.PENDING_REVIEW  # Resubmit
    ],
    DocumentState.PENDING_APPROVAL: [
        DocumentState.APPROVED,
        DocumentState.CHANGES_REQUESTED,
        DocumentState.DRAFT  # Reject
    ],
    DocumentState.APPROVED: [
        DocumentState.PUBLISHED,
        DocumentState.PENDING_APPROVAL  # Rescind approval
    ],
    DocumentState.PUBLISHED: [
        DocumentState.SUPERSEDED,
        DocumentState.ARCHIVED,
        DocumentState.OBSOLETE
    ],
    DocumentState.SUPERSEDED: [
        DocumentState.ARCHIVED,
        DocumentState.OBSOLETE
    ],
    DocumentState.ARCHIVED: [
        DocumentState.OBSOLETE,
        DocumentState.PUBLISHED  # Republish
    ],
    DocumentState.OBSOLETE: []  # Terminal state
}

2.2 State Transition Diagram

                                    ┌─────────────┐
                                    │   DRAFT     │◄──────────────┐
                                    └──────┬──────┘               │
                                           │ submit               │ revise
                                           ▼                      │
                                    ┌─────────────────┐           │
                                    │ PENDING_REVIEW  │───────────┘
                                    └───────┬─────────┘   recall
                                            │ assign
                                            ▼
                                    ┌─────────────┐
                             ┌──────│  IN_REVIEW  │──────┐
                             │      └──────┬──────┘      │
                    changes  │             │ approve     │ reject
                    requested│             │             │
                             ▼             ▼             │
                ┌────────────────────┐  ┌──────────────────────┐
                │ CHANGES_REQUESTED  │  │  PENDING_APPROVAL    │
                └─────────┬──────────┘  └──────────┬───────────┘
                          │                        │
                          │ resubmit               │ approve
                          └────────────────────────┼─────────────┐
                                                   ▼             │
                                           ┌───────────┐         │
                                           │ APPROVED  │◄────────┘
                                           └─────┬─────┘
                                                 │ publish
                                                 ▼
                                          ┌───────────┐
                    ┌─────────────────────│ PUBLISHED │─────────────────┐
                    │                     └─────┬─────┘                 │
                    │ supersede                 │ archive               │ obsolete
                    ▼                           ▼                       ▼
            ┌────────────┐              ┌───────────┐           ┌───────────┐
            │ SUPERSEDED │──────────────│ ARCHIVED  │──────────►│ OBSOLETE  │
            └────────────┘              └───────────┘           └───────────┘

2.3 State Metadata

@dataclass
class DocumentStateMetadata:
    """Metadata tracked for each state transition."""

    document_id: UUID
    from_state: DocumentState
    to_state: DocumentState
    transitioned_by: UUID
    transitioned_at: datetime
    reason: Optional[str]
    comments: Optional[str]

    # Workflow context
    workflow_instance_id: Optional[UUID]
    approval_step_id: Optional[UUID]

    # Compliance
    requires_signature: bool
    signature_id: Optional[UUID]

    # Immutable record
    transition_hash: str  # SHA-256 for audit trail

---

3. Approval Workflow Engine

3.1 Workflow Definition Schema

@dataclass
class ApprovalWorkflow:
    """Approval workflow definition."""

    id: UUID
    name: str
    description: str
    version: str

    # Scope
    applies_to_document_types: list[str]
    applies_to_classifications: list[str]

    # Steps
    steps: list[ApprovalStep]

    # Configuration
    allow_parallel_approval: bool = False
    require_all_approvers: bool = True  # vs. any approver
    auto_archive_rejected: bool = False
    escalation_enabled: bool = True

    # SLAs
    default_due_days: int = 7
    escalation_after_days: int = 3

    # Notifications
    notify_on_submission: bool = True
    notify_on_approval: bool = True
    notify_on_rejection: bool = True
    reminder_days: list[int] = field(default_factory=lambda: [3, 1])


@dataclass
class ApprovalStep:
    """Individual approval step in workflow."""

    id: UUID
    name: str
    order: int  # Execution order (1, 2, 3...)

    # Approvers
    approver_type: Literal["user", "role", "group", "dynamic"]
    approver_ids: list[UUID]  # User/role/group IDs
    dynamic_approver_rule: Optional[str]  # e.g., "document.owner.manager"

    # Requirements
    approval_type: Literal["all", "any", "quorum"]
    quorum_count: Optional[int]  # For quorum type

    # Delegation
    allow_delegation: bool = True
    delegation_depth: int = 1  # Max levels of delegation

    # Conditional
    condition: Optional[str]  # e.g., "document.value > 10000"
    skip_if_author_is_approver: bool = True

    # Actions
    required_action: Literal["approve", "sign", "acknowledge"]
    signature_required: bool = False

    # SLA
    due_days: Optional[int]  # Override workflow default

3.2 Workflow Execution Engine

class WorkflowEngine:
    """Execute approval workflows."""

    async def start_workflow(
        self,
        document_id: UUID,
        workflow_id: UUID,
        initiated_by: UUID
    ) -> WorkflowInstance:
        """Start a new workflow instance for a document."""

        # Load workflow definition
        workflow = await self.get_workflow(workflow_id)

        # Create instance
        instance = WorkflowInstance(
            id=uuid4(),
            workflow_id=workflow_id,
            document_id=document_id,
            status=WorkflowStatus.IN_PROGRESS,
            current_step_index=0,
            initiated_by=initiated_by,
            initiated_at=datetime.utcnow(),
            due_date=datetime.utcnow() + timedelta(days=workflow.default_due_days)
        )

        await self.save_instance(instance)

        # Update document state
        await self.document_service.transition_state(
            document_id,
            DocumentState.PENDING_APPROVAL
        )

        # Activate first step
        await self.activate_step(instance, workflow.steps[0])

        return instance

    async def activate_step(
        self,
        instance: WorkflowInstance,
        step: ApprovalStep
    ):
        """Activate a workflow step and notify approvers."""

        # Resolve approvers
        approvers = await self.resolve_approvers(step, instance.document_id)

        # Create pending approvals
        for approver in approvers:
            pending = PendingApproval(
                id=uuid4(),
                instance_id=instance.id,
                step_id=step.id,
                approver_id=approver.id,
                status=ApprovalStatus.PENDING,
                due_date=datetime.utcnow() + timedelta(
                    days=step.due_days or 7
                )
            )
            await self.save_pending_approval(pending)

            # Send notification
            await self.notification_service.send(
                recipient=approver.id,
                template="approval_request",
                data={
                    "document_name": instance.document.name,
                    "workflow_name": instance.workflow.name,
                    "step_name": step.name,
                    "due_date": pending.due_date.isoformat(),
                    "approval_url": self.get_approval_url(pending.id)
                }
            )

    async def process_approval(
        self,
        pending_approval_id: UUID,
        decision: Literal["approve", "reject", "request_changes"],
        comments: Optional[str],
        signature: Optional[Signature]
    ) -> ApprovalResult:
        """Process an approval decision."""

        pending = await self.get_pending_approval(pending_approval_id)
        instance = await self.get_instance(pending.instance_id)
        step = await self.get_step(pending.step_id)

        # Validate signature if required
        if step.signature_required and not signature:
            raise SignatureRequiredError("This step requires a signature")

        # Record decision
        pending.status = ApprovalStatus(decision)
        pending.decided_at = datetime.utcnow()
        pending.comments = comments
        pending.signature_id = signature.id if signature else None
        await self.save_pending_approval(pending)

        # Check step completion
        step_complete = await self.check_step_completion(instance, step)

        if step_complete:
            step_approved = await self.check_step_approved(instance, step)

            if step_approved:
                # Move to next step or complete workflow
                next_step_index = instance.current_step_index + 1
                workflow = await self.get_workflow(instance.workflow_id)

                if next_step_index < len(workflow.steps):
                    # Activate next step
                    instance.current_step_index = next_step_index
                    await self.activate_step(instance, workflow.steps[next_step_index])
                else:
                    # Workflow complete
                    await self.complete_workflow(instance, "approved")
            else:
                # Step rejected
                await self.complete_workflow(instance, "rejected")

        return ApprovalResult(
            pending_approval_id=pending_approval_id,
            decision=decision,
            step_complete=step_complete,
            workflow_complete=instance.status != WorkflowStatus.IN_PROGRESS
        )

    async def complete_workflow(
        self,
        instance: WorkflowInstance,
        outcome: Literal["approved", "rejected", "cancelled"]
    ):
        """Complete a workflow with final outcome."""

        instance.status = WorkflowStatus.COMPLETED
        instance.outcome = outcome
        instance.completed_at = datetime.utcnow()
        await self.save_instance(instance)

        # Update document state
        new_state = {
            "approved": DocumentState.APPROVED,
            "rejected": DocumentState.CHANGES_REQUESTED,
            "cancelled": DocumentState.DRAFT
        }[outcome]

        await self.document_service.transition_state(
            instance.document_id,
            new_state
        )

        # Send completion notification
        await self.notification_service.send(
            recipient=instance.initiated_by,
            template=f"workflow_{outcome}",
            data={
                "document_name": instance.document.name,
                "workflow_name": instance.workflow.name,
                "outcome": outcome
            }
        )

3.3 Delegation and Escalation

class DelegationService:
    """Handle approval delegation and out-of-office."""

    async def delegate_approval(
        self,
        pending_approval_id: UUID,
        delegate_to: UUID,
        reason: str,
        expires_at: Optional[datetime] = None
    ) -> Delegation:
        """Delegate an approval to another user."""

        pending = await self.get_pending_approval(pending_approval_id)
        step = await self.get_step(pending.step_id)

        # Validate delegation allowed
        if not step.allow_delegation:
            raise DelegationNotAllowedError("This step does not allow delegation")

        # Check delegation depth
        existing_delegations = await self.get_delegation_chain(pending_approval_id)
        if len(existing_delegations) >= step.delegation_depth:
            raise MaxDelegationDepthError(
                f"Maximum delegation depth ({step.delegation_depth}) reached"
            )

        # Create delegation
        delegation = Delegation(
            id=uuid4(),
            pending_approval_id=pending_approval_id,
            delegated_from=pending.approver_id,
            delegated_to=delegate_to,
            reason=reason,
            delegated_at=datetime.utcnow(),
            expires_at=expires_at
        )

        # Update pending approval
        pending.approver_id = delegate_to
        pending.delegated = True

        await self.save_delegation(delegation)
        await self.save_pending_approval(pending)

        # Notify delegate
        await self.notification_service.send(
            recipient=delegate_to,
            template="approval_delegated",
            data={
                "from_user": delegation.delegated_from,
                "document_name": pending.document.name,
                "reason": reason
            }
        )

        return delegation


class EscalationService:
    """Handle approval escalation for overdue items."""

    async def check_escalations(self):
        """Check for overdue approvals and escalate."""

        overdue = await self.get_overdue_approvals()

        for pending in overdue:
            step = await self.get_step(pending.step_id)
            workflow = await self.get_workflow(step.workflow_id)

            if not workflow.escalation_enabled:
                continue

            # Get escalation target (manager of approver)
            escalation_target = await self.get_escalation_target(pending.approver_id)

            if escalation_target:
                # Create escalation
                escalation = Escalation(
                    id=uuid4(),
                    pending_approval_id=pending.id,
                    escalated_to=escalation_target,
                    escalated_at=datetime.utcnow(),
                    reason="Approval overdue"
                )

                await self.save_escalation(escalation)

                # Notify escalation target
                await self.notification_service.send(
                    recipient=escalation_target,
                    template="approval_escalated",
                    data={
                        "document_name": pending.document.name,
                        "original_approver": pending.approver_id,
                        "days_overdue": (datetime.utcnow() - pending.due_date).days
                    }
                )

---

4. Distribution Controls

4.1 Distribution Configuration

@dataclass
class DistributionConfig:
    """Configuration for document distribution."""

    # Access control
    access_type: Literal["authenticated", "link", "email_list"]
    allowed_users: list[UUID] = field(default_factory=list)
    allowed_roles: list[str] = field(default_factory=list)
    allowed_domains: list[str] = field(default_factory=list)

    # Restrictions
    allow_download: bool = True
    allow_print: bool = True
    allow_copy: bool = True
    allow_forward: bool = False

    # Expiration
    expires_at: Optional[datetime] = None
    max_views: Optional[int] = None

    # Tracking
    track_views: bool = True
    track_downloads: bool = True
    require_acknowledgment: bool = False

    # Watermarking
    apply_watermark: bool = False
    watermark_type: Literal["user_email", "timestamp", "custom"] = "user_email"
    watermark_text: Optional[str] = None

    # Notifications
    notify_on_access: bool = False
    notify_on_download: bool = False

4.2 Distribution Service

class DistributionService:
    """Manage document distribution and access tracking."""

    async def create_distribution(
        self,
        document_id: UUID,
        config: DistributionConfig,
        created_by: UUID
    ) -> Distribution:
        """Create a new distribution for a document."""

        # Validate document is published
        document = await self.document_service.get(document_id)
        if document.state != DocumentState.PUBLISHED:
            raise DocumentNotPublishedError(
                "Only published documents can be distributed"
            )

        distribution = Distribution(
            id=uuid4(),
            document_id=document_id,
            document_version=document.version,
            config=config,
            created_by=created_by,
            created_at=datetime.utcnow(),
            access_count=0,
            download_count=0
        )

        # Generate access URL if link-based
        if config.access_type == "link":
            distribution.access_token = secrets.token_urlsafe(32)
            distribution.access_url = self.generate_access_url(
                distribution.id,
                distribution.access_token
            )

        await self.save_distribution(distribution)

        # Send notifications if email list
        if config.access_type == "email_list":
            for user_id in config.allowed_users:
                await self.notification_service.send(
                    recipient=user_id,
                    template="document_distributed",
                    data={
                        "document_name": document.name,
                        "access_url": self.get_authenticated_url(distribution.id)
                    }
                )

        return distribution

    async def access_document(
        self,
        distribution_id: UUID,
        user: User,
        access_token: Optional[str] = None
    ) -> AccessResult:
        """Validate and record document access."""

        distribution = await self.get_distribution(distribution_id)
        config = distribution.config

        # Validate access
        if config.expires_at and datetime.utcnow() > config.expires_at:
            raise AccessExpiredError("Distribution has expired")

        if config.max_views and distribution.access_count >= config.max_views:
            raise MaxViewsExceededError("Maximum views exceeded")

        if config.access_type == "link" and access_token != distribution.access_token:
            raise InvalidAccessTokenError("Invalid access token")

        if config.access_type == "authenticated":
            if user.id not in config.allowed_users and \
               user.role not in config.allowed_roles and \
               not any(user.email.endswith(d) for d in config.allowed_domains):
                raise AccessDeniedError("User not authorized")

        # Record access
        access_log = DistributionAccessLog(
            id=uuid4(),
            distribution_id=distribution_id,
            user_id=user.id,
            user_email=user.email,
            access_type="view",
            accessed_at=datetime.utcnow(),
            ip_address=user.ip_address,
            user_agent=user.user_agent
        )

        await self.save_access_log(access_log)

        # Increment counter
        distribution.access_count += 1
        await self.save_distribution(distribution)

        # Apply watermark if configured
        document_content = await self.document_service.get_content(
            distribution.document_id
        )

        if config.apply_watermark:
            document_content = await self.apply_watermark(
                document_content,
                config,
                user
            )

        # Notify if configured
        if config.notify_on_access:
            await self.notification_service.send(
                recipient=distribution.created_by,
                template="document_accessed",
                data={
                    "document_name": distribution.document.name,
                    "accessed_by": user.email
                }
            )

        return AccessResult(
            content=document_content,
            mime_type=distribution.document.mime_type,
            allow_download=config.allow_download,
            allow_print=config.allow_print,
            require_acknowledgment=config.require_acknowledgment
        )

4.3 Watermarking Service

class WatermarkService:
    """Apply dynamic watermarks to documents."""

    async def apply_watermark(
        self,
        content: bytes,
        config: DistributionConfig,
        user: User
    ) -> bytes:
        """Apply watermark based on configuration."""

        # Generate watermark text
        if config.watermark_type == "user_email":
            watermark_text = user.email
        elif config.watermark_type == "timestamp":
            watermark_text = datetime.utcnow().strftime("%Y-%m-%d %H:%M UTC")
        elif config.watermark_type == "custom":
            watermark_text = config.watermark_text
        else:
            watermark_text = f"{user.email} - {datetime.utcnow()}"

        # Determine document type
        mime_type = self.detect_mime_type(content)

        if mime_type == "application/pdf":
            return self.watermark_pdf(content, watermark_text)
        elif mime_type.startswith("image/"):
            return self.watermark_image(content, watermark_text)
        else:
            # For other types, create PDF wrapper with watermark
            return self.create_watermarked_wrapper(content, watermark_text)

    def watermark_pdf(self, content: bytes, text: str) -> bytes:
        """Apply diagonal watermark to PDF."""
        from PyPDF2 import PdfReader, PdfWriter
        from reportlab.pdfgen import canvas
        from reportlab.lib.colors import Color

        # Create watermark layer
        watermark_buffer = io.BytesIO()
        c = canvas.Canvas(watermark_buffer)

        # Style: diagonal, semi-transparent, gray
        c.setFillColor(Color(0.7, 0.7, 0.7, alpha=0.3))
        c.setFont("Helvetica", 36)
        c.saveState()
        c.translate(300, 400)
        c.rotate(45)
        c.drawString(0, 0, text)
        c.restoreState()
        c.save()

        watermark_buffer.seek(0)
        watermark_pdf = PdfReader(watermark_buffer)
        watermark_page = watermark_pdf.pages[0]

        # Apply to each page
        reader = PdfReader(io.BytesIO(content))
        writer = PdfWriter()

        for page in reader.pages:
            page.merge_page(watermark_page)
            writer.add_page(page)

        output = io.BytesIO()
        writer.write(output)
        return output.getvalue()

---

5. API Endpoints

5.1 Lifecycle Endpoints

# Get document state
GET /api/v1/documents/{id}/state
Response: {
  "current_state": "draft",
  "available_transitions": ["pending_review"],
  "state_history": [...]
}

# Transition state
POST /api/v1/documents/{id}/state/transition
Body: {
  "to_state": "pending_review",
  "comments": "Ready for review"
}

# Get state history
GET /api/v1/documents/{id}/state/history

5.2 Workflow Endpoints

# List workflow definitions
GET /api/v1/workflows
GET /api/v1/workflows/{id}

# Start workflow
POST /api/v1/documents/{id}/workflows/start
Body: {
  "workflow_id": "uuid",
  "due_date": "2025-12-31T00:00:00Z"
}

# Get active workflow
GET /api/v1/documents/{id}/workflows/active

# Get pending approvals (for current user)
GET /api/v1/approvals/pending

# Process approval
POST /api/v1/approvals/{pending_id}/decision
Body: {
  "decision": "approve",
  "comments": "Looks good",
  "signature": { ... }  // Optional
}

# Delegate approval
POST /api/v1/approvals/{pending_id}/delegate
Body: {
  "delegate_to": "user-uuid",
  "reason": "Out of office"
}

5.3 Distribution Endpoints

# Create distribution
POST /api/v1/documents/{id}/distributions
Body: DistributionConfig

# List distributions
GET /api/v1/documents/{id}/distributions

# Get distribution details
GET /api/v1/distributions/{id}

# Access document via distribution
GET /api/v1/distributions/{id}/access?token={access_token}

# Get access logs
GET /api/v1/distributions/{id}/access-logs

# Revoke distribution
DELETE /api/v1/distributions/{id}

# Update distribution config
PATCH /api/v1/distributions/{id}
Body: { "expires_at": "2025-12-31T00:00:00Z" }

---

6. Database Schema

-- Document state history
CREATE TABLE document_state_history (
    id UUID PRIMARY KEY,
    document_id UUID NOT NULL REFERENCES documents(id),
    from_state VARCHAR(50),
    to_state VARCHAR(50) NOT NULL,
    transitioned_by UUID REFERENCES users(id),
    transitioned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    reason TEXT,
    comments TEXT,
    workflow_instance_id UUID,
    signature_id UUID,
    transition_hash VARCHAR(64) NOT NULL
);

CREATE INDEX idx_state_history_document ON document_state_history(document_id);

-- Workflow definitions
CREATE TABLE workflow_definitions (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    version VARCHAR(50) NOT NULL,
    applies_to_types JSONB,
    applies_to_classifications JSONB,
    config JSONB NOT NULL,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true
);

-- Workflow steps
CREATE TABLE workflow_steps (
    id UUID PRIMARY KEY,
    workflow_id UUID NOT NULL REFERENCES workflow_definitions(id),
    name VARCHAR(255) NOT NULL,
    step_order INTEGER NOT NULL,
    approver_type VARCHAR(50) NOT NULL,
    approver_ids UUID[],
    approval_type VARCHAR(50) NOT NULL,
    config JSONB
);

-- Workflow instances
CREATE TABLE workflow_instances (
    id UUID PRIMARY KEY,
    workflow_id UUID NOT NULL REFERENCES workflow_definitions(id),
    document_id UUID NOT NULL REFERENCES documents(id),
    status VARCHAR(50) NOT NULL DEFAULT 'in_progress',
    outcome VARCHAR(50),
    current_step_index INTEGER DEFAULT 0,
    initiated_by UUID REFERENCES users(id),
    initiated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    due_date TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_workflow_instances_document ON workflow_instances(document_id);
CREATE INDEX idx_workflow_instances_status ON workflow_instances(status);

-- Pending approvals
CREATE TABLE pending_approvals (
    id UUID PRIMARY KEY,
    instance_id UUID NOT NULL REFERENCES workflow_instances(id),
    step_id UUID NOT NULL REFERENCES workflow_steps(id),
    approver_id UUID NOT NULL REFERENCES users(id),
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    due_date TIMESTAMP WITH TIME ZONE,
    decided_at TIMESTAMP WITH TIME ZONE,
    comments TEXT,
    signature_id UUID,
    delegated BOOLEAN DEFAULT false
);

CREATE INDEX idx_pending_approvals_approver ON pending_approvals(approver_id, status);

-- Distributions
CREATE TABLE distributions (
    id UUID PRIMARY KEY,
    document_id UUID NOT NULL REFERENCES documents(id),
    document_version VARCHAR(50) NOT NULL,
    config JSONB NOT NULL,
    access_token VARCHAR(255),
    access_url TEXT,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    access_count INTEGER DEFAULT 0,
    download_count INTEGER DEFAULT 0,
    revoked_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_distributions_document ON distributions(document_id);
CREATE INDEX idx_distributions_token ON distributions(access_token);

-- Distribution access logs
CREATE TABLE distribution_access_logs (
    id UUID PRIMARY KEY,
    distribution_id UUID NOT NULL REFERENCES distributions(id),
    user_id UUID,
    user_email VARCHAR(255),
    access_type VARCHAR(50) NOT NULL,
    accessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ip_address INET,
    user_agent TEXT
);

CREATE INDEX idx_access_logs_distribution ON distribution_access_logs(distribution_id);

---

7. Implementation Checklist

Phase 1: Lifecycle Engine (Weeks 1-2)

• Implement document state enum and transitions
• Create state machine validation
• Build state history tracking with hash chain
• Create state transition API endpoints

Phase 2: Approval Workflows (Weeks 3-5)

• Implement workflow definition schema
• Build workflow execution engine
• Create approval processing logic
• Implement delegation and escalation
• Build notification system integration

Phase 3: Distribution Controls (Weeks 6-7)

• Implement distribution configuration
• Build access control validation
• Create access tracking and logging
• Implement watermarking service

Phase 4: UI and Integration (Week 8)

• Build approval inbox UI
• Create workflow designer UI
• Implement distribution management UI
• Integrate with Google Drive publishing

---

Document Version: 1.0.0 Effective Date: Upon Approval Review Date: Quarterly Owner: Workflow Team