┌─────────────────────────────────────────────────────────────────────────────┐
│                        DELIVERABLE LIFECYCLE                                 │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   PLANNED → IN PROGRESS → REVIEW → REVISION → APPROVED → DELIVERED         │
│                                                                              │
│   Linear      Build        Client     Update     Sign-off    Archive        │
│   Ticket      Phase        Preview    Phase      Captured    + Handoff      │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

-- agency_deliverables from SCHEMA.md
CREATE TABLE agency_deliverables (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  canonical_id text UNIQUE,
  project_id uuid REFERENCES agency_projects(id),
  
  -- Content
  title text NOT NULL,
  description text,
  
  -- Timeline
  due_date date,
  
  -- Files
  file_urls jsonb DEFAULT '[]',  -- Array of file URLs
  
  -- Completion tracking
  is_completed boolean DEFAULT false,
  completed_at timestamptz,
  
  -- Approval workflow
  requires_approval boolean DEFAULT true,
  approved_by uuid REFERENCES auth.users(id),
  approved_at timestamptz,
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Deliverable types and templates
CREATE TABLE agency_deliverable_types (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  name text NOT NULL,                    -- 'automation_workflow', 'report', 'training'
  category text NOT NULL,                -- 'build', 'document', 'media', 'training'
  
  -- Template
  default_description text,
  estimated_hours numeric,
  
  -- Checklist items (JSON array)
  checklist_template jsonb DEFAULT '[]',
  
  created_at timestamptz DEFAULT now()
);

-- Deliverable versions (for revision tracking)
CREATE TABLE agency_deliverable_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  deliverable_id uuid REFERENCES agency_deliverables(id),
  
  version_number integer NOT NULL,
  
  -- Snapshot
  file_urls jsonb,
  notes text,
  
  -- Who created this version
  created_by uuid REFERENCES auth.users(id),
  created_at timestamptz DEFAULT now()
);

-- Deliverable feedback
CREATE TABLE agency_deliverable_feedback (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  deliverable_id uuid REFERENCES agency_deliverables(id),
  version_id uuid REFERENCES agency_deliverable_versions(id),
  
  -- Feedback content
  feedback_type text NOT NULL,  -- 'approval', 'revision_request', 'comment'
  content text,
  
  -- Who gave feedback
  given_by uuid REFERENCES auth.users(id),
  given_by_role text,  -- 'client', 'pm', 'reviewer'
  
  created_at timestamptz DEFAULT now()
);

interface AutomationDeliverable {
  type: 'automation_workflow';
  
  // Core info
  title: string;
  description: string;
  
  // Technical details
  platform: 'n8n' | 'make' | 'zapier' | 'custom';
  trigger_type: 'webhook' | 'schedule' | 'manual' | 'event';
  
  // Outputs
  workflow_json_url: string;
  documentation_url: string;
  
  // Testing
  test_cases: {
    scenario: string;
    input: Record<string, any>;
    expected_output: Record<string, any>;
    status: 'pass' | 'fail' | 'pending';
  }[];
  
  // Handoff
  credentials_required: string[];
  environment_variables: Record<string, string>;
  monitoring_setup: boolean;
}

# Automation Audit Report

**Client:** [Company Name]
**Date:** [Date]
**Prepared By:** [Name]

---

## Executive Summary

[2-3 sentences on overall findings and top recommendations]

## Current State

### Tools in Use
| Tool | Purpose | Monthly Cost | Integration Status |
|------|---------|--------------|-------------------|
| [Tool] | [Purpose] | $[Cost] | [Connected/Isolated] |

### Current Workflows
1. **[Process Name]**
   - Steps: [X]
   - Time: [X hours/week]
   - Pain points: [List]

## Opportunities

### Quick Wins (< 1 week)
1. [Opportunity with ROI estimate]

### Medium Term (1-4 weeks)
1. [Opportunity with ROI estimate]

### Strategic (1-3 months)
1. [Opportunity with ROI estimate]

## Recommendations

| Priority | Initiative | Est. Investment | Est. ROI | Timeline |
|----------|-----------|-----------------|----------|----------|
| 1 | [Initiative] | $[X] | [X]x | [X weeks] |

## Next Steps

1. [Specific action]
2. [Specific action]
3. [Specific action]

---

## Appendix

### Detailed Process Maps
### Tool Screenshots
### Data Flow Diagrams

# [Process Name] SOP

**Version:** 1.0
**Last Updated:** [Date]
**Owner:** [Role]

---

## Purpose

[Why this process exists]

## Scope

