Internal Launch Industries documentation. Do not share outside the team.
SOPs
Registered Agent
Secretary of State Pipeline

One-line summary: How Launch tracks every WA Registered-Agent client's annual-report deadline — what runs, where, when, who watches it, and how to fix it when something looks wrong.

Washington SOS Annual-Report Tracking Pipeline

SOP status: Live as of 2026-05-16. Owner: xVICTORx (code) + Lorena (task execution). Escalation: Monica.

What this pipeline does (plain English)

Every Washington-formed Launch client has an annual-report deadline at the Secretary of State. Missing one is a real problem — late fees, possible administrative dissolution, and an angry client. Launch is the Registered Agent for most of these companies, so we need to know each deadline before the client does.

This pipeline does four things:

  1. Pulls live data weekly from the WA SOS public site for every client UBI we know about.
  2. Stores the truth in Supabase, on the companies table — specifically companies.sos_expiration_date. That column is the single source of truth.
  3. Emails Monica a weekly digest every Monday with anything that needs eyes.
  4. Opens ClickUp alerts for Lorena at fixed milestones before each deadline (90 / 60 / 30 / 14 / 3 days out).

Plus a CRM page at crm.launchindustries.biz/sos-issues where Lorena or Monica can mark items "resolved" so they stop pestering until SOS state actually changes again.

How it works (architecture)

                   ┌──────────────────────────────┐
                   │ finditconsumer.wa.gov        │
                   │ /corps/search_detail.aspx    │  <-- WA SOS public detail endpoint
                   │   ?ubi=...                   │      (per-UBI, no auth, no Turnstile)
                   └─────────────┬────────────────┘
                                 │
                                 ▼  Mondays 14:00 UTC
                   ┌──────────────────────────────┐
                   │ cron: sos-pipeline-weekly    │  loops every companies.wa_ubi_number
                   │ → public.sos_run_pipeline()  │  stages rows in sos_corporations_raw
                   └─────────────┬────────────────┘
                                 │  null-preserving merge
                                 ▼
                   ┌──────────────────────────────┐
                   │ public.companies             │  <-- sos_expiration_date is THE truth
                   │  - sos_expiration_date       │      sos_status, sos_verified_at,
                   │  - sos_status                │      physical_address_*
                   │  - physical_address_*        │
                   └─────────────┬────────────────┘
                                 │
                  ┌──────────────┼──────────────────────────────┐
                  │              │                              │
                  ▼              ▼                              ▼
   ┌──────────────────────┐  ┌────────────────────────┐  ┌────────────────────────┐
   │ cron:               │  │ cron:                  │  │ CRM page:              │
   │ sos-weekly-digest-  │  │ annual-report-alerts-  │  │ crm.launchindustries   │
   │ email               │  │ nightly                │  │ .biz/sos-issues        │
   │                     │  │                        │  │                        │
   │ Mondays 15:00 UTC   │  │ Daily 08:30 UTC        │  │ Lorena/Monica resolve  │
   │ → sos-weekly-digest │  │ → opens/comments on    │  │ items; suppression     │
   │   edge function     │  │   ClickUp tasks at     │  │ writes to              │
   │ → emails            │  │   D-90/60/30/14/3      │  │ sos_issue_overrides    │
   │   registeredagent@  │  │   (Lorena owner,       │  │ (owned by separate CRM │
   │   launchindustries  │  │   Monica watcher)      │  │  build — don't edit    │
   │   .biz              │  │                        │  │  from this codebase)   │
   └──────────────────────┘  └────────────────────────┘  └────────────────────────┘

Companion: how new clients enter the pipeline

New clients enter through the Nutshell sync (ADR-0003):

