An n8n flow that gives the legal department one front door. Requests arrive from a form or a shared mailbox, get classified against a twelve-type taxonomy, and land in one of five lanes — self-serve template, playbook review, lawyer, blocked-on-requester, or GC escalation. An hourly sweep chases the SLA clock in business hours; a Monday morning report says what the department was actually asked for. Costs roughly $0.011 to $0.017 per request in Claude inference.
The workflow ships in apps/web/public/artifacts/legal-request-intake-router-n8n/legal-request-intake-router-n8n.json — 26 nodes across three trigger-rooted branches. The three Postgres tables it needs are in the sibling schema.sql, and credential setup plus a six-step verification sequence are in _README.md.
When to use
Your legal team fields somewhere north of 30 requests a month and cannot say, without opening a spreadsheet, how many arrived last week or what they were about. Requests reach you through whatever channel the requester happened to remember — 87% of legal requests arrive by email, per Checkbox’s intake research, with the rest by phone or in person. A meaningful share of what lands is work a requester could finish alone if someone pointed them at a template.
The win is not classification. The win is that a request gets a first touch in under ten minutes with a named lane and a stated deadline, and that at the end of the week you have a defensible answer to “what is legal spending its time on?” That second half is why this is worth building. CLOC’s 2026 State of the Industry Report, drawing on 135 law departments with a median revenue of $13 billion, found workload rising in regulatory compliance (63% of departments) and cybersecurity (58%) while the two usual relief valves narrowed: only 47% expect inside legal spend to grow (down from 65%) and 32% expect attorney headcount growth. When you cannot hire your way out, the argument for more resourcing has to be made from demand data you do not currently collect.
This flow is the layer above per-contract-type automation. It decides which pipeline a request belongs to; the NDA intake triage flow does the clause-level work once an NDA has been identified as one.
When NOT to use
Skip it below about 30 requests a month. The two-hour setup is the small cost; codifying a service catalog and populating the requester directory is the real one, and it does not pay back at that volume. A shared mailbox and a weekly rota is the better answer.
Skip it if you have no written templates or playbook. The self-serve lane points at a template URL, and the playbook lane assumes a reviewer has a written position to check against. Without those, every request routes to a lawyer and you have built an expensive queue. Write the catalog first — that is the actual project, and this flow is what makes it visible afterwards.
Skip it for intake that must be privileged from first contact: internal investigations, whistleblower reports, and anything already under a litigation hold. Those need a separate channel that bypasses the automation entirely. The flow’s privilege gate catches the ones that arrive at the front door by mistake, but a channel you have deliberately routed through a classifier is a channel you have to defend later.
Skip it if legal’s problem is capacity rather than routing. The router makes the queue legible and the demand measurable. It does not make lawyers, and a team already at 100% utilization will see the same backlog with better labels on it.
Setup
Run schema.sql first. It creates requester_directory (who is asking and on whose paper), legal_request_log (the audit trail and the SLA clock), and legal_sla_policy (one row per lane, seeded with the four default clocks). Then import the JSON, bind the five placeholder credentials per the README, and — before anything else — set the workflow timezone. The export ships Europe/London, and every cron expression plus the business-hours arithmetic in Compute Breach Tier reads from that one setting. Getting it wrong does not throw; it silently shifts every deadline.
The configuration that decides behavior is a handful of constants in two Code nodes. In Apply Routing Policy: CONFIDENCE_FLOOR is 0.75, VALUE_ESCALATION_USD is $250,000, and WALK_AWAY_FLAGS lists the six categories that force a GC escalation regardless of what the model concluded. Set VALUE_ESCALATION_USD to whatever your signature-authority matrix already says rather than a round number. In Normalize Request, PRIVILEGE_PATTERNS is eight regexes that divert a request to the GC channel without an API call at all.
Expect to tune the SLA hours once. They live in the legal_sla_policy table rather than in a node, so changing them is a SQL update and not a workflow edit.
What the flow does
Intake Form Webhook and Intake Mailbox Poll — legal@ are the two entry points; Normalize Request is the only node that knows there are two. It flattens both shapes into one envelope, caps the body at 4,000 characters, and sets a privilege_hit boolean. A request with no identifiable requester is force-flagged rather than guessed at — there is nobody to send an answer to.
Privileged-Content Gate acts on that boolean. Its true branch goes straight to #legal-gc-escalations and the model never sees the content. That ordering is the point: for a subpoena or an investigation, a classification round trip is a privilege problem, not a latency saving.
Requester Context looks the sender up by exact address first and email domain second, so a named exception beats the org-wide default. Merge Requester Context defaults a missing row to risk_posture: 'unknown' rather than 'standard' — that single word is what stops an unrecognized sender receiving an automated legal answer.
Claude — Classify + Route sends the envelope to Sonnet 5 with a system prompt naming twelve request types, three candidate lanes, nine risk flags, and a fixed list of fields a reviewer would have to chase. It returns strict JSON with a lane, a confidence, and a rationale under 300 characters. Sonnet 5 rather than Haiku because the expensive error is a lawyer-grade request sitting in a self-serve auto-reply, not the per-call price difference.
Apply Routing Policy is the safety belt, and it is deliberately ordinary JavaScript rather than a second prompt. Five overrides fire in priority order: a walk-away risk flag forces gc_escalation; a claimed value at or above the escalation floor forces lawyer; confidence below 0.75 demotes self_serve to playbook; a non-standard risk posture does the same; and named missing fields route to awaiting_requester. Every override stamps its reason onto the audit row, so you can measure which guards earn their place. A parse failure — the classic regression after any prompt edit is the model wrapping its JSON in markdown fences — is caught and escalated to a human unread. The guards live here rather than in the prompt because a prompt-only guardrail is bypassable by whatever text a requester pastes into the form.
Lane Switch fans out to five branches, with gc_escalation as the fallback output rather than a silent drop. All five converge on Write Intake Log, which inserts one row keyed on source_message_id with ON CONFLICT DO NOTHING — n8n retries on transient Postgres errors, and a duplicate row would double-count every number in the weekly report. The self-serve lane writes a row too. An unlogged self-serve answer is invisible work, and invisible work is exactly what this flow exists to stop.
The second branch sweeps hourly on weekdays, computes elapsed business hours against each lane’s SLA, and escalates at 50%, 100%, and 150% of the clock, jumping to the GC channel at the third tier. Record Escalation Tier writes the tier back after the Slack post, so a failed post produces a duplicate nudge next hour rather than a silent miss. The third branch aggregates the week and posts a report that states what to do about each number rather than only what the number is.
Cost reality
Claude inference is the dominant variable cost. A request serializes to roughly 2,400–3,800 input tokens — the twelve-type taxonomy and lane rules dominate, with the requester’s free text adding 200–800 — and the structured response lands at 250–400 output tokens. At Sonnet 5 list pricing of $3 per million input and $15 per million output, that is $0.011 to $0.017 per request. At 400 requests a month, $4.40–$6.80. At 2,000, $22–$34.
n8n executions are the other line. The intake branch is one execution per request; the SLA sweep at ten weekday runs a day is about 220 a month; the report is about four. So 400 requests a month is roughly 620 executions, inside n8n Cloud’s Starter plan (€20/month, 2,500 executions). At 2,000 requests a month you are at roughly 2,220 and should be on Pro (€50/month, 10,000 executions) for the headroom and the concurrency. Self-hosting on a small VPS handles either without an execution cap.
Set against that: the manual version of this job is a coordinator reading each request, deciding where it goes, and chasing it. At an estimated 6–10 minutes per request for read-classify-route-acknowledge and a fully-loaded coordinator rate of $60–$90 an hour, that is $6–$15 of human time per request — figures marked as estimates, not measured benchmarks. The ratio is not the interesting part. The interesting part is that the coordinator’s judgment gets spent on the 20–30% of requests where routing is genuinely ambiguous instead of on the NDA that arrives for the fortieth time.
Success metrics
Track three numbers weekly, all of them already in the report.
Intake coverage — the share of legal work that entered through the front door at all. Proxy it with the ratio of source = 'form' to source = 'email' rows, and target email below 30% by day 90. Coverage is the metric that decides whether anything else here is real; a router that sees half the work produces a demand report that is confidently wrong.
Self-serve durability — of the requests answered with a template, the percentage where the same person did not come back within seven days. Target 85% or better. This is the honest counterweight to a deflection rate, which measures only that you said no quickly.
First touch under ten minutes, every lane. If it drifts, the cause is a slow API call or queued n8n executions, not the routing logic. Check the execution list before touching a threshold.
vs alternatives
vs the shared mailbox and a spreadsheet. The status quo costs nothing to run and produces no data. It works fine below roughly 30 requests a month and degrades in a specific way above it: the queue stays manageable while the reporting becomes fiction, because nobody backfills a spreadsheet on a busy week. If you only want faster routing, a rota and a good autoresponder gets you most of the way. Build this when someone asks legal to justify headcount and the honest answer is that you do not know what you did last quarter.
vs a legal front door product.Checkbox and Streamline AI both sell exactly this as a product, with a form builder, a workflow designer, and reporting already in the box. Both are quote-gated — neither published a starting price as of their August 2026 pricing check — which puts them in an enterprise procurement cycle rather than an afternoon. They are the better choice if legal ops has budget and no engineering support, and if a vendor-shaped taxonomy fits your request mix. This flow is the better choice when your routing rules encode something specific about your business — a signature-authority threshold, a business unit that always needs a lawyer, a category of counterparty you refuse to self-serve — because those rules live in a Code node you own rather than in a configuration screen you negotiate for.
vs a ticket queue you already own. Jira Service Management, ServiceNow, or Zendesk can all take a legal request form today, at no new licence cost if IT already runs one. What they route on is form fields: the requester picks “contract review,” and the ticket goes to the contract review queue. That works precisely as well as your requesters’ self-classification, which is the thing intake data consistently shows is unreliable — the request-type hint in this flow is passed to the model explicitly labelled as self-declared and possibly wrong. Use the ticket system if your request mix is narrow and your form can enumerate it. Use this when the deciding information is in a paragraph of free text.
Watch-outs
Self-serve becomes a deflection wall. Failure mode: requesters get a template link, the template does not answer their actual question, and they route around legal into DMs — where the work still happens but stops being counted. Guard: the weekly report’s recontact query counts self-serve answers where the same person came back within seven days, and flags above 15%. The self-serve Slack reply also ends with an explicit escape hatch — reply in the thread and it goes to the review queue, no new form.
Taxonomy drift routes new work to lawyers. Failure mode: a new regulation or product line produces requests that fit none of the twelve types, land as other, and default into the lawyer lane, so the queue grows while the model looks like it is working. Guard: the report ranks request_type = 'other' and flags above 10% of volume, with the instruction that this is a missing type in the taxonomy rather than a model failure. Add it to the system prompt in Claude — Classify + Route.
Privileged content reaches the API. Failure mode: someone forwards a thread containing litigation context with a routine contract attached, and the whole thread goes into an inference request. Guard: PRIVILEGE_PATTERNS diverts eight categories before any API call, and Normalize Request caps the body at 4,000 characters so a long forwarded thread is truncated regardless. Pair the flow with a written AI policy for legal teams that authorizes the data flow explicitly, and re-run verification test 4 in the README after every edit to those patterns.
The SLA clock counts the wrong hours. Failure mode: elapsed time is computed in calendar hours, breach alerts fire overnight and at weekends for requests comfortably inside their window, and the team mutes the channel within a fortnight. Guard: Compute Breach Tier counts only Monday–Friday between BUSINESS_START and BUSINESS_END, and the sweep itself is cron-limited to weekday business hours. Verification test 6 in the README exists specifically to catch a timezone mismatch between the workflow setting and those constants.
The channel norm never forms. Failure mode: the form exists, and people email the GC directly anyway, because the first request is always sent the way it has always been sent. Guard: the legal-intake@ mailbox trigger catches them, and the report’s email-share threshold makes the gap visible as a number rather than a feeling. Pair it with autoresponders on individual lawyer mailboxes for the first 30 days. If email share is still above 30% at day 90, the problem is organizational and no node edit will fix it.
Stack
n8n for orchestration, Claude Sonnet 5 for classification, Slack for every queue and the weekly report, Ironclad or your own CLM for the playbook lane’s matter record, Postgres for the directory, the log, and the SLA policy, and Gmail for the catch-net mailbox. The concepts behind the routing rules are in legal intake, and where this sits on the capability curve is in the legal ops maturity model. Once a request is routed, the contract review SOP governs what the playbook lane actually does.
# Legal Request Intake Router — n8n
One front door for every legal request. Two entry points (a form webhook and a `legal-intake@` mailbox) converge into one normalized envelope, get classified against a twelve-type taxonomy, and route into one of five lanes — self-serve, playbook review, lawyer, awaiting-requester, or GC escalation. An hourly sweep chases the SLA clock; a Monday report tells you what the department was actually asked for last week.
**Files**
| File | What it is |
|---|---|
| `legal-request-intake-router-n8n.json` | The workflow export. 26 nodes, three trigger-rooted branches. |
| `schema.sql` | Three Postgres tables. Run this first. |
| `_README.md` | This file. |
---
## 1. Import
1. Run `schema.sql` against your Postgres database. It is idempotent — `CREATE TABLE IF NOT EXISTS` throughout, and the five `legal_sla_policy` rows use `ON CONFLICT DO NOTHING`.
2. In n8n: **Workflows → Import from File →** `legal-request-intake-router-n8n.json`.
3. Open **Workflow settings** and set the timezone. The export ships `Europe/London`. Every cron expression in the file (`0 9-18 * * 1-5` for the SLA sweep, `0 8 * * 1` for the report) reads that setting, and so does the business-hours arithmetic in `Compute Breach Tier`. Setting it wrong does not throw — it silently shifts every SLA deadline.
4. Bind the five credentials by name (next section). The export references them as `PLACEHOLDER_*` ids, which n8n shows as unbound until you map them.
5. Leave the workflow **inactive** until you have run the verification sequence in section 3.
---
## 2. Credentials
Five, one section each.
### `PLACEHOLDER_POSTGRES_CRED_ID` — Postgres (type: Postgres)
The database holding the three tables from `schema.sql`. Used by five nodes. The workflow needs `SELECT`, `INSERT`, and `UPDATE` on `legal_request_log`, `SELECT` on `requester_directory` and `legal_sla_policy`. It never needs `DELETE` or DDL — grant accordingly.
### `PLACEHOLDER_ANTHROPIC_CRED_ID` — Anthropic (type: Header Auth)
- **Name:** `x-api-key`
- **Value:** your Anthropic API key, from [console.anthropic.com](https://console.anthropic.com) → API Keys.
Used only by `Claude — Classify + Route`. The `anthropic-version: 2023-06-01` header is set on the node itself, not in the credential.
### `PLACEHOLDER_SLACK_CRED_ID` — Slack (type: Header Auth)
- **Name:** `Authorization`
- **Value:** `Bearer xoxb-...` — a bot token from your Slack app's **OAuth & Permissions** page.
Scopes required: `chat:write` and `chat:write.public`. Invite the bot into `#legal-ops`, `#legal-queue`, `#legal-lawyer-queue`, and `#legal-gc-escalations` before the first run; a post to a channel the bot is not in returns `not_in_channel` with HTTP 200, so it fails quietly.
The two requester-facing nodes (`Slack — Self-Serve Reply`, `Slack — Ask For Missing Fields`) derive a Slack handle from the email local part. If your handles do not match your email prefixes, replace that expression with a `users.lookupByEmail` call and add the `users:read.email` scope.
### `PLACEHOLDER_CLM_CRED_ID` — Ironclad (type: Header Auth)
- **Name:** `Authorization`
- **Value:** `Bearer ...` — an Ironclad API token with workflow-create permission.
Used by `CLM — Open Playbook Matter` only. The node's `template` value (`legal-playbook-review`) and its attribute names are **per-tenant** — read them off your own workflow designer and edit the node body. If you do not have a CLM, disable this node; the playbook lane still posts to Slack and still writes its audit row.
### `PLACEHOLDER_GMAIL_CRED_ID` — Gmail (type: Gmail OAuth2)
The dedicated `legal-intake@` mailbox — a shared mailbox, never an individual lawyer's inbox. Used by `Intake Mailbox Poll — legal@` and `Mark Email Processed`.
### `PLACEHOLDER_WEBHOOK_ID_LEGAL_INTAKE`
Not a credential. n8n assigns a real webhook id on import; copy the production URL from the `Intake Form Webhook` node and point your intake form at it. Expected JSON body:
```json
{
"submission_id": "form-2026-08-18-0042",
"requester_email": "jane@acme.com",
"business_unit": "EMEA Sales",
"request_type_hint": "vendor_contract",
"summary": "Renewal of the Datadog MSA",
"detail": "Free-text description of what they need and by when.",
"counterparty": "Datadog Inc.",
"claimed_value_usd": 84000,
"needed_by": "2026-09-05"
}
```
Only `submission_id` and `requester_email` are load-bearing. `Normalize Request` defaults everything else, and a submission with no requester email is force-routed to the GC channel rather than guessed at.
---
## 3. First-run verification
Run these six in order, with the workflow **inactive**, using **Execute Workflow** and pinned test data on the trigger node. Each one proves a different branch. Do not activate until all six pass.
### Test 1 — the happy path, self-serve lane
Pin a webhook body for a standard NDA from a requester you have inserted into `requester_directory` with `risk_posture = 'standard'`. Expect: `Lane Switch` takes output 1, the requester gets a Slack DM with a template link, and one row lands in `legal_request_log` with `lane = 'self_serve'` and `override_reason IS NULL`.
This is the only test where an automated answer goes out. If it routes anywhere else, check that your directory row actually matched — `SELECT * FROM requester_directory WHERE lower(match_value) = lower('jane@acme.com')`.
### Test 2 — the unknown requester is not self-served
Same body, but change `requester_email` to an address with no directory row and no matching domain. Expect: `lane = 'playbook'` and `override_reason = 'risk_posture_unknown'`. This proves that `Merge Requester Context` defaults a missing row to `unknown` rather than `standard` — the guard that stops an unrecognised sender receiving an automated legal answer.
### Test 3 — the walk-away override beats the model
Pin a body describing an employment matter with the word `termination` in the detail (but not in the subject, so the privilege gate does not catch it first). Expect: whatever Claude proposed, `lane = 'gc_escalation'` and `override_reason` starts with `walk_away_flag:`. Check the `#legal-gc-escalations` post arrived.
### Test 4 — the privilege gate skips the model entirely
Pin a body with `"summary": "Subpoena received from the state AG"`. Expect: `Privileged-Content Gate` takes its TRUE branch, `Claude — Classify + Route` **never executes** (confirm in the execution view — the node should be untouched, not merely fast), and the GC channel post says explicitly that no classification was run.
This is the test that proves privileged content cannot reach the Anthropic API through the normal path. Re-run it after every edit to `PRIVILEGE_PATTERNS`.
### Test 5 — parse failure escalates rather than failing open
Temporarily edit `Claude — Classify + Route` to point at `https://api.anthropic.com/v1/messages-broken` so the call returns an error body. Execute. Expect: `Apply Routing Policy` catches it, emits `lane = 'gc_escalation'` with `override_reason` starting `parser_error:`, and a human gets the request unread. **Restore the URL afterwards.**
This is the test most teams skip and most regret. A classifier that fails open is worse than no classifier, because the failure is invisible.
### Test 6 — the SLA sweep counts business hours, not calendar hours
Insert a row directly:
```sql
INSERT INTO legal_request_log (source_message_id, source, requester_email, request_type, lane, sla_business_hours, received_at)
VALUES ('sla-test-1', 'form', 'jane@acme.com', 'vendor_contract', 'playbook', 16, now() - interval '3 days');
```
Execute the `SLA Sweep — Hourly Weekdays` branch. Expect one escalation post naming an elapsed figure **lower** than 72, because weekends and nights are excluded. If it reports something close to 72, your workflow timezone and `BUSINESS_START`/`BUSINESS_END` in `Compute Breach Tier` disagree. Then re-execute immediately: the second run must post **nothing**, because `Record Escalation Tier` wrote the tier back. Delete the test row when done.
### Optional — the weekly report on an empty week
Execute the `Weekly Demand Report — Mon 08:00` branch against an empty table. It should post the "no requests logged" message rather than dividing by zero.
---
## 4. What to tune, and when
Ship with the defaults. Change them after a quarter of real traffic, not before.
| Setting | Node | Default | Change it when |
|---|---|---|---|
| `CONFIDENCE_FLOOR` | Apply Routing Policy | `0.75` | The weekly report shows more than ~25% low-confidence and sampling proves they were genuinely routable. |
| `VALUE_ESCALATION_USD` | Apply Routing Policy | `250000` | Your signature-authority matrix says a different number. It should match that document, not a round figure. |
| `WALK_AWAY_FLAGS` | Apply Routing Policy | 6 flags | Never shrink this list to reduce escalation volume. Fix the taxonomy or the intake form instead. |
| `PRIVILEGE_PATTERNS` | Normalize Request | 8 patterns | Add to it freely. Every addition costs you one unclassified request and buys certainty about a category of content. |
| `BUSINESS_START` / `BUSINESS_END` | Compute Breach Tier | `9` / `18` | Your team is not on a single working day — split by `region` from the directory if you support follow-the-sun. |
| SLA hours per lane | `legal_sla_policy` table | 16 / 40 / 8 / 8 | Your published service catalog says otherwise. The table is the right place to change it; no node edit needed. |
| Report thresholds | Format Demand Report | 15 / 10 / 30 / 20 / 25 % | After a quarter, set each to the level your team actually treats as a problem. |
---
## 5. Known limits
1. **The classification is a routing decision, never legal advice.** The system prompt states this and the self-serve reply points at a template rather than answering. Do not extend the prompt to answer the underlying question.
2. **Attachments are not read.** `has_attachment` is a boolean the classifier can use as a signal; the file itself is never sent. For clause-level review of an attached contract, this router hands off to a per-contract-type flow — the NDA triage flow is the worked example.
3. **The recontact metric is a proxy.** It counts any later request from the same person within seven days, so a requester with two unrelated matters registers as a recontact. It is directionally right at the volumes this report is read at; treat a spike as a prompt to read five threads, not as a measurement.
4. **`Mark Email Processed` uses `markAsRead`.** If your `legal-intake@` mailbox has other readers, switch it to `addLabels` with a dedicated `legal-intake-processed` label and rely on the trigger filter (already set to `-label:legal-intake-processed`) for deduplication.
5. **Not runtime-tested against a live Anthropic, Slack, Ironclad, or Gmail tenant.** The workflow JSON is complete and every Code node's logic has been exercised against the routing cases in section 3, but the HTTP node bodies are written from published API shapes and should be verified against your own tenant during the first-run sequence.
-- legal-request-intake-router-n8n — schema
-- Run this once against the Postgres database bound to PLACEHOLDER_POSTGRES_CRED_ID
-- before importing the workflow. Three tables: who is allowed to ask, what was
-- asked, and how fast each lane is expected to answer.
-- ---------------------------------------------------------------------------
-- 1. requester_directory
-- Maps a requester's email domain or exact address to their business unit and
-- the unit's standing risk posture. The router degrades gracefully when a
-- requester is missing (posture defaults to 'unknown', which blocks the
-- self-serve lane), so an empty table is safe on day one — but every row you
-- add moves requests out of the lawyer queue.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS requester_directory (
id BIGSERIAL PRIMARY KEY,
match_value TEXT NOT NULL, -- 'jane@acme.com' or '@acme-emea.com'
match_type TEXT NOT NULL -- 'email' | 'domain'
CHECK (match_type IN ('email', 'domain')),
business_unit TEXT NOT NULL,
region TEXT,
risk_posture TEXT NOT NULL DEFAULT 'standard'
CHECK (risk_posture IN ('standard', 'elevated', 'restricted')),
default_assignee TEXT, -- Slack member ID of the unit's named lawyer
notes TEXT,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (match_value, match_type)
);
CREATE INDEX IF NOT EXISTS requester_directory_match_idx
ON requester_directory (match_type, lower(match_value));
-- ---------------------------------------------------------------------------
-- 2. legal_sla_policy
-- One row per lane. The SLA sweep reads these; the router stamps the tier onto
-- each logged request. Hours are BUSINESS hours, not calendar hours — the
-- Compute Breach Tier code node converts using BUSINESS_HOURS.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS legal_sla_policy (
lane TEXT PRIMARY KEY
CHECK (lane IN ('self_serve', 'playbook', 'lawyer',
'awaiting_requester', 'gc_escalation')),
sla_business_hours INTEGER, -- NULL = no clock (self-serve is instant)
escalation_channel TEXT NOT NULL,
description TEXT
);
INSERT INTO legal_sla_policy (lane, sla_business_hours, escalation_channel, description) VALUES
('self_serve', NULL, '#legal-ops', 'Template or policy answer returned at intake. No clock.'),
('playbook', 16, '#legal-queue', 'Standard-paper review against a written playbook. 2 business days.'),
('lawyer', 40, '#legal-lawyer-queue', 'Needs a lawyer''s judgment. 5 business days.'),
('awaiting_requester', 8, '#legal-ops', 'Blocked on the requester supplying named missing fields.'),
('gc_escalation', 8, '#legal-gc-escalations', 'Privileged, litigation, or regulator-facing. 1 business day, GC-visible.')
ON CONFLICT (lane) DO NOTHING;
-- ---------------------------------------------------------------------------
-- 3. legal_request_log
-- The audit trail, the SLA clock, and the only honest source for the weekly
-- demand report. source_message_id is the idempotency key: n8n retries on
-- transient Postgres errors and you do not want a duplicate row (or a duplicate
-- Slack post) for one request.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS legal_request_log (
id BIGSERIAL PRIMARY KEY,
source_message_id TEXT NOT NULL UNIQUE, -- Gmail message id, or form submission id
source TEXT NOT NULL -- where it actually arrived from
CHECK (source IN ('form', 'email', 'backfill')),
requester_email TEXT NOT NULL,
business_unit TEXT,
risk_posture TEXT,
request_type TEXT NOT NULL, -- from the taxonomy in the Claude prompt
lane TEXT NOT NULL
REFERENCES legal_sla_policy (lane),
model_lane TEXT, -- what Claude said, before overrides
override_reason TEXT, -- why the policy node disagreed, if it did
confidence NUMERIC(4,3),
sla_business_hours INTEGER,
risk_flags TEXT[] NOT NULL DEFAULT '{}',
missing_fields TEXT[] NOT NULL DEFAULT '{}',
claimed_value_usd NUMERIC(14,2),
assignee TEXT, -- Slack member ID
status TEXT NOT NULL DEFAULT 'open'
CHECK (status IN ('open', 'awaiting_requester', 'closed')),
last_escalated_tier INTEGER NOT NULL DEFAULT 0, -- 0 none, 1 = 50%, 2 = 100%, 3 = 150%
received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
first_touch_at TIMESTAMPTZ,
closed_at TIMESTAMPTZ,
recontacted_within_7d BOOLEAN -- backfilled by the weekly report job
);
CREATE INDEX IF NOT EXISTS legal_request_log_open_idx
ON legal_request_log (status, lane, received_at)
WHERE status <> 'closed';
CREATE INDEX IF NOT EXISTS legal_request_log_received_idx
ON legal_request_log (received_at DESC);
CREATE INDEX IF NOT EXISTS legal_request_log_requester_idx
ON legal_request_log (lower(requester_email), received_at DESC);