Orquestração de litigation hold com n8n

# Litigation hold orchestration — n8n flow

Orchestrates a litigation-hold issue cycle: notification, acknowledgement tracking, reminder cadence, escalation paths, and an immutable audit log. Replaces the spreadsheet-and-Outlook-rules manual cycle with a deterministic flow that can't drop a custodian.

## Import

1. Import `litigation-hold-orchestration-n8n.json` into n8n.
2. Set workflow timezone to your office TZ.
3. Provision the audit table (DDL in `audit-table-schema.sql`).
4. Wire credentials.
5. Do NOT enable until dry-run confirms behavior on a closed hold.

## What ships in this bundle

This n8n export covers the **issuance** half of the cycle (Phase 1). The tracking + escalation half (Phase 2 — daily cron, reminders, escalation) is intentionally a separate workflow you wire to the same audit table; that lets the issuance flow be triggered fresh per hold while the tracker runs continuously across all active holds.

The Phase 2 tracker is a 4-node workflow:

1. Cron daily 9am office TZ.
2. Postgres query against the audit table: select hold/custodian pairs without `acknowledged` action.
3. Code node: determine action (reminder at +3, +7; escalation to legal-ops lead at +14; escalation to manager at +21).
4. Email send + audit-log append.

The DDL and the audit-query are in this README's appendix below; the Phase 2 export is left to the legal-ops engineer to assemble per the firm's escalation paths (which vary by jurisdiction and matter type more than the issuance step does).

## Credentials

### `PLACEHOLDER_CUSTODIAN_DB_CRED_ID` — Custodian source

Read access to wherever the custodian list lives:

- **In-house Postgres** — the default. Schema documented in the `Load Custodian List` node.
- **Relativity / Everlaw / Logikcull** — replace the Postgres node with an HTTP node calling the platform's custodian endpoint.
- **HRIS** — possible if HRIS is the source of truth, but legal-ops typically maintains the custodian list separately.

### `PLACEHOLDER_SMTP_CRED_ID` — SMTP

Use the **firm's own mail relay**, not a third-party SaaS like SendGrid. Hold notices may flag the existence of a litigation matter; routing them through a third party expands the privilege exposure.

### `PLACEHOLDER_SLACK_CRED_ID` — Slack bot token

`chat:write` scope. The bot needs to be invited to the workspaces where custodians live; per-custodian DMs require user-level scope which Slack handles automatically when the bot is in the workspace.

### `PLACEHOLDER_AUDIT_DB_CRED_ID` — Audit table

Write access to the audit table. The table itself must be append-only at the DB level (DDL below).

## Audit table schema

```sql
CREATE TABLE hold_audit (
    audit_id        BIGSERIAL PRIMARY KEY,
    hold_id         TEXT NOT NULL,
    custodian_id    TEXT NOT NULL,
    action          TEXT NOT NULL CHECK (action IN (
        'notice_sent',
        'reminder_sent',
        'escalated_to_lead',
        'escalated_to_manager',
        'acknowledged',
        'released'
    )),
    template_sha    TEXT,
    payload_json    JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX hold_audit_lookup_idx ON hold_audit (hold_id, custodian_id, action);

-- Immutability: the audit table is the firm's defensibility chain.
-- Counsel needs to be able to demonstrate the log can't be edited.
REVOKE UPDATE, DELETE, TRUNCATE ON hold_audit FROM PUBLIC;
REVOKE UPDATE, DELETE, TRUNCATE ON hold_audit FROM <legal_ops_app_role>;
GRANT INSERT, SELECT ON hold_audit TO <legal_ops_app_role>;
```

The `REVOKE` statements are NOT optional. Without them the table is editable, and counsel cannot demonstrate the audit log's defensibility under e-discovery scrutiny.

## Hold-notice template

Save per-matter templates as `n8n/data/hold-notices/<matter-id>.md`. The template uses these placeholders, replaced per custodian by the `Personalize Notice` node:

- `{{custodian_name}}` — custodian's full name
- `{{matter_id}}` — matter identifier
- `{{hold_id}}` — hold identifier
- `{{ack_url}}` — per-custodian acknowledgement URL

Sample template structure (counsel approves the actual language):

```
Dear {{custodian_name}},

This is a litigation hold notice for Matter {{matter_id}}.

You are required to preserve all documents, communications, and electronic
data related to [counsel-defined scope]. This includes [specific systems
and document types].

You must NOT delete, alter, or destroy any potentially relevant material,
even if it would be deleted under normal retention policy.

Please acknowledge receipt of this notice by clicking the link below
within 7 business days:

{{ack_url}}

If you have questions about scope or what to preserve, contact [legal-ops
contact].

This hold remains in effect until you receive a written release notice
from the legal department.

[counsel signature block]
```