Nutshell account (with WA UBI # custom field)
    │
    ▼  every 5 min
public.nutshell-companies-sync-paginated  (JSON-RPC getAccount for custom fields)
    │
    ▼  writes wa_ubi_number into companies
public.companies.wa_ubi_number
    │
    ▼  next Monday at 14:00 UTC
sos-pipeline-weekly picks it up automatically

Translation: a new RA client gets a UBI added to their Nutshell account → within 5 minutes the UBI is in Supabase → next Monday the SOS pipeline pulls their deadline → it shows up in alerts and the weekly digest.

Source-of-truth policy (Monica's call, 2026-05-16)

companies.sos_expiration_date is the only trusted source for SOS expiration dates. Full stop.

Nutshell has a custom field called "SOS Expiration." It is provably unreliable — KBND and Dexpro were both 365 days stale there because no one updated Nutshell after renewing. ADR-0003 explicitly excludes sos_expiration_date from the Nutshell write set. ADR-0002 establishes companies.sos_expiration_date as authoritative.

If a human ever asks "should I update Nutshell's SOS Expiration field?" — the answer is no. Don't. It's deprecated. The SOS pipeline owns this column; everything else reads from it.

Where to change things (runbook)

"I want to run the SOS pipeline manually right now"

-- Respects the 6-day cooldown (won't re-pull if last run was recent)
SELECT public.sos_run_pipeline();
 
-- Force-run, ignoring cooldown
SELECT public.sos_run_pipeline(p_force => true);
 
-- Force-run for specific companies only (by companies.id)
SELECT public.sos_run_pipeline(
  p_force => true,
  p_trigger => 'manual',
  p_only_ids => ARRAY[1075, 567]::integer[]
);

"I want to re-send the weekly digest email right now"

SELECT net.http_post(
  url := 'https://egnrizeybeihfavzwxfe.supabase.co/functions/v1/sos-weekly-digest',
  headers := jsonb_build_object('Content-Type', 'application/json'),
  body := '{}'::jsonb
);

"I want to change the cron schedule"

-- Find the job
SELECT jobid, jobname, schedule, active FROM cron.job WHERE jobname LIKE 'sos%';
 
-- Unschedule (use the name)
SELECT cron.unschedule('sos-pipeline-weekly');
 
-- Re-schedule with a new cron expression
SELECT cron.schedule(
  'sos-pipeline-weekly',
  '0 14 * * 1',  -- Mondays 14:00 UTC
  $$ SELECT public.sos_run_pipeline(); $$
);

"I want to change who gets the digest, or the email format"

Edit the sos-weekly-digest edge function source. Deploy with the Supabase CLI or via the dashboard.

"I want to change which clients qualify for the digest"

Edit the public.sos_weekly_digest_data() Postgres function. Heads up: the CRM SOS Issues build is editing this function and public.sos_issue_overrides concurrently — coordinate with the CRM owner before changing it.

"I want to change the alert cadence (D-90/60/30/14/3)"

Edit public.annual_report_alerts_decide_windows.

"I want to change which ClickUp list / assignee gets the tasks"

Edit the annual-report-alerts edge function source (the routing logic) or the wiring in public.annual_report_alerts_run().

Who watches what

RoleResponsibility
LorenaOwns every <Client> - SOS Renewal ClickUp task. Acts on alerts. Files the actual annual report at SOS.
MonicaEscalation watcher. Gets the weekly digest email. Resolves edge cases via the CRM SOS Issues page.
xVICTORxMaintains the pipeline code (SQL functions, edge functions, cron jobs).
xKAYLAxOwns the ClickUp routing layer (lists, assignees, templates).

Cycle rollover is human-confirmed. The system never auto-closes a ClickUp task. After a filing is confirmed at SOS, Lorena (or Monica) closes the task; the next year's task is created on the next pipeline run that detects a new sos_expiration_date.

Common issues and how to fix them

"The digest email didn't arrive Monday"

  1. Did the cron job fire? 
    SELECT jobid, jobname, schedule, active FROM cron.job WHERE jobname = 'sos-weekly-digest-email';
    If active is false, that's your answer — re-enable.
  2. Did the HTTP call go out? 
    SELECT id, status_code, content, created
FROM net._http_response
ORDER BY id DESC
LIMIT 10;
  3. If status_code is 200 but no email, check the Resend dashboard for the function's outbound message. If status_code is non-200, check the edge function logs in Supabase.

"The SOS pipeline didn't run"

SELECT * FROM public.sos_pipeline_runs ORDER BY id DESC LIMIT 5;

Each row shows when the pipeline ran, how many UBIs were processed, how many succeeded, how many failed, and the trigger (cron / manual). No recent row → cron job is off or the function errored before logging.

"A client shows as expired but I know they filed"

Their row hasn't been re-fetched yet. Force a refresh for just their companies.id:

SELECT public.sos_run_pipeline(
  p_force => true,
  p_only_ids => ARRAY[<id>]::integer[]
);

Wait ~30 seconds, then re-check companies.sos_expiration_date.

"I marked an item handled in the CRM but it keeps alerting"

The CRM SOS Issues page resolves an item by writing to sos_issue_overrides. If SOS state changes after that (new fetch surfaces a new status), the override is invalidated — that's by design. Look at the override row to see when it was created and the underlying companies.sos_status value at the time vs. now. If they differ, the pipeline saw a real change and re-surfaced the item.

"A client's UBI is in Nutshell but they're not showing up in the pipeline"

The Nutshell sync runs every 5 minutes but only writes companies.wa_ubi_number after a successful getAccount JSON-RPC call. Verify:

SELECT id, legal_entity_name, wa_ubi_number, nutshell_id
FROM public.companies
WHERE legal_entity_name ILIKE '%<client name>%';

If wa_ubi_number is null but the Nutshell record clearly has a UBI custom field, see ADR-0003 and Monica's Inbox/2026-05-16_victorx-nutshell-custom-field-backfill-results.md.

"WA SOS returned an error fetching one of our clients"

SELECT * FROM public.sos_pipeline_runs ORDER BY id DESC LIMIT 1;
-- look at the failures count, then:
SELECT * FROM public.sos_corporations_raw
WHERE pipeline_run_id = <latest run id>
  AND http_status >= 400
ORDER BY ubi;

Common causes: malformed UBI in Launch's data (extra spaces, wrong length); client dissolved or admin-dissolved at SOS (their record returns 404); a transient 5xx from WA SOS (retry next week is fine — these are usually one-off).

Related ADRs and source documents

  • ADR-0002 — WA SOS data feed: Launch's Data Room/ADRs/0002-wa-sos-bulk-data-feed.md. The pipeline architecture, why we use search_detail.aspx instead of CCFS, the staging-table merge pattern.
  • ADR-0003 — Nutshell custom-field sync: Launch's Data Room/ADRs/0003-nutshell-custom-field-sync.md. How new UBIs enter the universe. Why sos_expiration_date is excluded from Nutshell writes.
  • Active project record — Supabase: public.active_projects WHERE slug = 'sos-address-pipeline'. Current state, open follow-ups, files touched.
  • Build reports — Monica's Inbox/2026-05-16_victorx-*.md (pipeline build, Nutshell backfill, address backfill, ADR summaries).

Revisit triggers

Update this SOP — or write a new one and supersede this — when any of the following happens:

  1. WA SOS retires search_detail.aspx or starts gating it behind Cloudflare Turnstile. Falls back to Option C in ADR-0002 (browser scraper) or a public-records-request cadence.
  2. The cron schedule changes away from Monday weekly. If the digest moves to daily or to a different day, update the "When things run" section.
  3. The alert cadence changes away from D-90/60/30/14/3.
  4. Launch crosses ~500 active WA-formed RA clients. At that volume, per-UBI fetch loops start mattering — would graduate to a different fetch pattern.
  5. Lorena or Monica's role on this changes. Update the "Who watches what" table.

Maintained by xVICTORx. Last updated 2026-05-16.

Email SignatureInbox Assistant