[What this covers and doesn't cover]

## Prerequisites

- [ ] [Prerequisite 1]
- [ ] [Prerequisite 2]

## Procedure

### Step 1: [Title]

**Action:** [What to do]

**Details:**
1. [Substep]
2. [Substep]

**Screenshot/Example:**
[Image or code block]

### Step 2: [Title]

[Continue pattern]

## Troubleshooting

| Issue | Cause | Solution |
|-------|-------|----------|
| [Issue] | [Cause] | [Solution] |

## Related Documents

- [Link to related SOP]
- [Link to related system]

┌─────────────────────────────────────────────────────────────────┐
│                     APPROVAL WORKFLOW                            │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│   DRAFT → INTERNAL REVIEW → CLIENT PREVIEW → FEEDBACK LOOP     │
│                                     ↓                            │
│                              ┌──────┴──────┐                    │
│                              │             │                    │
│                           APPROVED    REVISION                  │
│                              │        REQUEST                   │
│                              ↓             │                    │
│                          DELIVERED    ←────┘                    │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

-- Submit for review
UPDATE agency_deliverables
SET status = 'in_review',
    submitted_at = now()
WHERE id = :deliverable_id;

-- Create version snapshot
INSERT INTO agency_deliverable_versions (
  deliverable_id,
  version_number,
  file_urls,
  notes,
  created_by
)
SELECT 
  id,
  COALESCE((SELECT MAX(version_number) FROM agency_deliverable_versions WHERE deliverable_id = :deliverable_id), 0) + 1,
  file_urls,
  :version_notes,
  auth.uid()
FROM agency_deliverables
WHERE id = :deliverable_id;

-- Record approval
UPDATE agency_deliverables
SET approved_by = auth.uid(),
    approved_at = now(),
    is_completed = true,
    completed_at = now()
WHERE id = :deliverable_id;

-- Record feedback
INSERT INTO agency_deliverable_feedback (
  deliverable_id,
  version_id,
  feedback_type,
  content,
  given_by,
  given_by_role
) VALUES (
  :deliverable_id,
  :version_id,
  :feedback_type,  -- 'approval', 'revision_request', 'comment'
  :content,
  auth.uid(),
  :role
);

/agency/
  /clients/
    /{client_canonical_id}/
      /projects/
        /{project_canonical_id}/
          /deliverables/
            /{deliverable_canonical_id}/
              /v1/
                - workflow.json
                - documentation.md
              /v2/
                - workflow.json
                - documentation.md
              /final/
                - workflow.json
                - documentation.md

interface DeliverableFile {
  url: string;           // Supabase Storage URL
  filename: string;      // Original filename
  file_type: string;     // MIME type
  size_bytes: number;
  uploaded_at: string;   // ISO timestamp
  uploaded_by: string;   // user_id
  version: number;       // Which version this belongs to
}

// file_urls jsonb structure
type FileUrls = DeliverableFile[];

async function uploadDeliverableFile(
  deliverableId: string,
  file: File,
  version: number
): Promise<DeliverableFile> {
  const deliverable = await supabase
    .from('agency_deliverables')
    .select('canonical_id, project:agency_projects(canonical_id, client:agency_clients(canonical_id))')
    .eq('id', deliverableId)
    .single();
  
  const path = `agency/clients/${deliverable.project.client.canonical_id}/projects/${deliverable.project.canonical_id}/deliverables/${deliverable.canonical_id}/v${version}/${file.name}`;
  
  const { data, error } = await supabase.storage
    .from('deliverables')
    .upload(path, file);
  
  if (error) throw error;
  
  const { data: { publicUrl } } = supabase.storage
    .from('deliverables')
    .getPublicUrl(path);
  
  return {
    url: publicUrl,
    filename: file.name,
    file_type: file.type,
    size_bytes: file.size,
    uploaded_at: new Date().toISOString(),
    uploaded_by: (await supabase.auth.getUser()).data.user.id,
    version
  };
}

## Deliverable Quality Checklist

### All Deliverables
- [ ] Title matches project scope
- [ ] Description explains what it does and why
- [ ] All files uploaded and accessible
- [ ] Version number incremented
- [ ] Due date is accurate

### Build Deliverables
- [ ] Code reviewed internally
- [ ] Tested in staging environment
- [ ] Error handling implemented
- [ ] Logging configured
- [ ] Documentation complete
- [ ] Credentials documented (not in code)

### Document Deliverables
- [ ] Spelling/grammar checked
- [ ] Formatting consistent
- [ ] Screenshots current
- [ ] Links working
- [ ] Client branding applied (if required)

### Media Deliverables
- [ ] Audio clear
- [ ] Video quality acceptable
- [ ] Captions/transcript provided
- [ ] Length appropriate

interface DeliverableValidation {
  deliverable_id: string;
  checks: {
    name: string;
    status: 'pass' | 'fail' | 'warning';
    message: string;
  }[];
  is_valid: boolean;
}

async function validateDeliverable(deliverableId: string): Promise<DeliverableValidation> {
  const deliverable = await getDeliverable(deliverableId);
  const checks = [];
  
  // Required fields
  checks.push({
    name: 'has_title',
    status: deliverable.title ? 'pass' : 'fail',
    message: deliverable.title ? 'Title present' : 'Missing title'
  });
  
  checks.push({
    name: 'has_description',
    status: deliverable.description ? 'pass' : 'warning',
    message: deliverable.description ? 'Description present' : 'Missing description'
  });
  
  // Files
  checks.push({
    name: 'has_files',
    status: deliverable.file_urls?.length > 0 ? 'pass' : 'fail',
    message: `${deliverable.file_urls?.length || 0} files attached`
  });
  
  // Due date
  const isOverdue = deliverable.due_date && new Date(deliverable.due_date) < new Date();
  checks.push({
    name: 'due_date_valid',
    status: isOverdue ? 'warning' : 'pass',
    message: isOverdue ? 'Deliverable is overdue' : 'Due date OK'
  });
  
  return {
    deliverable_id: deliverableId,
    checks,
    is_valid: checks.every(c => c.status !== 'fail')
  };
}

## Deliverable Handoff

### Pre-Handoff
- [ ] Final approval received
- [ ] All files in /final/ folder
- [ ] Documentation complete
- [ ] Access credentials shared securely

### Handoff Meeting
- [ ] Walk through deliverable
- [ ] Demonstrate functionality
- [ ] Answer questions
- [ ] Confirm client can access/use

### Post-Handoff
- [ ] Handoff confirmation email sent
- [ ] Linear ticket closed
- [ ] Hours logged
- [ ] Internal retrospective (if needed)

Subject: [Project Name] - [Deliverable Name] Delivered

Hi [Client Name],

Great news! **[Deliverable Name]** is now complete and ready for use.

## What's Included

- [File 1]: [Brief description]
- [File 2]: [Brief description]
- [Documentation]: [Link]

## Access

[Instructions for accessing/using the deliverable]

## Next Steps

1. [Any action required from client]
2. [What happens next in project]

## Questions?

Reply to this email or reach out in Slack at #[channel].

Best,
[Your Name]

-- Deliverables by status
SELECT 
  CASE 
    WHEN is_completed THEN 'completed'
    WHEN due_date < current_date AND NOT is_completed THEN 'overdue'
    ELSE 'in_progress'
  END as status,
  COUNT(*) as count
FROM agency_deliverables d
JOIN agency_projects p ON d.project_id = p.id
WHERE p.client_id = :client_id
GROUP BY 1;

-- Average approval time
SELECT 
  AVG(EXTRACT(epoch FROM (approved_at - created_at)) / 86400) as avg_days_to_approval
FROM agency_deliverables
WHERE approved_at IS NOT NULL
  AND created_at > now() - interval '90 days';

-- Revision rate
SELECT 
  d.id,
  d.title,
  COUNT(dv.id) as version_count,
  COUNT(df.id) FILTER (WHERE df.feedback_type = 'revision_request') as revision_requests
FROM agency_deliverables d
LEFT JOIN agency_deliverable_versions dv ON d.id = dv.deliverable_id
LEFT JOIN agency_deliverable_feedback df ON d.id = df.deliverable_id
GROUP BY d.id
HAVING COUNT(dv.id) > 1;

-- Project deliverables overview
SELECT 
  p.name as project_name,
  COUNT(d.id) as total_deliverables,
  COUNT(d.id) FILTER (WHERE d.is_completed) as completed,
  COUNT(d.id) FILTER (WHERE d.due_date < current_date AND NOT d.is_completed) as overdue,
  COUNT(d.id) FILTER (WHERE d.requires_approval AND d.approved_at IS NULL AND d.is_completed = false) as awaiting_approval
FROM agency_projects p
LEFT JOIN agency_deliverables d ON p.id = d.project_id
WHERE p.client_id = :client_id
GROUP BY p.id
ORDER BY p.start_date DESC;

// Create Linear issue for deliverable
async function createDeliverableTicket(deliverable: AgencyDeliverable) {
  const issue = await linear.createIssue({
    teamId: AGENCY_TEAM_ID,
    title: `[Deliverable] ${deliverable.title}`,
    description: `
## Deliverable Details

**Project:** ${deliverable.project.name}
**Client:** ${deliverable.project.client.company_name}
**Due:** ${deliverable.due_date}

## Description

${deliverable.description}

## Checklist

${deliverable.checklist?.map(item => `- [ ] ${item}`).join('\n')}

---
_Deliverable ID: ${deliverable.canonical_id}_
    `,
    dueDate: deliverable.due_date,
    labelIds: [DELIVERABLE_LABEL_ID],
    projectId: deliverable.project.linear_project_id
  });
  
  return issue;
}