How I Built a Signal-Scoring Pipeline in Postgres (End to End)

Search for how to build a lead-scoring or signal-scoring pipeline in Postgres and you get a wall of generic data-engineering tutorials - MongoDB-to-Power-BI ETL, pg_cron demos, Supabase quickstarts. None of them are about go-to-market. They show you how to move rows; not one shows you how to decide which account a rep should call next. That gap is the whole article. I needed a scored signal queue for StackSwap and built one in Postgres instead of renting it inside Clay or a CRM. This is the end-to-end build log: the schema, the scoring math, the recompute job, and the seam where the scores actually drive outreach. It is the signal layer underneath GTM OS, and it is deliberately boring engineering - which is the point, because the judgment is in the weights, not the tech. For the layer-by-layer context first, pair this with the GTM data layer.

Signal scoring is not lead scoring

Most "lead scoring" you read about is static: a fit score on the account - industry, size, tech stack - that barely moves once it is set. Signal scoring is the live half, the part that decays. A pricing-page visit, a job change into a buying role, an API call, a competitor-cancel rumor: each is an event with a timestamp, and its value to you is mostly a function of how fresh it is. A pricing visit from this morning is a different object than the same visit from six weeks ago, even though the two rows look identical. So the pipeline scores two things and combines them: a slow-moving fit score on the entity, and a fast-moving intent score that is the time-weighted sum of recent signals. The reason to build this in Postgres rather than eyeball it in a spreadsheet is that "time-weighted" has to be recomputed on a schedule, against thousands of rows, with a decay curve - which is exactly what a database is for and a spreadsheet is not.

The schema (three tables)

Strip it to the minimum and it is three tables. entities - one row per account (and optionally per person): the firmographic fit attributes plus a stored fit_score. This is the slow layer; it updates when enrichment refreshes, not every hour. signals - the event log, append-only: entity_id, signal_type, weight, source, and an occurred_at timestamp. Every pricing visit, job change, form fill, API call, or review-site action lands here as one immutable row. Append-only matters - you never mutate a signal, you let it age. scores - the computed output: entity_id, fit_score, intent_score, total_score, and a computed_at stamp. This table is disposable; it is fully derivable from the other two, which means you can change your weights and rebuild it without losing a single source row. That separation - raw events you never touch, a derived score you can always rebuild - is the most important design choice in the whole thing. It is what lets you tune the model on Tuesday without having corrupted Monday's data, and it is the thing the spreadsheet version can never give you.

The scoring function (and why it is SQL, not an LLM)

The score is a weighted sum with a decay term. In plain terms: total equals fit weight times fit, plus intent weight times the sum of each recent signal's weight multiplied by a decay factor on its age. The decay is an exponential half-life - a signal worth 10 points today is worth 5 at its half-life, 2.5 at two half-lives - so stale intent fades instead of sitting at the top of the queue forever. Different signal types get different half-lives: an API call decays over weeks, a funding round over months, a single pricing visit over days. It is one SQL query with a window function, run as a scheduled job. I want to flag why it is SQL and not a model call, because the temptation in 2026 is to throw an LLM at everything. Scoring is arithmetic over time - causal, repeatable, auditable. An LLM "scoring" a lead hands you a confident number you cannot reproduce or explain, and it will rank a six-week-old pricing visit as high intent because the text looks the same as a fresh one. Keep the model for the unstructured work - classifying a reply, drafting an opener, extracting intent from a webhook payload - and keep the math in SQL. The applied version of that division of labor is AI SDR infrastructure.

Recompute: pg_cron or a worker

Two ways to keep the scores fresh, and the choice is a real fork. pg_cron inside Postgres (Supabase ships it): schedule the scoring query to run every N minutes. Dead simple, no extra infrastructure, the database does its own housekeeping. This is where I started and where most teams should stay. An external worker: a small job that wakes on a schedule, or on a webhook when a high-value signal lands, recomputes, and writes back. More moving parts, but it is where you go when scoring needs to call out - to enrich a brand-new entity before scoring it, or to recompute one account the instant it hits pricing instead of waiting for the next cron tick. The decision underneath is cadence: real-time, hourly, or daily. Most intent does not need real-time - a daily or hourly batch is plenty, and real-time scoring is usually solving for a dashboard's vanity refresh, not a rep's response window. The exception is a few high-velocity signals - a demo request, a trial-and-immediate-API-call - that genuinely decay in hours; those earn an event-triggered recompute, everything else rides the batch. I run the batch on pg_cron and event-trigger only the short-half-life signals.

Where the score actually plugs in

A score nobody acts on is a dashboard, not a pipeline. The output table feeds two places. Outbound: the send queue pulls the top of the scored list, not a static lead list, so the next account a sequence touches is the highest live-intent fit - and because intent decays, the queue reorders itself as signals age. Inbound and sales: the same total_score sets the routing tier and the speed-to-lead SLA - a 90 routes and pages a rep instantly, a 40 drips on a nurture. And there is a hard gate I keep on purpose: a human approves anything that leaves the building. The pipeline ranks and drafts; it does not autosend off a score. The score decides priority, the opener decides relevance, but a person still says go. Owning the pipeline is what gives me a place to put that gate - the same human-in-the-loop principle behind AI SDR infrastructure.

What broke

Three things bit me, and they are the useful part of any build log. Stale signals at the top. My first version summed signal weights with no decay. Within two weeks the highest-"intent" accounts were ones that had visited pricing once, a month ago, and never again - the queue was ranking history, not heat. The half-life decay was the fix, and it is why the decay term is not optional. I pulled the full version of that lesson into a signal decay model. Double-counting. Two sources reported the "same" pricing visit - the client-side pixel and the server log - and the entity scored twice for one event. The fix was a dedupe key on the signals table: entity_id, signal_type, source, and occurred_at rounded to the minute, so the same event from two sources collapses to one row. Append-only does not mean accept-everything. The LLM-scoring detour. I briefly let a model assign the intent score from a text summary of an account's activity. It was seductive and wrong: the numbers were unreproducible, drifted run to run, and it once scored a long-churned account as red-hot because the summary mentioned our category a lot. I ripped it out and put the arithmetic back in SQL. The model writes the opener; the database keeps the score. That one line is the most important thing in this build.

Where this leaves you

If you want the built version rather than the build instructions, GTM OS is the hosted control center this signal layer lives inside - run on your own AI keys, with the scored queue and the human-approval gate already wired. If you want the building blocks, the Operator Playbook is the set of Claude skills that scaffold exactly this kind of pipeline - schema, scoring query, and recompute job - for the price of a lunch instead of a platform subscription. And if you want to know how much of your current stack is a scoring layer scattered across tools you could consolidate, that is what a StackScan audit is for. The reason this was hard to find written down is the same reason it is worth running yourself: the scoring is where your judgment lives. The decay curves, the weights, the half-lives - those are your read on your market, encoded in SQL you can diff. Keep the data and the sending on their own rails. Run the scores from one place.