## Phase 2 tracker query

The Phase 2 daily cron uses this query to find non-acknowledgers:

```sql
WITH last_action AS (
    SELECT hold_id, custodian_id, MAX(created_at) AS last_action_at,
           BOOL_OR(action = 'acknowledged') AS acknowledged
    FROM hold_audit
    WHERE action IN ('notice_sent', 'reminder_sent', 'acknowledged', 'released')
    GROUP BY hold_id, custodian_id
)
SELECT
    la.hold_id,
    la.custodian_id,
    la.last_action_at,
    EXTRACT(EPOCH FROM (NOW() - la.last_action_at)) / 86400 AS days_since_last_action,
    (SELECT MIN(created_at) FROM hold_audit ha
        WHERE ha.hold_id = la.hold_id AND ha.custodian_id = la.custodian_id
        AND ha.action = 'notice_sent') AS first_notice_at
FROM last_action la
WHERE la.acknowledged = false
  AND NOT EXISTS (
      SELECT 1 FROM hold_audit ha
      WHERE ha.hold_id = la.hold_id AND ha.custodian_id = la.custodian_id
      AND ha.action = 'released'
  );
```

The tracker computes `days_since_first_notice`, decides action, dispatches.

## Dry-run procedure

1. Provision the audit table on a non-production DB.
2. Wire credentials against staging endpoints (test SMTP, test Slack workspace).
3. Replay a closed hold's custodian list manually.
4. Confirm: notification fires once per custodian; audit log records each send; per-custodian acknowledgement URLs are unique.
5. Switch to production DB. Issue your next real hold via the flow.

## Known limits

- Phase 1 issuance only ships in this n8n export; Phase 2 tracker is documented but not bundled.
- The flow assumes per-custodian email + Slack. Custodians without Slack accounts (contractors, alumni) get email only — the Slack node fails gracefully but the failure is silent in the audit log. The Phase 2 tracker should treat email-only custodians the same as Slack+email.
- Acknowledgement URL routing requires a separate webhook endpoint (the `/ack/<hold>/<custodian>/<token>` URL) — deploy that as a small Express / Flask endpoint that writes `action: acknowledged` to the audit table. The endpoint is NOT bundled.
- The flow does not handle hold-release. Release is its own action — when counsel releases, a separate webhook writes `action: released` to the audit table per custodian. The tracker stops sending reminders to released custodian/hold pairs.

-- Litigation hold audit table — append-only DDL.
-- Counsel's defensibility chain depends on this table being immutable.

