PDF Templates Database Schema

Complete database schema design for the PDF templates feature.

---

Tables Overview

pdf_templates              → Main template definitions
pdf_template_versions      → Version history
pdf_template_components    → Reusable components (letterhead, footer, etc.)

---

pdf_templates Table

Main template storage with versioning support.

CREATE TABLE pdf_templates (
    id                  BIGSERIAL PRIMARY KEY,
    organization_id     BIGINT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,

    -- Metadata
    name                TEXT NOT NULL,
    description         TEXT,
    template_type       TEXT NOT NULL,
    -- Values: 'report', 'prescription', 'disclaimer', 'certificate', 'invoice'

    -- Template content
    template_html       TEXT NOT NULL,  -- HTML with Go template syntax
    template_css        TEXT,           -- Custom CSS styling

    -- Editor state (JSONB) - stores block-based editor structure
    editor_state        JSONB NOT NULL DEFAULT '{}',
    -- {
    --   "blocks": [
    --     {
    --       "id": "block_1",
    --       "type": "letterhead",
    --       "config": {"showLogo": true, "alignment": "center"}
    --     },
    --     {
    --       "id": "block_2",
    --       "type": "heading",
    --       "config": {"content": "Consultation Report", "level": 2}
    --     },
    --     {
    --       "id": "block_3",
    --       "type": "field_list",
    --       "config": {"showPrivateFields": false}
    --     }
    --   ]
    -- }

    -- Layout config (JSONB)
    layout_config       JSONB NOT NULL DEFAULT '{}',
    -- {
    --   "pageSize": "A4",           -- A4, Letter, Legal
    --   "orientation": "portrait",  -- portrait, landscape
    --   "margins": {
    --     "top": 20,                -- mm
    --     "right": 20,
    --     "bottom": 20,
    --     "left": 20
    --   },
    --   "header": true,
    --   "footer": true,
    --   "pageNumbers": true
    -- }

    -- Versioning (same pattern as custom_fields)
    version             INT NOT NULL DEFAULT 1,
    published           BOOLEAN NOT NULL DEFAULT FALSE,

    -- Component references
    components_used     JSONB,
    -- ["letterhead", "signature_block", "footer_standard"]

    -- Audit
    created_by          BIGINT REFERENCES users(id),
    updated_by          BIGINT REFERENCES users(id),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (organization_id, name)
);

CREATE INDEX idx_pdf_templates_org ON pdf_templates(organization_id);
CREATE INDEX idx_pdf_templates_type ON pdf_templates(template_type);
CREATE INDEX idx_pdf_templates_published ON pdf_templates(published);

Template Types

Type	Use Case	Common Fields
report	Consultation reports	Patient info, form fields, specialist signature
prescription	Medication prescriptions	Patient info, medications, dosage, specialist signature
disclaimer	Legal documents, consents	Organization letterhead, legal text, patient signature
certificate	Completion certificates	Patient info, service details, specialist signature
invoice	Billing invoices	Patient info, service line items, total, payment terms

---

pdf_template_versions Table

Stores historical snapshots of templates (same versioning pattern as custom fields).

CREATE TABLE pdf_template_versions (
    id                  BIGSERIAL PRIMARY KEY,
    template_id         BIGINT NOT NULL REFERENCES pdf_templates(id) ON DELETE CASCADE,
    organization_id     BIGINT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,

    -- Version metadata
    version             INT NOT NULL,
    published_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Snapshot of template at publish time
    name                TEXT NOT NULL,
    template_type       TEXT NOT NULL,
    template_html       TEXT NOT NULL,
    template_css        TEXT,
    editor_state        JSONB NOT NULL,
    layout_config       JSONB NOT NULL,
    components_used     JSONB,

    -- Audit
    changed_by          BIGINT REFERENCES users(id),
    change_notes        TEXT,  -- Optional notes about what changed in this version
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (template_id, version)
);

CREATE INDEX idx_pdf_template_versions_template ON pdf_template_versions(template_id);
CREATE INDEX idx_pdf_template_versions_org ON pdf_template_versions(organization_id);

Versioning Workflow

1. Draft changes: Updates to pdf_templates set published = false
2. Publish:
   • Archive current version to pdf_template_versions
   • Increment version in pdf_templates
   • Set published = true
3. Rollback: Copy version from pdf_template_versions back to pdf_templates

---

pdf_template_components Table

Reusable template blocks shared across templates (letterhead, footer, signature blocks).

CREATE TABLE pdf_template_components (
    id                  BIGSERIAL PRIMARY KEY,
    organization_id     BIGINT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,

    -- Metadata
    name                TEXT NOT NULL,
    description         TEXT,

    -- Component content
    component_html      TEXT NOT NULL,  -- HTML snippet
    component_css       TEXT,           -- CSS snippet

    -- Component type
    category            TEXT NOT NULL,
    -- Values: 'header', 'footer', 'signature', 'letterhead', 'table', 'section'

    -- Preview thumbnail (optional)
    thumbnail_url       TEXT,

    -- Variables this component expects
    variables_used      TEXT[],
    -- ['Organization.LogoURL', 'Organization.Name', 'Organization.Address']

    -- Audit
    created_by          BIGINT REFERENCES users(id),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (organization_id, name)
);

