USE_CASE_ID: account_scoring_with_cooldown_digest
NAME: Account scoring with cooldown Slack digest
CATEGORY: Account research and scoring

The user runs a list of target accounts through a scoring chain on a
recurring basis and wants a Slack digest of new high-tier accounts —
*new* meaning not already notified within a recent cooldown window
(typically 30 days). Same accounts may re-qualify over time as signals
shift, but they shouldn't be re-notified every run.

This is the recurring-monitoring sibling of
account_research_and_scoring (which is a one-shot batch-scoring use
case). The scoring chain itself can be identical; this use case adds
the digest delivery and the cooldown / exclusion-list mechanic.

For routing, file structure, and cross-cutting principles, see
https://floqer.com/docs/use-case-catalog.txt. This file does not repeat them.

INDEX:
  1. When to use / when not to use
  2. Inputs and pre-flight clarifications
  3. Outputs
  4. Workflow design
  5. Implementation
  6. Best practices
  7. Common variations
  8. Failure modes and mitigations
  9. Related use cases

================================================================================
1. WHEN TO USE / WHEN NOT TO USE
================================================================================

USE WHEN:
  - The account list is monitored on a recurring schedule (daily,
    weekly) and signals shift over time.
  - The user wants a digest notification — not a per-account write to
    a sequencer or CRM — when accounts cross into a high tier.
  - The same account may re-qualify across runs, but the user does
    not want it surfaced again within a recent cooldown window.
  - "Top 5% / top N" or threshold-based selection is acceptable — the
    digest is a curated short list, not the full ranked output.

DO NOT USE WHEN:
  - One-shot batch scoring with no recurrence — see
    account_research_and_scoring. No need for a digest sheet or
    cooldown log.
  - The trigger is per-account (a hiring post, a website visit) and
    the action is per-account outreach — see intent-driven outreach.
    This use case batches the day's qualifiers into one digest, not
    one-touch alerts.
  - The user wants every qualifying account written to a sequencer
    or CRM directly — see ICP-based outbound prospecting. This use
    case is a human-in-the-loop notification, not automated outreach.
  - The cooldown window needs to be per-recipient or per-channel
    (different reps see different accounts at different cadences).
    The single-log pattern below assumes one global cooldown. Adapt
    the log schema to add a recipient column if needed.

================================================================================
2. INPUTS AND PRE-FLIGHT CLARIFICATIONS
================================================================================

INPUTS
  - Target account list — at minimum account_id (unique key),
    company_name, company_domain, vertical/segment.
  - Signal definitions — what to score on. Can be fabricated for a
    demo or driven by a real research action (llm_web_agents,
    enrichment, headcount-by-job-role, tech-stack scrape).
  - Tier thresholds — e.g. T1 ≥ 90, T2 ≥ 70, T3 < 70. Calibrate so
    T1 is roughly the top 5–10% of the list size you expect; the
    digest is most useful when it surfaces a handful per run.
  - Cooldown window — typically 30 days for "warm" workflows, 60–90
    for slow-moving accounts where re-mention is noisy.
  - Slack channel for the digest. Requires Slack connection on the
    Floqer Connections page; cannot be configured by API.

PRE-FLIGHT CLARIFICATIONS
  - Run cadence: daily, weekly, ad hoc? The digest sheet has no
    external data trigger — its rows are hardcoded recipients, and
    a scheduler reruns them on the chosen cadence by calling
    `POST /sheets/{digest_sheet}/run-all` (or per-row equivalents).
  - Recipient model: who gets pinged, and on what channel? The
    digest sheet has ONE ROW PER RECIPIENT (one for each rep / DM
    target / channel). Each row's inputs hardcode that recipient's
    slack target and, optionally, an account-owner filter so each
    rep only sees their own accounts. If there's a single
    org-wide channel, the sheet has one row.
  - Account list size: drives whether to paginate the digest's
    cross-sheet lookups. Up to 200 rows fits in one /rows/list page;
    beyond that, chain pages.
  - Whether T2 also gets surfaced (in a separate bucket, with longer
    cooldown) or only T1.
  - Whether "mentioned" means "appeared in a digest" or "actioned
    by a rep". The cooldown log can model either; default below is
    "appeared in a digest".