CREATE TABLE hold_audit (
    audit_id        BIGSERIAL PRIMARY KEY,
    hold_id         TEXT NOT NULL,
    custodian_id    TEXT NOT NULL,
    action          TEXT NOT NULL CHECK (action IN (
        'notice_sent',
        'reminder_sent',
        'escalated_to_lead',
        'escalated_to_manager',
        'acknowledged',
        'released'
    )),
    template_sha    TEXT,
    payload_json    JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX hold_audit_lookup_idx
    ON hold_audit (hold_id, custodian_id, action);

CREATE INDEX hold_audit_created_idx
    ON hold_audit (created_at);

-- Immutability constraints. NOT optional.
-- Replace <legal_ops_app_role> with the DB role your n8n flow uses.
REVOKE UPDATE, DELETE, TRUNCATE ON hold_audit FROM PUBLIC;
GRANT INSERT, SELECT ON hold_audit TO <legal_ops_app_role>;

-- Optional: row-level revisioning if you anticipate legitimate
-- corrections (e.g. a custodian acknowledged offline and the lead
-- needs to record it). Even then, NEVER UPDATE existing rows —
-- INSERT a 'corrected_offline' action with a payload_json reference
-- to the prior row's audit_id, preserving the original record.

# Hold notice template

Per-matter template the litigation-hold flow personalizes per custodian. Copy this file to `n8n/data/hold-notices/<matter-id>.md` per matter and customize counsel-approved language.

The template uses these placeholders, replaced per custodian by the `Personalize Notice` node:

- `{{custodian_name}}`
- `{{matter_id}}`
- `{{hold_id}}`
- `{{ack_url}}`

---

# Litigation Hold Notice — Matter {{matter_id}} — Hold {{hold_id}}

Dear {{custodian_name}},

The firm's legal department is issuing a litigation hold related to Matter {{matter_id}}. As a custodian whose records may be relevant, you are required to take immediate steps to preserve potentially relevant material.

## What you must preserve

You must preserve all documents, communications, and electronic data related to [counsel-defined scope — replace with matter-specific scope]. This includes, but is not limited to:

- Email (work and any personal email used for work-related communication on this matter)
- Slack messages, including DMs and channel posts
- Documents (Google Drive, SharePoint, OneDrive, local files)
- Calendar entries
- [Other systems specific to this matter — replace with named systems]

The scope covers material from [counsel-defined date range] to the date this hold is released in writing.

## What you must NOT do

You must NOT delete, alter, modify, or destroy any potentially relevant material, even if the firm's normal retention policy would otherwise permit deletion.

You must NOT discuss this hold's scope or substance with anyone outside the legal team and your direct counsel of record.

## What this means in practice

- **Auto-deletion settings:** check email, Slack, and document-system retention settings. If any are set to delete material in the relevant scope, contact [legal-ops contact] immediately.
- **Personal devices:** if you have used personal devices or accounts for any work related to this matter, [legal-ops contact] will work with you on appropriate preservation.
- **Departing the firm:** if you leave the firm while this hold is in effect, [legal-ops contact] will work with HR and IT to preserve your records before any account decommissioning.
- **Replacement of equipment:** do not destroy or recycle hardware in scope until [legal-ops contact] confirms preservation.

## Acknowledgement

You must acknowledge receipt of this notice within 7 business days. Please click the link below:

[{{ack_url}}]({{ack_url}})

If you have not received an automated reminder by [date + 14 days], please contact [legal-ops contact] to confirm your acknowledgement was recorded.

## Questions

For questions about scope or what to preserve, contact:

- [legal-ops contact name and email]
- [outside counsel contact, if applicable]

Do NOT discuss the substance of the matter outside this channel.

## When this hold ends

This hold remains in effect until you receive a written release notice from the legal department. Verbal release is not sufficient. Until you receive written release, you must continue to preserve material in scope.

---

[counsel signature block]

[firm letterhead — match firm's standard hold-notice formatting]

{
  "name": "Litigation hold orchestration",
  "nodes": [
    {
      "parameters": {
        "httpMethod": "POST",
        "path": "issue-hold",
        "responseMode": "lastNode",
        "options": { "rawBody": false }
      },
      "id": "4a4a4a4a-0001-0000-0000-000000000001",
      "name": "Issue Trigger",
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 2,
      "position": [240, 300],
      "webhookId": "issue-hold",
      "notesInFlow": true,
      "notes": "Receives `{matter_id, hold_id, custodian_list_source, notice_template_path}` from the legal-ops platform when counsel marks a hold ready to issue. Manual trigger via curl is also acceptable for first-issue workflows."
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT custodian_id, full_name, email, slack_user_id, manager_email, last_updated_at\nFROM custodian_registry\nWHERE matter_id = $1\n  AND active = true\n  AND deleted_at IS NULL;",
        "options": {
          "queryReplacement": "={{ $json.matter_id }}"
        }
      },
      "id": "4a4a4a4a-0001-0000-0000-000000000002",
      "name": "Load Custodian List",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.4,
      "position": [460, 300],
      "credentials": {
        "postgres": {
          "id": "PLACEHOLDER_CUSTODIAN_DB_CRED_ID",
          "name": "Postgres — custodian registry"
        }
      },
      "notesInFlow": true,
      "notes": "Pulls active custodians for the matter. Replace with HTTP node if your custodian source is in Relativity / Everlaw / Logikcull. Schema returned must match: custodian_id, full_name, email, slack_user_id (optional), manager_email."
    },
    {
      "parameters": {
        "jsCode": "// Load notice template, hash it, and prepare per-custodian email payloads.\nconst fs = require('fs');\nconst crypto = require('crypto');\n\nconst trigger = $('Issue Trigger').item.json;\nconst custodians = $input.all().map(r => r.json);\nconst templatePath = trigger.notice_template_path;\nconst template = fs.readFileSync(templatePath, 'utf8');\nconst templateSha = crypto.createHash('sha256').update(template).digest('hex').slice(0, 16);\n\nconst out = custodians.map(c => {\n  const ackToken = crypto.randomBytes(16).toString('hex');\n  const ackUrl = `${$env.ACK_BASE_URL}/ack/${trigger.hold_id}/${c.custodian_id}/${ackToken}`;\n  const personalized = template\n    .replace(/\\{\\{custodian_name\\}\\}/g, c.full_name)\n    .replace(/\\{\\{matter_id\\}\\}/g, trigger.matter_id)\n    .replace(/\\{\\{hold_id\\}\\}/g, trigger.hold_id)\n    .replace(/\\{\\{ack_url\\}\\}/g, ackUrl);\n\n  return {\n    json: {\n      hold_id: trigger.hold_id,\n      matter_id: trigger.matter_id,\n      custodian_id: c.custodian_id,\n      custodian_email: c.email,\n      custodian_slack_id: c.slack_user_id,\n      manager_email: c.manager_email,\n      template_sha: templateSha,\n      ack_token: ackToken,\n      ack_url: ackUrl,\n      personalized_text: personalized,\n      issued_at: new Date().toISOString(),\n    }\n  };\n});\n\nreturn out;"
      },
      "id": "4a4a4a4a-0001-0000-0000-000000000003",
      "name": "Personalize Notice",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [680, 300],
      "notesInFlow": true,
      "notes": "Loads the per-matter notice template, computes SHA for the audit log, generates a unique acknowledgement token + URL per custodian. The token is what makes acknowledgements custodian-specific."
    },
    {
      "parameters": {
        "fromEmail": "={{ $env.LEGAL_OPS_FROM_EMAIL }}",
        "toEmail": "={{ $json.custodian_email }}",
        "subject": "LEGAL HOLD: Action Required — Matter {{ $json.matter_id }}",
        "emailFormat": "html",
        "html": "={{ $json.personalized_text }}<br><br><a href=\"{{ $json.ack_url }}\">Click here to acknowledge receipt of this hold notice</a>",
        "options": {}
      },
      "id": "4a4a4a4a-0001-0000-0000-000000000004",
      "name": "Send Hold Email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 2.1,
      "position": [900, 240],
      "credentials": {
        "smtp": {
          "id": "PLACEHOLDER_SMTP_CRED_ID",
          "name": "SMTP — firm mail relay (not third-party)"
        }
      },
      "notesInFlow": true,
      "notes": "Hold notices may flag the existence of a litigation matter. Use the firm's own mail relay (not a third-party SaaS like SendGrid) for confidentiality."
    },
    {
      "parameters": {
        "method": "POST",
        "url": "https://slack.com/api/chat.postMessage",
        "authentication": "predefinedCredentialType",
        "nodeCredentialType": "slackApi",
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={\n  \"channel\": \"{{ $json.custodian_slack_id }}\",\n  \"text\": \"You have received a litigation hold notice for Matter {{ $json.matter_id }}. Please check your email for details and acknowledgement instructions.\",\n  \"unfurl_links\": false\n}",
        "options": {}
      },
      "id": "4a4a4a4a-0001-0000-0000-000000000005",
      "name": "Send Hold Slack DM",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [900, 360],
      "credentials": {
        "slackApi": {
          "id": "PLACEHOLDER_SLACK_CRED_ID",
          "name": "Slack bot token (chat:write)"
        }
      },
      "notesInFlow": true,
      "notes": "Slack DM is intentionally vague — does not name the matter. Custodian gets details from email. The DM exists to ensure the email isn't missed in inbox-overflow scenarios."
    },
    {
      "parameters": {
        "operation": "insert",
        "schema": "public",
        "table": "hold_audit",
        "columns": "hold_id, custodian_id, action, template_sha, payload_json, created_at",
        "additionalFields": {}
      },
      "id": "4a4a4a4a-0001-0000-0000-000000000006",
      "name": "Audit: Notice Sent",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.4,
      "position": [1120, 300],
      "credentials": {
        "postgres": {
          "id": "PLACEHOLDER_AUDIT_DB_CRED_ID",
          "name": "Postgres — immutable audit table (append-only)"
        }
      },
      "notesInFlow": true,
      "notes": "Append-only by DB constraint (REVOKE UPDATE, DELETE on this table). Counsel's defensibility depends on this; do not work around the constraint."
    }
  ],
  "connections": {
    "Issue Trigger": { "main": [[{ "node": "Load Custodian List", "type": "main", "index": 0 }]] },
    "Load Custodian List": { "main": [[{ "node": "Personalize Notice", "type": "main", "index": 0 }]] },
    "Personalize Notice": {
      "main": [
        [
          { "node": "Send Hold Email", "type": "main", "index": 0 },
          { "node": "Send Hold Slack DM", "type": "main", "index": 0 }
        ]
      ]
    },
    "Send Hold Email": { "main": [[{ "node": "Audit: Notice Sent", "type": "main", "index": 0 }]] },
    "Send Hold Slack DM": { "main": [[{ "node": "Audit: Notice Sent", "type": "main", "index": 0 }]] }
  },
  "settings": {
    "executionOrder": "v1",
    "timezone": "America/New_York",
    "saveExecutionProgress": true,
    "saveManualExecutions": true,
    "callerPolicy": "workflowsFromSameOwner"
  },
  "active": false,
  "versionId": "1"
}