CREATE INDEX idx_pdf_template_components_org ON pdf_template_components(organization_id);
CREATE INDEX idx_pdf_template_components_category ON pdf_template_components(category);

Component Categories

Category	Purpose	Examples
letterhead	Organization header	Logo + name + address
footer	Page footer	Organization info, page numbers, confidentiality notice
signature	Specialist signature block	Signature image, name, title, license
header	Document header (not page header)	Document title, metadata
table	Reusable table layouts	Service line items, medication tables
section	Content sections	Patient info section, findings section

---

Integration with Existing Tables

Form Templates Reference

-- Forms can specify which PDF template to use for document generation
ALTER TABLE form_templates
ADD COLUMN pdf_template_id BIGINT REFERENCES pdf_templates(id) ON DELETE SET NULL;

-- Example: Intake survey uses "Standard Report" template
UPDATE form_templates
SET pdf_template_id = (SELECT id FROM pdf_templates WHERE name = 'Standard Report')
WHERE type = 'survey';

Documents Reference Template Version

-- Documents track which template version was used (immutability)
ALTER TABLE appointment_documents
ADD COLUMN pdf_template_id BIGINT REFERENCES pdf_templates(id),
ADD COLUMN pdf_template_version INT;

-- When generating PDF, record template version
INSERT INTO appointment_documents (
    appointment_id,
    document_type,
    file_url,
    pdf_template_id,
    pdf_template_version
) VALUES (
    102,
    'report',
    's3://bucket/org-1/documents/report_123.pdf',
    5,  -- template id
    3   -- template version at generation time
);

---

Row-Level Security (RLS)

pdf_templates

ALTER TABLE pdf_templates ENABLE ROW LEVEL SECURITY;

-- Admins can manage templates for their organization
CREATE POLICY pdf_templates_admin_all ON pdf_templates
FOR ALL
TO authenticated
USING (organization_id = current_app_org_id() AND has_role('admin'))
WITH CHECK (organization_id = current_app_org_id() AND has_role('admin'));

-- Specialists can view published templates
CREATE POLICY pdf_templates_specialist_read ON pdf_templates
FOR SELECT
TO authenticated
USING (
    organization_id = current_app_org_id()
    AND published = true
    AND has_role('specialist')
);

pdf_template_versions

ALTER TABLE pdf_template_versions ENABLE ROW LEVEL SECURITY;

-- Admins can view version history
CREATE POLICY pdf_template_versions_admin_read ON pdf_template_versions
FOR SELECT
TO authenticated
USING (organization_id = current_app_org_id() AND has_role('admin'));

pdf_template_components

ALTER TABLE pdf_template_components ENABLE ROW LEVEL SECURITY;

-- Admins can manage components
CREATE POLICY pdf_template_components_admin_all ON pdf_template_components
FOR ALL
TO authenticated
USING (organization_id = current_app_org_id() AND has_role('admin'))
WITH CHECK (organization_id = current_app_org_id() AND has_role('admin'));

-- Specialists can view components (for template preview)
CREATE POLICY pdf_template_components_specialist_read ON pdf_template_components
FOR SELECT
TO authenticated
USING (organization_id = current_app_org_id() AND has_role('specialist'));

---

Sample Data

Default Template (Letterhead Component)

INSERT INTO pdf_template_components (
    organization_id,
    name,
    description,
    category,
    component_html,
    component_css,
    variables_used
) VALUES (
    1,
    'Standard Letterhead',
    'Organization header with logo and contact info',
    'letterhead',
    '<div class="letterhead">
  <img src="{{.Organization.LogoURL}}" alt="{{.Organization.Name}}" class="logo" />
  <h1 class="org-name">{{.Organization.Name}}</h1>
  <p class="org-contact">{{.Organization.Address}} | {{.Organization.Phone}}</p>
</div>',
    '.letterhead {
  text-align: center;
  border-bottom: 2px solid #003366;
  padding-bottom: 10mm;
  margin-bottom: 10mm;
}
.letterhead .logo {
  max-width: 150px;
  margin-bottom: 5mm;
}
.letterhead .org-name {
  font-size: 18pt;
  color: #003366;
  margin: 0;
}
.letterhead .org-contact {
  font-size: 10pt;
  color: #666;
  margin: 5px 0 0;
}',
    ARRAY['Organization.LogoURL', 'Organization.Name', 'Organization.Address', 'Organization.Phone']
);

Default Report Template