================================================================================
3. OUTPUTS
================================================================================

  - A per-account score (0–100) and tier (T1/T2/T3) on the scoring
    sheet, persisted as action-output cells per row.
  - A per-recipient Slack message listing only new high-tier
    accounts (optionally narrowed to the recipient's owned accounts)
    that haven't been surfaced within the cooldown window. The
    digest sheet has one row per recipient; each row's chain ships
    that recipient's personalized digest.
  - A Mentioned Accounts log of every (account_id, mentioned_at)
    pair pushed by the digest — used both for the next run's
    cooldown lookup and as an audit trail. Optionally scope per
    recipient (see Failure modes).

================================================================================
4. WORKFLOW DESIGN
================================================================================

Three sheets in one workflow:

  Scored Accounts (main sheet)
    Inputs: account_id (number), company_name (string),
            company_domain (url), vertical (string).
    Chain : score every account; tier; drop junk.

  Mentioned Accounts (cooldown log, no chain)
    Inputs: account_id (number), mentioned_at (string ISO date),
            marker (string — always "active", used as the
            lookup-all key from the digest).
    Pure data sink. Rows are written by the digest's
    push_data_to_sheet step.

  Slack Digest (recipient roster — one row per pinged person)
    Inputs (hardcoded per row, never change between runs):
      recipient_name    (string, optional — display label)
      slack_target      (string — channel id or DM user id)
      account_owner     (string, optional — used to filter Scored
                         Accounts to just this recipient's accounts;
                         omit when one channel sees all accounts)
    cache_enabled: false (mandatory — see below).
    Chain : compute today's date, look up qualifying accounts, look
            up recent mentions, set-difference, build digest message,
            send Slack, log new mentions.

    Trigger model: rows here are permanent and hardcoded. The
    workflow re-runs the same rows on a schedule via the API
    (`POST /sheets/{digest_sheet}/run-all`, or per-row Run Rows
    from a scheduler / cron). No new rows are added per run. Any
    value that varies per run (today's date, time of day, etc.) is
    computed inside the chain by a JS formula, NOT passed as an
    input.

    Why cache_enabled MUST be false: with hardcoded inputs that
    never change, Floqer's row-level cache would otherwise reuse the
    previous run's outputs and the digest would freeze. Turning
    cache off forces a fresh execution on every re-trigger.

Cross-sheet reads in the Slack Digest chain use one of two
equivalent primitives — pick based on scale:

  - `lookup_another_floqer_workflow_row` (native lookup) — best
    when the scoring sheet is large (thousands+) and the T1 match
    rate is sparse. The action returns only matching rows, so the
    payload stays small. Cooldown sheet lookup uses the `marker`
    input column with `find_all: true`. See Section 5 Stage 3
    Option A for the full configuration.

  - `http_api_call` -> `/rows/list` — best when the scoring sheet
    is small-to-medium (up to a few thousand rows) and/or you're
    authoring purely via API. Returns every row in the page (up to
    200); downstream JS filters in-memory. Pattern fully documented
    in https://floqer.com/docs/use-case-detail/cross_floqer_table_lookup.txt.

  Both feed the same downstream JS. Section 5 STAGE 3 documents both
  paths so the choice is local to that stage — the rest of the chain
  is identical.

================================================================================
5. IMPLEMENTATION
================================================================================

QUICK REFERENCE — action chain per sheet (annotated with display names):

  SCORED ACCOUNTS  (main)
    inputs: account_id, company_name, company_domain, vertical
    chain:
      <research / signal collection>    "Account Research"          // placeholder — typically llm_web_agents, enrichment actions, or a JS formatter projecting upstream research; see Stage 1
      format_data_using_js_expression   "Score (0-100)"
      format_data_using_js_expression   "Tier (T1 / T2 / T3)"
      filter                            "Drop T3 junk"

  MENTIONED ACCOUNTS  (log sink — no chain)
    inputs: account_id, mentioned_at, marker

  SLACK DIGEST  (cache_enabled: false — one row per recipient, rows hardcoded, re-run on schedule)
    inputs: recipient_name, slack_target, account_owner (optional)
    chain:
      format_data_using_js_expression      "Today (ISO date — computed at runtime)"
      lookup_another_floqer_workflow_row   "Fetch T1 Accounts"        // OR http_api_call → /rows/list — see Section 4
      lookup_another_floqer_workflow_row   "Fetch Recent Mentions"    // OR http_api_call → /rows/list
      format_data_using_js_expression      "Apply Cooldown (filter by tier + per-recipient owner; subtract recent mentions)"
      format_data_using_js_expression      "Extract Digest Message"
      format_data_using_js_expression      "Extract Survivor IDs CSV"
      csv_to_structured_array_format       "Survivors CSV → List"
      slack_send_message                   "Send Digest"  (channel/user from {{input.slack_target}})
      push_data_to_sheet                   "Log to Mentioned Accounts"  (mentioned_at from Today formula)

Detailed stages below.

STAGE 1 — Score accounts (Scored Accounts sheet)

  Inputs as above. Chain shape:

    input
      -> <research / signal collection>    (placeholder — see below)
      -> format_data_using_js_expression   (Score 0-100 from signals)
      -> format_data_using_js_expression   (Tier: "T1" / "T2" / "T3")
      -> filter                             (drop "T3")

  Research / signal collection is a PLACEHOLDER, not a specific
  action. Plug in whatever produces the per-account signal data the
  Score step consumes. Typical shapes:

    - One `llm_web_agents` per signal type (funding, leadership,
      product launches, hiring), feeding evidence into a final
      `llm_models` or JS scorer. See
      https://floqer.com/docs/use-case-detail/account_research_and_scoring.txt for
      the full research-chain pattern.
    - Enrichment actions (PDL, store_leads, headcount_by_job_role,
      tech-stack scrape) projecting firmographic / technographic
      signals as structured columns.
    - A `format_data_using_js_expression` projecting upstream
      research outputs into a single per-row JSON blob that Score
      consumes — useful when research happens once upstream and
      is replayed for re-scoring.
    - For a demo / scaffolding run, a `format_data_using_js_expression`
      that fabricates deterministic mock signals from the
      company_name hash. Replace with real research before shipping.

  Filter on tier (a string output) using `is not "T3"`. Do NOT
  filter the score column directly — JS formatter outputs are typed
  string regardless of the JS return value, so numeric filter
  operators (`greater than`, etc.) silently fail. The
  string-sentinel pattern via tier dodges that.

STAGE 2 — Cooldown log (Mentioned Accounts sheet)

  Inputs only — no chain. Rows arrive from STAGE 5's
  push_data_to_sheet.

  Why a `marker` input column: lookups need a column with a
  uniformly-matching value so a "give me all rows" query is possible
  without per-row filtering. `marker = "active"` on every row makes
  `target_column=marker, value="active", find_all=true` return
  everything.

STAGE 3 — Cross-sheet reads (Slack Digest sheet, top of chain)

  Two reads, one per source sheet. Pick the implementation by scale
  (see Section 4 for the choice rubric).

  Option A — `lookup_another_floqer_workflow_row` (native).
    Configure each lookup with `find_all: true`:

      Scored Accounts lookup:
        table_to_search = <scored_sheet_id>
        target_column   = <column_uuid_for_tier>
        operator        = "eq"
        value           = "T1"
        find_all        = true

      Mentioned Accounts lookup:
        table_to_search = <mentioned_sheet_id>
        target_column   = <column_uuid_for_marker>
        operator        = "eq"
        value           = "active"
        find_all        = true

    The `record` output is an array of objects keyed by column
    display name on the target sheet (because `find_all: true`).
    Downstream JS (STAGE 4) reads `r.<input_field>` and
    `r.<action_display_name>` directly off those objects — no
    `r.cells[<aiid>]` indirection needed.

    Best for: large scoring sheets with sparse T1 match rate;
    small payload over the wire; cleaner downstream JS.

    Resolving the column UUID (works as documented):

      POST /api/v1/workflows/{wf}/sheets/{sheet}/actions/{aiid}/options/target_column
      Body: { "context": { "target_table": "<target_sheet_id>" } }

    Pass the chosen `value` into `target_column` on the PATCH body.
    Note: the options-call context key is `target_table` even though
    the PATCH field is `table_to_search` — see the lookup
    action-detail "Key Notes" for the one-line explanation.

  Option B — `http_api_call` -> `/rows/list`.
    Two `http_api_call` actions, both POST to
    /workflows/<wf>/sheets/<sheet>/rows/list with
    `{"page_size": "200"}`:

      Fetch Scored Rows  — endpoint targets Scored Accounts sheet.
      Fetch Mentions     — endpoint targets Mentioned Accounts sheet.

    Run the schema-discovery dance once (see
    https://floqer.com/docs/action-detail/http_api_call.txt section 3) so each call
    exposes `data` and `status` outputs downstream. STAGE 4 then
    filters in JS against `r.cells[<tier_aiid>].outputs.formatted_data`.

    Best for: small-to-medium scoring sheets (up to a few thousand
    rows), API-only authoring, or when you need to read multiple
    output cells per row from the same fetch.
    Caveat: payload returns every row in the page, not just matches,
    so it doesn't scale to large sparse-match sheets the way the
    native lookup does.

STAGE 4 — Apply cooldown (Slack Digest sheet, JS formatter)

  Parse both responses; filter Scored to T1 (optionally narrowed
  to just this recipient's owned accounts); build excluded-set from
  Mentions within the cooldown window; emit survivors + message
  payload. Encode newlines as `__NL__` before stringifying so the
  downstream message extractor can parse cleanly — see
  https://floqer.com/docs/action-detail/format_data_using_js_expression.txt
  §8.3 Chained JSON between formatters.

  Sketch:

    (() => {
      const parseJson = raw => { try { return JSON.parse(raw); } catch (e) { return null; } };
      const scored    = parseJson(`{{<fetch_scored>.data}}`)  || {};
      const mentions  = parseJson(`{{<fetch_mentions>.data}}`) || {};
      const rows      = Array.isArray(scored.rows)   ? scored.rows   : [];
      const ments     = Array.isArray(mentions.rows) ? mentions.rows : [];

      // Per-recipient owner filter — hardcoded on this row.
      // Set this row's account_owner input to "" / null when one
      // recipient should see every owner's accounts.
      const owner = "{{input.account_owner}}".trim();

      const TIER_KEY = "<tier_action_instance_id>";
      const t1 = rows.filter(r => {
        const tierCell = r.cells && r.cells[TIER_KEY];
        const isT1 = tierCell && tierCell.outputs && tierCell.outputs.formatted_data === "T1";
        if (!isT1) return false;
        if (!owner) return true;                                    // no filter — surface all T1
        return (r.inputs && r.inputs.account_owner) === owner;      // require owner match
      });

      const cutoffMs = Date.now() - 30 * 86400e3;
      const excluded = new Set();
      for (const m of ments) {
        const id = String(m.inputs && m.inputs.account_id || "");
        const at = m.inputs && m.inputs.mentioned_at;
        const t  = at ? new Date(at).getTime() : NaN;
        if (id && !isNaN(t) && t >= cutoffMs) excluded.add(id);
      }

      const survivors = t1
        .filter(r => !excluded.has(String(r.inputs && r.inputs.account_id || "")))
        .map(r => ({
          account_id:   String(r.inputs.account_id),
          company_name: r.inputs.company_name,
          score: r.cells["<score_action_instance_id>"].outputs.formatted_data
        }));

      const lines = survivors.length === 0
        ? "(no new T1 accounts today)"
        : survivors.map(s => "• " + s.company_name + " — score " + s.score).join("__NL__");
      const recipient = "{{input.recipient_name}}";
      const header = recipient
        ? "*Daily T1 Watch — " + recipient + "*__NL__"
        : "*Daily T1 Watch*__NL__";
      const message = header +
                      survivors.length + " new (after " + excluded.size +
                      " cooldown exclusions, " + t1.length + " total T1):__NL__" + lines;

      const idsCsv = survivors.length === 0
        ? ""
        : survivors.map(s => s.account_id).join(",") + ",";  // trailing comma — see csv_to_structured_array_format.txt key notes

      return JSON.stringify({ survivor_count: survivors.length, message: message, survivor_ids_csv: idsCsv });
    })()

  Note: this JS reads `{{input.account_owner}}` and
  `{{input.recipient_name}}` from the current digest row — that's
  how a single chain handles multiple recipients. Each row in the
  Slack Digest sheet runs the same chain with its own per-recipient
  values.

STAGE 5 — Build digest message + log (Slack Digest sheet, terminal)

  Note: "Today" runs as the FIRST action in the chain (before the
  lookups in Stage 3) so it's available as a single source-of-truth
  reference for every downstream step that needs the current date.
  Its formula is just `new Date().toISOString().slice(0, 10)` — no
  inputs, no upstream dependency.

  After Apply Cooldown, terminal actions:

    -> format_data_using_js_expression  (Extract Message: parse JSON,
                                         decode __NL__ back to \n)
    -> format_data_using_js_expression  (Extract Survivor IDs CSV)
    -> csv_to_structured_array_format   (CSV -> structured list;
                                         input is the IDs CSV)
    -> slack_send_message               (channel = {{input.slack_target}},
                                         message = Extract Message output)
    -> push_data_to_sheet               (fan out survivors into
                                         Mentioned Accounts)

  Push column mapping:
    account_id   -> {{csv_action.list.value}}
    mentioned_at -> {{today.formatted_data}}    (from the Today
                                                 action at the top
                                                 of the chain)
    marker       -> "active"  (literal)

  Slack target wiring: `select_slack_channel` accepts a channel id
  (Cxxx...) or a user DM id (Dxxx...). Hardcode the right value per
  row in the `slack_target` input. If Slack isn't connected on the
  account, replace slack_send_message with a JS formatter that just
  exposes the composed message as a cell output. The architecture
  is unchanged; swap the real action in once OAuth is wired.

================================================================================
6. BEST PRACTICES
================================================================================

  - Calibrate tier thresholds against the list size. Aim for ~5–10%
    in T1 so the digest stays a focused short list.
  - Disable cache on the Slack Digest sheet. Same trigger_date input
    on the same day shouldn't return cached output if the upstream
    data changed during the day.
  - Pick a cooldown that reflects how often signals genuinely refresh
    — 30 days is sensible for funding / hiring signals; shorter when
    you ingest fast-moving intent.
  - Always set page_size: "200" on the cross-sheet http_api_call
    fetches. Default is 20 and silently truncates. For >200 rows,
    chain pages.
  - Pad single-value CSVs with a trailing comma. See
    https://floqer.com/docs/action-detail/csv_to_structured_array_format.txt key notes
    — single-result digests would otherwise push zero rows into the
    cooldown log and the cooldown stops working.
  - Keep the cell-key lookups (`r.cells[<aiid>]`) consolidated in one
    JS step. Replicating them across multiple formatters multiplies
    the maintenance cost when scoring-chain action ids change.

================================================================================
7. COMMON VARIATIONS
================================================================================

  - Multi-tier digest with different cadences (e.g. T1 daily, T2
    weekly): use a SEPARATE digest sheet per cadence. Floqer
    schedules at the sheet level — a single sheet's scheduled run
    fires every row on it together, so you can't drive different
    rows on different intervals from one sheet. Create a "Slack
    Digest — T1 Daily" sheet (recipient rows hardcoded with
    `tier_filter = "T1"`) and a "Slack Digest — T2 Weekly" sheet
    (recipient rows with `tier_filter = "T2"`). Schedule each sheet
    on its own cadence. Both sheets read from the same Scored
    Accounts and Mentioned Accounts; only the filter and cadence
    differ. Cooldown can be unified (one shared Mentioned Accounts)
    or split (per-tier mention log) depending on whether crossing
    tiers should suppress.
  - Per-channel routing: just add another recipient row with a
    different `slack_target`. Same chain, different destination.
    E.g. one row pings a #partnerships channel, another pings a
    #vp-only DM with stricter filters — both off the same
    Mentioned Accounts log so cooldown is unified.
  - Multi-rep digest: one row per rep, each with their own
    `account_owner` filter. Each rep gets a personalized digest of
    only their accounts. Combine with per-recipient cooldown
    scoping (see Failure modes) if reps shouldn't suppress each
    other's mentions.
  - CRM-aware exclusion: add a third http_api_call (or native
    lookup) to a CRM-export sheet and union into the excluded set
    in Apply Cooldown. Suppresses accounts already in active
    pursuit.
  - Cold list discovery upstream: replace the static account list
    with an ICP-driven discovery flow — see
    icp_outbound_prospecting.txt. Stage 1 becomes the discovery +
    scoring chain, Stage 2 onward is unchanged.
  - Brief writing on T1: insert llm_models after the cooldown step
    on survivors only, generate a per-account outreach brief, and
    push briefs into a separate Briefs sheet alongside the Slack
    digest. Keeps the human-in-the-loop digest light while still
    pre-staging context.

================================================================================
8. FAILURE MODES AND MITIGATIONS
================================================================================

  - Cross-sheet lookups truncated at 20 rows.
    Mitigation: explicit page_size: "200" on every http_api_call.
    For >200 rows, chain pages and merge in JS.

  - Cooldown log grows unbounded over months.
    Mitigation: periodic prune step — a separate workflow or a
    weekly chained action that deletes Mentioned Accounts rows
    with mentioned_at older than the cooldown window. Old rows
    are no longer load-bearing.

  - Chained JSON breaks downstream JSON.parse.
    Mitigation: __NL__ sentinel encoding. See
    https://floqer.com/docs/action-detail/format_data_using_js_expression.txt
    §8.3 Chained JSON between formatters.

  - Single-survivor digest pushes zero cooldown rows.
    Mitigation: trailing-comma pad. See
    https://floqer.com/docs/action-detail/csv_to_structured_array_format.txt
    key notes.

  - Slack not connected; digest send step fails.
    Mitigation: replace slack_send_message with a JS formatter
    emitting the message as a cell output. Architecture stays
    intact; swap in real Slack once OAuth is connected.

  - Action-instance-id drift in the JS filter when the scoring
    chain is edited. Replacing a JS formatter step regenerates
    its instance id, and the digest's filter then misses the
    column.
    Mitigation: when editing scoring-chain actions, re-fetch
    /actions/graph on the Scored sheet and update the
    JS constants in the Apply Cooldown step.

  - Cache returns stale output across reruns.
    Mitigation: `cache_enabled: false` on the Slack Digest sheet is
    mandatory, not optional. With permanent recipient rows whose
    inputs never change, cache would otherwise treat every re-trigger
    as a cache hit and the digest would freeze on its first run's
    output. Verify with `GET /sheets/{digest_sheet}` after creation.

  - Multi-recipient concurrent execution race on the cooldown log.
    A scheduled run fires every row on the sheet in parallel via
    `/run-all`. If two recipient rows have the same account in
    their respective T1 set, whichever finishes its push first
    wins — the second row sees the first's mention and silently
    suppresses that account from its digest.
    Mitigation: add a `recipient_id` column to Mentioned Accounts
    and scope the cooldown lookup to the current recipient. This is
    the only fix that works under Floqer's built-in sheet-level
    scheduler, since you can't ask `/run-all` to execute rows
    sequentially. It also has a side benefit: different reps get
    independent cooldown clocks. (If you happen to drive the digest
    from an external scheduler, you could also call `/run` per row
    in sequence — but per-recipient scoping is the cleaner default.)

================================================================================
9. RELATED USE CASES
================================================================================

  - Account research and scoring — the one-shot batch-scoring sibling.
    Drop the digest + cooldown half and you have that use case.

  - Cross-table lookup via HTTP API Call — the implementation
    pattern this use case relies on for cross-sheet reads. See
    https://floqer.com/docs/use-case-detail/cross_floqer_table_lookup.txt.

  - ICP-based outbound prospecting — when the account list is
    discovered cold rather than supplied. Plugs into Stage 1
    upstream of scoring.

  - Intent-driven outreach — when a single per-account intent
    signal triggers immediate action rather than batched digests.

================================================================================

This file is maintained manually. Last updated: 2026-05-11.

Use case catalog: https://floqer.com/docs/use-case-catalog.txt
Related action details:
  https://floqer.com/docs/action-detail/lookup_another_floqer_workflow_row.txt
  https://floqer.com/docs/action-detail/http_api_call.txt