INSERT INTO pdf_templates (
    organization_id,
    name,
    description,
    template_type,
    template_html,
    template_css,
    editor_state,
    layout_config,
    components_used,
    version,
    published
) VALUES (
    1,
    'Standard Consultation Report',
    'Default template for consultation reports',
    'report',
    '<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  {{template "styles"}}
</head>
<body>
  {{template "letterhead"}}
  <h2 class="doc-title">Consultation Report</h2>
  {{template "patient_info"}}
  {{template "form_fields"}}
  {{template "signature"}}
  {{template "footer"}}
</body>
</html>',
    -- CSS stored in template_css column
    'body { font-family: Arial, sans-serif; }',
    -- Editor state (JSONB)
    '{
      "blocks": [
        {"id": "1", "type": "letterhead", "config": {}},
        {"id": "2", "type": "heading", "config": {"content": "Consultation Report", "level": 2}},
        {"id": "3", "type": "section", "config": {"title": "Patient Information"}},
        {"id": "4", "type": "field_list", "config": {}},
        {"id": "5", "type": "signature", "config": {}},
        {"id": "6", "type": "footer", "config": {}}
      ]
    }'::jsonb,
    -- Layout config (JSONB)
    '{
      "pageSize": "A4",
      "orientation": "portrait",
      "margins": {"top": 20, "right": 20, "bottom": 20, "left": 20},
      "header": true,
      "footer": true,
      "pageNumbers": true
    }'::jsonb,
    -- Components used
    '["Standard Letterhead", "Standard Footer"]'::jsonb,
    1,
    true
);

---

Migration Script

-- Create tables
\i pdf_templates_schema.sql

-- Seed default components for all organizations
INSERT INTO pdf_template_components (organization_id, name, category, component_html)
SELECT
    id,
    'Standard Letterhead',
    'letterhead',
    '<div class="letterhead">
      <img src="{{.Organization.LogoURL}}" />
      <h1>{{.Organization.Name}}</h1>
    </div>'
FROM organizations;

-- Add foreign keys to existing tables
ALTER TABLE form_templates ADD COLUMN pdf_template_id BIGINT REFERENCES pdf_templates(id);
ALTER TABLE appointment_documents ADD COLUMN pdf_template_id BIGINT REFERENCES pdf_templates(id);
ALTER TABLE appointment_documents ADD COLUMN pdf_template_version INT;

---

Indexes and Performance

Query Patterns

1. List templates by organization and type

   SELECT * FROM pdf_templates
   WHERE organization_id = 1
     AND template_type = 'report'
     AND published = true;

   • Covered by: idx_pdf_templates_org, idx_pdf_templates_type

2. Get template with version history

   SELECT
     t.*,
     json_agg(v.* ORDER BY v.version DESC) AS versions
   FROM pdf_templates t
   LEFT JOIN pdf_template_versions v ON v.template_id = t.id
   WHERE t.id = 5
   GROUP BY t.id;

   • Covered by: idx_pdf_template_versions_template

3. List components by category

   SELECT * FROM pdf_template_components
   WHERE organization_id = 1
     AND category = 'letterhead';

   • Covered by: idx_pdf_template_components_org, idx_pdf_template_components_category

---

Storage Estimates

Assumptions:

• 100 organizations
• 10 templates per organization (average)
• 5 versions per template (average)
• 20 components per organization

Storage breakdown:

• pdf_templates: ~10KB per template × 1,000 templates = 10 MB
• pdf_template_versions: ~10KB × 5,000 versions = 50 MB
• pdf_template_components: ~5KB × 2,000 components = 10 MB

Total: ~70 MB (negligible)

---

Constraints and Validation

Template Name Uniqueness

-- Enforced by UNIQUE constraint
UNIQUE (organization_id, name)

Template Type Validation

ALTER TABLE pdf_templates
ADD CONSTRAINT valid_template_type
CHECK (template_type IN ('report', 'prescription', 'disclaimer', 'certificate', 'invoice'));

Component Category Validation

ALTER TABLE pdf_template_components
ADD CONSTRAINT valid_component_category
CHECK (category IN ('header', 'footer', 'signature', 'letterhead', 'table', 'section'));

Layout Config Validation

-- Validate pageSize is valid
ALTER TABLE pdf_templates
ADD CONSTRAINT valid_page_size
CHECK (
    (layout_config->>'pageSize') IS NULL OR
    (layout_config->>'pageSize') IN ('A4', 'Letter', 'Legal', 'A3', 'A5')
);

-- Validate orientation
ALTER TABLE pdf_templates
ADD CONSTRAINT valid_orientation
CHECK (
    (layout_config->>'orientation') IS NULL OR
    (layout_config->>'orientation') IN ('portrait', 'landscape')
);

---

Audit Trail

Track who created/modified templates:

-- Audit trigger for pdf_templates
CREATE TRIGGER set_pdf_templates_updated_at
BEFORE UPDATE ON pdf_templates
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- Audit trigger for pdf_template_components
CREATE TRIGGER set_pdf_template_components_updated_at
BEFORE UPDATE ON pdf_template_components
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

---

Cleanup Policies

Delete Unused Templates

-- Find templates not referenced by any form_template or document
SELECT t.id, t.name
FROM pdf_templates t
WHERE NOT EXISTS (
    SELECT 1 FROM form_templates ft WHERE ft.pdf_template_id = t.id
)
AND NOT EXISTS (
    SELECT 1 FROM appointment_documents d WHERE d.pdf_template_id = t.id
);

Archive Old Versions (Optional)

-- Keep only last 10 versions per template
DELETE FROM pdf_template_versions
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY template_id ORDER BY version DESC) AS rn
        FROM pdf_template_versions
    ) sub
    WHERE rn <= 10
);