DEV Community: Mike Clarke

Your AI generated hundreds of pieces of content. None of it ever shipped.

Mike Clarke — Fri, 14 Aug 2026 14:00:06 +0000

Your AI generated hundreds of pieces of content. None of it ever shipped.

Key Takeaways

A queue in a ready state is not done. Done means consumed and delivered.
Generation and publication are two separate stages. An automated bridge between them is not optional.
Any queue without a consumer and a depth alert is a silent failure waiting to accumulate.
"The work looks done" is the most dangerous state in an autonomous system.

In ARIA — the autonomous content operations system we run at Elevare Digital — we watched two queues quietly fill up over time. Content was being generated. It was landing in the database in a ready state. Every upstream metric looked fine.

None of it ever reached the destination. Not a single item.

The root cause was not a bug. It was a design assumption that never got questioned: publication was a manual step, and nobody ran it.

What the system looked like

ARIA's content pipeline has two distinct stages. Stage one: generation. The agents do their work, write the content, and mark the row ready. Stage two: publication. Something takes those ready rows and pushes them out.

Stage one was fully automated. Stage two was manual by design — a decision that made sense early on when we wanted a human in the loop before anything shipped publicly.

The problem is that "manual by design" eventually became "never runs." There was no consumer process watching the queue. There was no alert watching the queue depth. The count just grew.

-- What we were looking at in Supabase:
-- content_queue table, simplified

CREATE TABLE content_queue (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  content_id uuid REFERENCES content(id),
  status text NOT NULL DEFAULT 'pending',
  -- statuses: pending | generating | ready | published | failed
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- The generation side was automated and ran correctly.
-- Items moved from 'pending' -> 'generating' -> 'ready'.
-- Nothing moved them from 'ready' -> 'published'.
-- The query below returned a number that kept growing:

SELECT COUNT(*) FROM content_queue WHERE status = 'ready';

That count was not a success metric. It was a backlog. We were reading it as one and ignoring the other.

Why this is easy to miss

When you look at a pipeline dashboard and see items flowing into ready, it feels like progress. The agents are working. The generation numbers are up. The logs are clean.

The gap between ready and published is invisible unless you specifically instrument it. We hadn't. There was no metric on queue depth over time, no alert threshold, nothing that would fire if ready stopped draining.

This is the specific failure mode:

// Pseudocode for what ARIA's generation side was doing — correctly:
async function generateContent(jobId: string) {
  await updateStatus(jobId, 'generating');
  const content = await runGenerationAgents(jobId);
  await saveContent(content);
  await updateStatus(jobId, 'ready'); // <-- work stops here
  // Nothing downstream is listening. This is a dead end.
}

// What the publication side needed but didn't have:
async function publishConsumer() {
  // This function did not exist as an automated process.
  // It existed as a manual script someone had to remember to run.
  const readyItems = await getItemsByStatus('ready');
  for (const item of readyItems) {
    await publish(item);
    await updateStatus(item.id, 'published');
  }
}

The generation function had no idea there was no consumer on the other end. It just kept doing its job.

The fix: every queue gets a consumer and a depth alert

The rule we applied after this: no queue exists without two things attached to it.

An automated consumer that drains it.
A depth alert that fires if items in a terminal-waiting state (like ready) exceed a threshold for too long.

The consumer can be a Deno edge function on a schedule, a Supabase pg_cron job, a webhook trigger — the mechanism matters less than the guarantee that something is watching and pulling.

-- pg_cron job that checks for stranded 'ready' items
-- and alerts if the queue hasn't drained

-- First, a view that makes the staleness visible:
CREATE VIEW stranded_content AS
SELECT
  id,
  content_id,
  status,
  updated_at,
  now() - updated_at AS time_in_state
FROM content_queue
WHERE
  status = 'ready'
  AND updated_at < now() - INTERVAL '2 hours';

-- Then a function that pages someone if this view has rows:
CREATE OR REPLACE FUNCTION alert_on_stranded_queue()
RETURNS void AS $$
DECLARE
  stranded_count integer;
BEGIN
  SELECT COUNT(*) INTO stranded_count FROM stranded_content;

  IF stranded_count > 0 THEN
    -- Call your alerting mechanism here.
    -- We use a Supabase edge function that posts to our ops channel.
    PERFORM net.http_post(
      url := current_setting('app.alert_webhook_url'),
      body := json_build_object(
        'message', format('%s items stranded in ready state', stranded_count),
        'severity', 'warning'
      )::text,
      headers := '{"Content-Type": "application/json"}'
    );
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Schedule it:
SELECT cron.schedule(
  'check-stranded-queue',
  '*/30 * * * *', -- every 30 minutes
  'SELECT alert_on_stranded_queue()'
);

The threshold and interval depend on your expected throughput. The point is that a human gets paged before the count becomes embarrassing.

The lesson isn't about the bug

This wasn't a bug in the traditional sense. The code did exactly what it was written to do. Generation worked. The ready status was accurate. The manual publication step was documented.

The failure was architectural: we treated "generated" as a proxy for "shipped" without building anything to enforce the difference.

In an autonomous pipeline, every state transition needs an owner. If the transition from ready to published requires a human action, then the system needs to demand that action — not wait quietly while the queue fills.

A full queue is not progress. It's generated work that went nowhere. The two look identical from the outside until you add the one metric that matters: how long has this item been waiting, and who knows about it?

— Mike Clarke, founder of Elevare Digital.

Configuring Smart Traffic Systems: A Developer's Deep Dive

Mike Clarke — Wed, 12 Aug 2026 06:00:21 +0000

As developers, we often tackle complex problems, and few are as dynamic and critical as managing urban traffic. The constant ebb and flow of vehicles, pedestrians, and public transport presents a fascinating challenge. If you've ever stared at a gridlocked intersection and thought, "There has to be a better way," then you're already thinking like a smart traffic system architect. Today, we're diving into how to configure a smart traffic system – not just the fancy AI, but the foundational logic that makes it tick.

The Gridlock Dilemma: Why Smart Systems Matter

Traditional traffic light systems are, for the most part, static. They operate on pre-defined timers, regardless of actual traffic density. This leads to frustrating scenarios: an empty main road gets a long green light while a dozen cars wait impatiently on a side street, or vice-versa. This inefficiency isn't just annoying; it costs time, wastes fuel, increases pollution, and can even delay emergency services.

The goal of a smart traffic system is to move beyond these fixed schedules. It aims for dynamic, adaptive control, optimizing traffic flow in real-time. This isn't just about making commutes smoother; it's about building more efficient, sustainable, and responsive cities.

The Core Concept: Sensors, Logic, and Actuation

At its heart, a smart traffic system is a feedback loop. It observes, decides, and acts. Here's a breakdown:

Sensing (Input): This is where data is collected. Inductive loops embedded in the road, cameras with computer vision capabilities, radar, and even connected vehicle data can tell the system about vehicle presence, speed, queue length, and pedestrian crossings.
Processing/Logic (Decision): This is the brain of the operation. Based on the data from the sensors, the system's algorithms decide the optimal traffic light phasing. Simple systems might use rule-based logic (e.g., "if queue on street A > 5 and queue on street B < 2, extend green for A"). More advanced systems employ machine learning models to predict traffic patterns or reinforce learning agents to dynamically learn optimal strategies.
Actuation (Output): Once a decision is made, the system controls the traffic signals. This involves sending commands to change light states (red, yellow, green) and their durations.

Configuring such a system involves defining these relationships, setting thresholds, and refining the algorithms. It's less about hard-coding every single scenario and more about building a flexible, adaptable framework.

Pseudocode Snapshot: A Simple Adaptive Intersection

Let's consider a basic 4-way intersection. Our goal is to dynamically adjust green light times based on detected traffic volume. We'll use a TrafficLight object for each approach and a Sensor object to detect vehicles.

// Define a simplified TrafficLight object
class TrafficLight:
    constructor(id, initial_state, min_green_time, max_green_time)
    method set_state(new_state)
    method get_current_state()

// Define a simplified Sensor object
class Sensor:
    constructor(location_id)
    method get_vehicle_count() // Returns number of vehicles detected
    method get_queue_length() // Returns estimated queue length

// Main Traffic Management System Logic
function configure_smart_intersection(intersection_id, approaches):
    // approaches: a map from approach_id (e.g., 'north_bound') to a tuple of (TrafficLight, Sensor)

    current_green_approach = 'north_bound' // Start with a default
    timer_for_current_approach = 0

    loop indefinitely:
        // 1. Get current traffic data
        traffic_data = {}
        for approach_id, (light, sensor) in approaches.items():
            traffic_data[approach_id] = {
                'vehicle_count': sensor.get_vehicle_count(),
                'queue_length': sensor.get_queue_length()
            }

        // 2. Apply Decision Logic
        current_light, current_sensor = approaches[current_green_approach]

        // Check if current green time has exceeded minimum or if other approaches demand attention
        if timer_for_current_approach >= current_light.min_green_time:
            // Look for approaches with significant queues that aren't currently green
            candidate_next_approach = null
            max_queue = 0

            for other_approach_id, (other_light, other_sensor) in approaches.items():
                if other_approach_id != current_green_approach:
                    if other_sensor.get_queue_length() > max_queue:
                        max_queue = other_sensor.get_queue_length()
                        candidate_next_approach = other_approach_id

            // If a significant queue is detected elsewhere OR max_green_time is reached
            if (candidate_next_approach != null and max_queue > THRESHOLD_FOR_SWITCH) or 
               timer_for_current_approach >= current_light.max_green_time:
                // Initiate switch sequence (e.g., yellow for current, then red, then green for next)
                // (Simplified for pseudocode)
                current_light.set_state('YELLOW')
                wait(YELLOW_DURATION)
                current_light.set_state('RED')

                current_green_approach = candidate_next_approach // Or pick based on priority
                next_light, _ = approaches[current_green_approach]
                next_light.set_state('GREEN')
                timer_for_current_approach = 0
            else:
                // Extend current green light
                timer_for_current_approach += TIME_STEP
        else:
            // Must complete minimum green time
            timer_for_current_approach += TIME_STEP

        wait(TIME_STEP) // Simulate time passing

This pseudocode illustrates a basic reactive system. Real-world systems incorporate predictive models, coordination between multiple intersections, pedestrian detection, emergency vehicle preemption, and sophisticated optimization algorithms. The THRESHOLD_FOR_SWITCH and TIME_STEP would be configurable parameters crucial for fine-tuning performance.

Why Hands-On Practice is Non-Negotiable

Understanding the concepts is one thing; making a system like this work in a dynamic environment is another. The real challenge lies in:

Parameter Tuning: What's the optimal THRESHOLD_FOR_SWITCH? How do min_green_time and max_green_time interact across multiple intersections?
Edge Cases: What happens during peak hours, during an accident, or when sensors fail?
Scalability: How do you extend this logic from one intersection to an entire city?
Performance: Ensuring real-time decisions without introducing latency.

These are problems best solved by building, testing, and iterating. Reading about algorithms is great, but getting your hands dirty with a simulated environment lets you see the immediate impact of your configuration choices. It's where you learn the nuances of balancing flow, preventing deadlocks, and optimizing for various metrics.

Ready to Build Smarter Cities?

Configuring smart traffic systems is a fantastic way to apply your development skills to a tangible, impactful problem. It combines elements of data processing, algorithms, and real-time control. Instead of just theorizing, imagine deploying your own adaptive traffic logic and seeing the results unfold.

Practice this concept interactively on CodeCityApp — free trial at codecityapp.com

Originally published on CodeCityApp

Your pipeline was green for weeks. It shipped nothing.

Mike Clarke — Tue, 11 Aug 2026 14:00:05 +0000

There is a category of production failure that monitoring tools are almost designed to miss. The system runs. The pings arrive. Every dashboard stays green. And somewhere behind that green, nothing is happening.

We hit this with ARIA, the autonomous system we run at Elevare Digital. A pipeline had been silent for weeks. Not crashing — silent. The health checks came in on schedule. The function executed. And the work it existed to do was not getting done.

Key Takeaways

Liveness (did it run?) and function (did it accomplish anything?) are different properties. Most health checks only measure one.
A cron that reschedules, retries, or re-checks the same item will emit healthy pings indefinitely while producing zero output.
The fix is to make your health signal carry a payload: items processed, rows moved, work done.
Alert on absence of output over a window, not just on errors.
A green heartbeat on a pipeline that produces nothing is a failure wearing a healthy costume.

What we were checking vs. what we needed to check

The health check looked like this conceptually:

// What we had — liveness only
async function runPipeline() {
  await doWork(); // might do nothing
  await recordHealth({ status: 'ok', timestamp: new Date() });
}

The function ran. It called doWork(). It recorded status: ok. Monitoring saw the ping. Everything looked fine.

The problem: doWork() had silently reduced itself to re-checking one item it had already processed. It found nothing new, did nothing, and returned without error. The health signal had no idea. It recorded that the function executed — which was true — and implied from that the pipeline was functioning — which was not.

This distinction sounds obvious written out. It is easy to miss in practice because the failure mode produces no errors, no exceptions, no timeouts. It produces only silence, and your monitoring is not listening for silence.

The mechanism in plain terms

Cron fires. Function wakes up. Function checks for work. No new work is found (or the same old item keeps surfacing and getting skipped). Function exits cleanly. Health ping is recorded.

Repeat. Every interval. For weeks.

From the outside: healthy system.
From the inside: a poster that clocks in, sits down, does nothing, clocks out, and files a timesheet marked "completed."

The fix: make the health signal carry evidence of work

We changed the health record to include output metrics. Not just "did it run" but "what did it produce."

// What we changed to — function-level health
async function runPipeline() {
  const result = await doWork();

  await recordHealth({
    status: 'ok',
    timestamp: new Date(),
    items_processed: result.count, // the actual payload
  });
}

Then we added a check that looks at recent health records and alerts when a supposedly-active pipeline has emitted zero output over a window:

-- Supabase: find active pipelines that reported no work over the last N intervals
SELECT
  pipeline_name,
  COUNT(*) AS runs,
  SUM(items_processed) AS total_output,
  MAX(recorded_at) AS last_run
FROM pipeline_health
WHERE recorded_at > now() - interval '7 days'
GROUP BY pipeline_name
HAVING SUM(items_processed) = 0
ORDER BY last_run DESC;

This query has no interesting results on a healthy system. When it returns rows, a pipeline ran repeatedly and moved nothing — and that is worth an alert regardless of what the status field says.

Why this failure mode is common

Most health check patterns are borrowed from web services, where liveness and function are closely coupled. If your API endpoint returns 200, it almost certainly did the thing it exists to do. The request-response cycle forces the work to happen before the response is emitted.

Background pipelines are different. The work is decoupled from the signal. The function can complete — cleanly, successfully — and still have accomplished nothing. The health check fires after execution regardless of output.

So when teams instrument a pipeline the same way they instrument an endpoint, they end up measuring the wrong thing.

What to add to any pipeline health check

Three fields that matter more than status: ok:

interface PipelineHealthRecord {
  pipeline_name: string;
  recorded_at: string;
  status: 'ok' | 'error';

  // These are the fields that actually tell you something
  items_processed: number; // work done this run
  items_available: number | null; // work seen (optional but useful)
  error_detail: string | null;
}

With items_available you can distinguish between two very different situations:

Available = 0, processed = 0: queue is empty, pipeline is idle. Probably fine.
Available > 0, processed = 0: work exists, pipeline is not touching it. Not fine.

The second case is what we had. The pipeline could see work (or thought it could), entered its processing loop, and exited without moving anything. Status: ok the whole time.

The alert logic

Once the health records carry real output data, the alert becomes straightforward:

async function checkPipelineOutput(pipelineName: string) {
  const { data } = await supabase
    .from('pipeline_health')
    .select('items_processed, recorded_at')
    .eq('pipeline_name', pipelineName)
    .gte('recorded_at', new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString())
    .order('recorded_at', { ascending: false });

  if (!data || data.length === 0) return; // no runs at all — separate alert

  const totalOutput = data.reduce((sum, row) => sum + (row.items_processed ?? 0), 0);
  const runCount = data.length;

  if (runCount > 3 && totalOutput === 0) {
    await sendAlert({
      pipeline: pipelineName,
      message: `Ran ${runCount} times in the last 7 days. Produced zero output.`,
      severity: 'high',
    });
  }
}

The threshold (runCount > 3) is tunable. The point is that multiple runs with zero output is the signal. A single zero-output run might be a quiet period. Several in a row is the pipeline telling you something is wrong, if you are listening.

The honest lesson

We were not measuring the wrong thing by accident. We followed a normal health check pattern and it was simply insufficient for this class of job. The pattern works fine for services. It does not work for pipelines.

Log the absence of work as loudly as the presence of errors. Zero output on a pipeline that should be producing is not a quiet success. It is an invisible failure, and the only difference between that and a noisy crash is that the noisy crash gets fixed.

— Mike Clarke, founder of Elevare Digital.

The cleanup code that ate every code block in our published articles

Mike Clarke — Tue, 04 Aug 2026 14:00:21 +0000

Liquid syntax error: Variable '{{% raw %}' was not properly terminated with regexp: /\}\}/

Smart Traffic Systems: Configuring Flow for a Smarter City

Mike Clarke — Wed, 29 Jul 2026 06:00:20 +0000

Smart Traffic Systems: Configuring Flow for a Smarter City

Ever found yourself stuck in a gridlocked intersection, wondering why the lights aren't cooperating? Or perhaps you've mused about the sheer inefficiency of fixed-timer traffic signals in a dynamic urban landscape. As developers, these aren't just annoyances; they're ripe problems begging for intelligent solutions. And that's where smart traffic systems come in.

The Problem: Static Systems vs. Dynamic Reality

Traditional traffic light systems operate on pre-programmed timings. They don't care if an ambulance needs to pass, if a bus is running behind schedule, or if one lane is bumper-to-bumper while another is empty. This static approach leads to congestion, increased travel times, higher fuel consumption, and crucially, frustrated citizens. We can do better.

The Concept: Dynamic, Data-Driven Control

How to configure a smart traffic system? At its core, it's about shifting from reactive, static timing to proactive, data-driven optimization. A smart system continuously gathers data from various sources: loop detectors, cameras (for vehicle count and classification), GPS data from connected vehicles, even weather sensors. This data feeds into an intelligent agent, often leveraging AI/ML algorithms, to make real-time decisions about traffic light signalization, lane usage, pedestrian crossings, and even suggesting alternative routes.

Think of it as a central brain that constantly analyzes the pulse of the city's arteries and adjusts flow accordingly. The goal is to minimize overall travel time, reduce congestion, prioritize emergency vehicles, and optimize public transport efficiency. It's a complex optimization problem, often involving multi-agent systems and reinforcement learning.

A Glimpse Under the Hood (Pseudocode):

Let's imagine a simplified scenario for a single intersection. Our smart system needs to decide which phase (e.g., North-South green, East-West red) to activate and for how long.

// Initialize system components
TrafficLightController = new Controller()
SensorDataStream = new DataStreamService()
OptimalPhaseSelector = new MLModel()

// Main Loop - runs continuously
LOOP:
    // 1. Gather Real-time Data
    currentTrafficData = SensorDataStream.getLiveTrafficData(intersectionID)
    // currentTrafficData might include:
    //    - VehicleCountsPerLane
    //    - AverageWaitingTimesPerLane
    //    - PresenceOfEmergencyVehicles
    //    - PedestrianCrossingRequests

    // 2. Process Data and Determine Optimal Phase
    optimalPhaseResult = OptimalPhaseSelector.predictOptimalPhase(
        currentTrafficData,
        historicalTrafficPatterns,
        currentWeatherConditions
    )
    // optimalPhaseResult might contain:
    //    - recommendedPhase (e.g., 'NS_GREEN')
    //    - recommendedDuration (e.g., 45 seconds)
    //    - priorityFlags (e.g., 'EmergencyVehiclePresent')

    // 3. Command Traffic Lights
    IF optimalPhaseResult.priorityFlags.contains('EmergencyVehiclePresent'):
        TrafficLightController.triggerEmergencyPhase(optimalPhaseResult.recommendedPhase)
    ELSE:
        TrafficLightController.setPhase(optimalPhaseResult.recommendedPhase, optimalPhaseResult.recommendedDuration)

    // 4. Log and Monitor (for debugging and retraining)
    Log.event("Phase changed to " + optimalPhaseResult.recommendedPhase + " for " + optimalPhaseResult.recommendedDuration)
    Monitor.displayCurrentTrafficFlow()

    WAIT for short interval // Re-evaluate every few seconds
END LOOP

// --- Additional functions for OptimalPhaseSelector (simplified) ---

FUNCTION predictOptimalPhase(data, history, weather):
    // Example logic (highly simplified):
    IF data.EmergencyVehicles > 0:
        RETURN {recommendedPhase: 'ClearPathForEmergency', recommendedDuration: 10, priorityFlags: ['EmergencyVehiclePresent']}

    ELSE IF data.LaneTraffic['NorthBound'] > data.LaneTraffic['EastBound'] * 2 AND data.WaitingTimes['NorthBound'] > 60:
        RETURN {recommendedPhase: 'NS_GREEN', recommendedDuration: 60}

    ELSE IF data.PedestrianRequests['EastCross'] AND data.SafeToCross:
        RETURN {recommendedPhase: 'EW_PED_GREEN', recommendedDuration: 20}

    ELSE:
        // Fallback to a default or historical pattern if no strong signal
        RETURN {recommendedPhase: history.getDefaultPhaseForTimeOfDay(), recommendedDuration: 30}
END FUNCTION

This pseudocode barely scratches the surface. A real-world system would involve complex state machines, robust fault tolerance, distributed sensing, and sophisticated machine learning models trained on vast datasets. The OptimalPhaseSelector would likely be a black box of intricate algorithms deciding based on predicted future states, not just current ones.

Why Practice Matters in This Domain

Configuring a smart traffic system isn't just about writing code; it's about understanding complex systems, optimizing for multiple conflicting objectives, and handling real-time data under pressure. There are countless edge cases, from sensor failures to unexpected traffic surges. Practicing these concepts – building simplified models, simulating various scenarios, and tweaking parameters – is crucial for developing the intuition needed to build robust, life-changing solutions.

It's one thing to read about Reinforcement Learning; it's another to apply it to make a simulated city's traffic flow smoother. These are problems where your algorithms have a direct, tangible impact.

Practice this concept interactively on CodeCityApp — free trial at codecityapp.com

Originally published on CodeCityApp

The upsert said it worked. It wrote zero rows. Every single run.

Mike Clarke — Tue, 28 Jul 2026 14:00:04 +0000

Your scanner runs. It fetches data. It loops through rows, calls upsert, increments a success counter. It logs ✓ 42 rows processed. You check the table. Empty.

That ran every day for a while before we caught it.

Key Takeaways

ON CONFLICT (col) will not use a partial unique index (WHERE col IS NOT NULL) as the conflict arbiter. Postgres requires the predicate to be restated in the statement itself.
PostgREST's upsert path can't restate that predicate. Every row throws a quiet error.
A loop that only increments on success will report a perfectly healthy run that wrote absolutely nothing.
Fix: replace the partial index with a plain, unconditional unique index.

The setup

At Elevare Digital, ARIA runs data ingestion pipelines that pull from external sources and upsert into Postgres via Supabase. Standard stuff. One scanner's job was to fetch records and land them in a table, using a unique column as the conflict target so re-runs were idempotent.

The index on that column had been created like this, probably by someone being careful about NULLs:

CREATE UNIQUE INDEX records_external_id_idx
  ON records (external_id)
  WHERE external_id IS NOT NULL;

Reasonable-looking. Partial indexes are useful. This one quietly broke everything.

What Postgres actually requires

When you write:

INSERT INTO records (external_id, payload)
VALUES ('abc-123', '{...}')
ON CONFLICT (external_id) DO UPDATE
  SET payload = EXCLUDED.payload;

Postgres needs to identify which unique constraint or index to use as the arbiter. For a full unique index, ON CONFLICT (external_id) is enough — Postgres finds it.

For a partial unique index, Postgres requires the conflict target to include the index predicate:

INSERT INTO records (external_id, payload)
VALUES ('abc-123', '{...}')
ON CONFLICT (external_id) WHERE external_id IS NOT NULL  -- restate the predicate
DO UPDATE
  SET payload = EXCLUDED.payload;

Without that WHERE clause, Postgres cannot match the partial index. It doesn't fall back. It throws an error:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

You can verify this yourself in a few lines:

-- Create a table with a partial unique index
CREATE TABLE demo (id serial, external_id text, payload text);
CREATE UNIQUE INDEX demo_ext_id_partial ON demo (external_id) WHERE external_id IS NOT NULL;

-- This fails — Postgres can't match the partial index
INSERT INTO demo (external_id, payload) VALUES ('x1', 'a')
ON CONFLICT (external_id) DO UPDATE SET payload = EXCLUDED.payload;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

-- This works — predicate restated
INSERT INTO demo (external_id, payload) VALUES ('x1', 'a')
ON CONFLICT (external_id) WHERE external_id IS NOT NULL
DO UPDATE SET payload = EXCLUDED.payload;
-- INSERT 0 1

Why PostgREST couldn't restate the predicate

ARIA calls Supabase's upsert via PostgREST. The request looks something like:

const { error } = await supabase
  .from('records')
  .upsert(rows, { onConflict: 'external_id' });

PostgREST translates onConflict: 'external_id' into:

ON CONFLICT (external_id) DO UPDATE ...

There's no interface in PostgREST's upsert path to append an arbitrary WHERE predicate to the conflict target. So every row-level upsert hit the same error. Every row.

Why the loop reported success

The ingestion loop looked roughly like this:

let successCount = 0;

for (const row of fetchedRows) {
  const { error } = await supabase
    .from('records')
    .upsert(row, { onConflict: 'external_id' });

  if (!error) {
    successCount++;
    // error case: nothing. no log, no throw, no increment.
  }
}

console.log(`✓ ${successCount} rows processed`);

When error was non-null, the loop just... continued. No log. No counter. The success counter never moved, but fetchedRows.length rows later, it logged a number that looked fine because the fetch itself succeeded.

Actually — it logged 0. But nobody was watching for zero. Zero looks like "nothing to sync today."

A counter that only moves on success will always look plausible. You need to also track failures:

let successCount = 0;
let errorCount = 0;

for (const row of fetchedRows) {
  const { error } = await supabase
    .from('records')
    .upsert(row, { onConflict: 'external_id' });

  if (error) {
    errorCount++;
    console.error('upsert failed:', error.message, row.external_id);
  } else {
    successCount++;
  }
}

console.log(`processed: ${successCount} ok, ${errorCount} failed of ${fetchedRows.length} fetched`);

if (errorCount > 0) {
  throw new Error(`ingestion completed with ${errorCount} errors`);
}

Now a run that writes nothing will at least be loud about it.

The fix

Drop the partial index. Create a plain one.

DROP INDEX records_external_id_idx;

CREATE UNIQUE INDEX records_external_id_idx
  ON records (external_id);

If NULLs are genuinely a concern, handle them at the application layer or with a NOT NULL constraint on the column. A partial index to "exclude NULLs" is not worth the silent failure mode when you need conflict arbitration.

After the index swap, the PostgREST upsert matched correctly and rows started landing.

The honest lesson

Postgres did nothing wrong here. The docs describe this requirement. The error message is clear. The problem was that the error was being swallowed and nothing downstream cared that zero rows were written.

Two independent things had to both be true for this to stay hidden:

A partial index being used as an ON CONFLICT target without the predicate
A loop that treated errors as a no-op

Either one alone might have surfaced faster. Together, they produced a system that appeared healthy and did nothing.

When you write an ingestion loop, the number that matters isn't "how many did I try" or "how many succeeded." It's the ratio. And if errors are silent, you'll never compute it.

— Mike Clarke, founder of Elevare Digital.

The leads were in the table. The automation never saw them.

Mike Clarke — Fri, 24 Jul 2026 14:00:12 +0000

There were two groups of leads sitting in contacted state. One group was getting follow-up emails, call tasks, the whole sequence. The other group was getting nothing — indefinitely.

Same status. Same table. Different outcomes. No errors anywhere.

Key Takeaways

A "dead population path" is when one write route into a table skips a step that downstream automation assumes happened.
Bulk imports are the most common source of this. They write directly to the final state, skipping every intermediate code path.
The fix is not patching the import. It's a trigger that enforces enrollment regardless of how a row arrives.
For every table your automation reads: list every way rows enter it, then verify each path runs the prerequisites downstream depends on.

What was actually happening

The CRM pipeline in ARIA works like this: a lead gets contacted, the first-contact handler fires, it enrolls the lead in the follow-up sequence. That enrollment step lived exclusively inside the first-contact code path.

User action / API call
  → first-contact handler
      → write lead to `contacted` state
      → enroll in follow-up sequence  ← this is the step

Someone ran a bulk import. It wrote records straight to contacted. No handler. No enrollment.

Bulk import
  → write lead to `contacted` state
                                      ← nothing

The downstream automation that sends follow-ups reads the leads table and looks for enrolled leads in contacted state. The imported leads were in the right table, in the right state, but they were never enrolled. The automation skipped them every single cycle — correctly, by its own logic.

No error. No warning. Just silence.

The gap never closed because there was no reconciliation job. Nothing periodically asked "are there contacted leads that aren't in the sequence?" The imported population just sat there.

Why this is easy to miss

The table looked fine. The leads looked fine. The automation looked fine.

The bug was architectural: there were two write routes into one table, and only one of them ran the setup step the other parts of the system depended on. The system had no way to detect the discrepancy because it never compared "who should be enrolled" against "who is enrolled." It just processed whoever was already enrolled.

This is what I'd call a dead population path. The rows exist. The automation is running. The rows are just invisible to it.

Bulk imports create this constantly because they're designed to skip your application layer — that's literally their value proposition. The problem is your application layer is often where side effects live.

The fix: enforce enrollment at the database layer

The only reliable fix is moving the enrollment check to a place that runs regardless of how the row was written. Postgres triggers do this.

-- The trigger function
CREATE OR REPLACE FUNCTION enroll_contacted_lead()
RETURNS TRIGGER AS $$
BEGIN
  -- Only act on rows entering or already in 'contacted' state
  IF NEW.status = 'contacted' THEN
    -- Check whether this lead qualifies for enrollment
    IF NEW.assigned_to IS NOT NULL AND NEW.email IS NOT NULL THEN
      -- Enroll if not already enrolled
      INSERT INTO follow_up_enrollments (lead_id, enrolled_at, sequence_id)
      VALUES (NEW.id, now(), NEW.sequence_id)
      ON CONFLICT (lead_id) DO NOTHING;
    ELSE
      -- Log why it was skipped so you can find these later
      INSERT INTO enrollment_skipped_log (lead_id, reason, skipped_at)
      VALUES (
        NEW.id,
        CASE
          WHEN NEW.assigned_to IS NULL THEN 'no_assignee'
          WHEN NEW.email IS NULL THEN 'no_email'
          ELSE 'unknown'
        END,
        now()
      )
      ON CONFLICT (lead_id) DO NOTHING;
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach it to both INSERT and UPDATE
-- so it catches bulk imports (INSERT) and status changes (UPDATE) alike
CREATE TRIGGER trg_enroll_contacted_lead
AFTER INSERT OR UPDATE OF status ON leads
FOR EACH ROW
EXECUTE FUNCTION enroll_contacted_lead();

A few things worth noting about this implementation:

ON CONFLICT DO NOTHING on the enrollment insert means existing correctly-enrolled leads are untouched. Safe to run on a table that already has good data.

The skip log is not optional. If a lead doesn't qualify, you want to know why. Without it, you're back to silent failures. The log gives you a query you can run to find every lead that got skipped and the reason.

AFTER INSERT OR UPDATE OF status covers both paths. The bulk import fires INSERT. A status change through the application fires UPDATE. Both paths now run the same enrollment logic.

In Supabase, you apply this in the SQL editor or through a migration file. No special Supabase API needed — it's just Postgres.

Backfilling the existing gap

The trigger handles new rows. But the affected leads already in the table needed a one-time backfill.

-- Enroll any existing contacted leads that slipped through
INSERT INTO follow_up_enrollments (lead_id, enrolled_at, sequence_id)
SELECT
  l.id,
  now(),
  l.sequence_id
FROM leads l
LEFT JOIN follow_up_enrollments fe ON fe.lead_id = l.id
WHERE
  l.status = 'contacted'
  AND fe.lead_id IS NULL           -- not already enrolled
  AND l.assigned_to IS NOT NULL    -- qualifies
  AND l.email IS NOT NULL;         -- qualifies

Run this once after deploying the trigger. Then the trigger maintains the invariant going forward.

The question to ask about every table your automation reads

What are all the ways rows get into this table, and does each one run the steps downstream assumes happened?

For most tables the answer is one or two paths and they're all through your application code. Fine.

But the moment you add a bulk import, a migration script, a database-level upsert from an external tool, or a direct admin write — you've added a path that bypasses your application layer. Any side effects that lived there are now missing for those rows.

The fix isn't to never import. It's to stop trusting that write path determines setup steps. Push the invariants down to the database, where they apply to every write regardless of origin.

Honest lesson

This was a silent failure that looked like working software. The table had data. The automation was running. The logs showed no errors. The only way to notice was to ask why two groups with the same status were having different outcomes — and actually go find the answer.

AI systems like ARIA that run autonomously are more exposed to this class of bug, not less. There's no human checking each lead. The automation either processes them or it doesn't, and if it doesn't, you need something in the system that catches the gap. A trigger logging skipped records is a simple form of that. A reconciliation job that periodically diffs expected vs actual enrollment is a stronger one.

Both beat finding out months later when a customer asks why no one followed up.

— Mike Clarke, founder of Elevare Digital.

The table looked general-purpose. The schema disagreed.

Mike Clarke — Tue, 21 Jul 2026 14:00:09 +0000

A CHECK constraint is documentation with teeth.

That's the whole lesson. But here's what it looks like when you learn it at runtime instead of at read-time.

Key Takeaways

A CHECK constraint encodes scope assumptions the column name never will
A constraint violation inside a trigger aborts the parent transaction — silently, from the caller's perspective
Before writing to a table you inherited, run \d tablename or query information_schema.check_constraints. Read what's there.
Gate on allowed values in code before the insert. Let the constraint be a backstop, not the first line of defense.
Skip-and-log beats throw-and-abort when the parent write is more important than the child record

What happened

We run ARIA, an autonomous CRM and nurture automation system at Elevare Digital. New contact records enroll into follow-up sequences automatically — no human queues the work.

At some point, new records stopped enrolling. The parent write (creating the contact) was aborting entirely. No sequence. No contact. No error surfaced to the caller in a useful way.

The culprit was a region column with a CHECK constraint that looked roughly like this:

ALTER TABLE enrollment_tracker
  ADD CONSTRAINT region_allowed
  CHECK (region IN ('north', 'south', 'east', 'west', 'central'));

The table had been built for one specific program covering five regions. Later, code started treating it as a general enrollment tracker and writing region values the constraint never anticipated. Postgres rejected the insert. Because that insert happened inside a trigger, the whole parent transaction rolled back.

The column was named region. Nothing in that name says "only these five values are valid." The constraint said it. Nobody read the constraint.

Why a trigger makes this worse

If you insert directly into a table and violate a CHECK, you get an error back immediately. Annoying, but contained.

When the insert is inside a trigger on a different table, the error propagates up and aborts the statement that fired the trigger. The caller sees their write fail. They may have no idea a trigger was involved, let alone which constraint fired inside it.

-- Trigger fires on INSERT to contacts
CREATE OR REPLACE FUNCTION enroll_contact()
RETURNS trigger AS $$
BEGIN
  -- This insert can blow up the parent INSERT INTO contacts
  INSERT INTO enrollment_tracker (contact_id, region, enrolled_at)
  VALUES (NEW.id, NEW.region, now());

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_enroll
AFTER INSERT ON contacts
FOR EACH ROW EXECUTE FUNCTION enroll_contact();

Now do this:

INSERT INTO contacts (id, name, region)
VALUES (gen_random_uuid(), 'Acme Corp', 'southeast');
-- ERROR: new row for relation "enrollment_tracker" violates
-- check constraint "region_allowed"
-- DETAIL: Failing row contains (..., southeast, ...).
-- The contact was NOT created.

southeast is a perfectly valid business concept. The table just never knew about it.

The fix: gate before you insert

Once we understood the constraint, the fix was straightforward. Check the allowed values in code (or in the trigger function itself) before attempting the insert. If the value isn't allowed, skip the enrollment record and log it. The parent write completes.

CREATE OR REPLACE FUNCTION enroll_contact()
RETURNS trigger AS $$
DECLARE
  allowed_regions TEXT[] := ARRAY['north', 'south', 'east', 'west', 'central'];
BEGIN
  IF NEW.region = ANY(allowed_regions) THEN
    INSERT INTO enrollment_tracker (contact_id, region, enrolled_at)
    VALUES (NEW.id, NEW.region, now());
  ELSE
    -- Log it; don't abort the parent write
    INSERT INTO enrollment_skipped (contact_id, region, skipped_at, reason)
    VALUES (NEW.id, NEW.region, now(), 'region not in enrollment_tracker allowed list');
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Alternately, you can query the constraint definition directly rather than hardcoding the list — which is useful if the allowed values might expand:

-- Pull allowed values from the constraint definition at runtime
-- (useful for visibility; hardcoding is fine if values are stable)
SELECT consrc
FROM pg_constraint
WHERE conname = 'region_allowed'
  AND conrelid = 'enrollment_tracker'::regclass;

Returns something like (region = ANY (ARRAY['north'::text, 'south'::text, ...])). Not the cleanest parse, but it tells you exactly what the schema intended.

How to read the constraints you inherited

Before writing to any table you didn't build:

-- psql shortcut
\d enrollment_tracker

-- Or query directly
SELECT
  tc.constraint_name,
  tc.constraint_type,
  cc.check_clause
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.check_constraints cc
  ON tc.constraint_name = cc.constraint_name
WHERE tc.table_name = 'enrollment_tracker';

Constraints you'll find this way:

CHECK — allowed values, ranges, cross-column rules
UNIQUE — uniqueness you may not have assumed
NOT NULL — columns the schema considers required
FOREIGN KEY — referential dependencies

None of this is hidden. It's just rarely read.

The actual lesson

The table name was enrollment_tracker. That sounds general. It wasn't — it was built for a specific program with five regions, and the CHECK constraint was the only place that scope was written down.

When later code treated it as a general tracker, it imported an assumption it never knew was there. The schema surfaced that assumption at write time, inside a trigger, in a way that took down the parent record.

Schema constraints are the closest thing to binding documentation that most databases have. They don't drift. They don't get outdated and left in a wiki. They're enforced.

Read them before you write. Not after.

— Mike Clarke, founder of Elevare Digital.

Your AI agent checked its queue, found nothing, and went back to sleep. The queue was full.

Mike Clarke — Fri, 17 Jul 2026 14:00:09 +0000

Postgres Row-Level Security doesn't raise an error when it blocks you. It returns zero rows. Your query succeeds. Your agent sees an empty queue. Your agent idles. Your jobs pile up.

This is what happened to ARIA, our autonomous AI system at Elevare Digital.

Key Takeaways

Postgres RLS silently filters rows on SELECT — a blocked read and a genuinely empty table look identical to the caller
An orchestrator that reads its own queue gets no exception, no warning, no non-200 status code when RLS blocks it
A healthy heartbeat on an idle agent tells you nothing about whether the idle is real
The fix: add a canary count that verifies you can read, not just that you did read
Treat an empty read as a question, not an answer

What the agent saw

The orchestrator polls a work queue table. Normal operation:

Query for pending jobs
If results → process them
If empty → log idle heartbeat, sleep

The logs showed healthy idle heartbeats. Monitoring showed the agent alive and polling. From the outside, everything looked fine.

Jobs were not being processed.

What actually happened

Someone added a Row-Level Security policy to the queue table, scoped to auth.uid(). Correct for user-facing reads. But the orchestrator connects under the service role — and no service-role bypass was added to the policy.

Here's the policy as it was written:

-- Added for user-facing queue visibility
CREATE POLICY "users_see_own_jobs"
  ON work_queue
  FOR SELECT
  USING (auth.uid() = user_id);

And RLS was enabled on the table:

ALTER TABLE work_queue ENABLE ROW LEVEL SECURITY;

No bypass for the service role. No FOR ALL escape. The orchestrator's SELECT now matched zero rows — because RLS filtered every row out before returning results.

Postgres does not raise. It does not warn. The query completes with status 200 and an empty array.

// Orchestrator poll — looks completely normal
const { data: jobs, error } = await supabase
  .from('work_queue')
  .select('*')
  .eq('status', 'pending');

// error is null. jobs is []. Agent concludes: nothing to do.
if (!jobs || jobs.length === 0) {
  await recordIdleHeartbeat();
  return;
}

The error is null. The jobs array is empty. The agent's logic is correct for the data it received. The data it received was wrong in a way that produced no signal.

Why this is hard to catch

If RLS blocks a write, you often get an error — the row you tried to insert violates a policy, or returns nothing when you expected a rowcount. Writes are easier to notice.

SELECT under RLS is different. The design intent is that users should not be able to distinguish "this row doesn't exist" from "this row exists but you can't see it." That's a security feature. It's also exactly the wrong behavior for an orchestrator that needs to know the difference between "queue is empty" and "I am blind."

The service role in Supabase bypasses RLS by default — but only if you're using the service-role key on the client. If your edge function or backend is using the anon key, or if it's operating in a context where auth.uid() resolves to null, RLS applies and silently filters.

-- This is what the orchestrator needed, but wasn't there
CREATE POLICY "service_role_bypass"
  ON work_queue
  FOR ALL
  USING (auth.role() = 'service_role');

-- Or, simpler: grant the service role explicit bypass at the table level
ALTER TABLE work_queue FORCE ROW LEVEL SECURITY; -- applies to all
-- and then in your Supabase client: use the service-role key, which bypasses RLS automatically

The actual bypass mechanism in Supabase is that the service-role JWT includes "role": "service_role", and Supabase's Postgres config grants that role BYPASSRLS. If your client is initialized with the service-role key, you're fine. If it's not, RLS applies — no error, just silence.

The fix: a canary count

The real repair has two parts: fix the RLS policy, and add a check that will catch this failure mode again.

Part 1 — Fix the policy:

-- Preserve user-facing policy
CREATE POLICY "users_see_own_jobs"
  ON work_queue
  FOR SELECT
  USING (auth.uid() = user_id);

-- Add explicit service-role access
-- (Or: initialize your orchestrator client with the service-role key,
--  which bypasses RLS automatically via BYPASSRLS grant)
CREATE POLICY "orchestrator_full_access"
  ON work_queue
  FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

Part 2 — The canary check:

The orchestrator now runs a canary query before trusting an empty result. The canary queries a row it knows exists — a sentinel row inserted specifically for this purpose, or a count from an unrestricted table the orchestrator owns.

async function pollQueue() {
  const { data: jobs, error: queueError } = await supabase
    .from('work_queue')
    .select('*')
    .eq('status', 'pending');

  if (queueError) {
    await alertOpsChannel('queue_poll_error', queueError);
    return;
  }

  // Jobs came back — process normally
  if (jobs && jobs.length > 0) {
    await processJobs(jobs);
    return;
  }

  // Empty result — but is it genuinely empty, or are we blind?
  const canaryOk = await verifyReadAccess();
  if (!canaryOk) {
    // This is the case we were missing before
    await alertOpsChannel('queue_read_access_lost', {
      message: 'Orchestrator received empty queue result but canary check failed. Possible RLS or permission regression.',
    });
    return; // Do NOT record idle heartbeat — we don't know the real state
  }

  // Canary passed — empty really means empty
  await recordIdleHeartbeat();
}

async function verifyReadAccess(): Promise<boolean> {
  // Query a sentinel row that always exists in a known state
  // This could be a dedicated canary table, or a system-health row
  const { data, error } = await supabase
    .from('orchestrator_canary')
    .select('id')
    .eq('id', 'heartbeat-sentinel')
    .single();

  if (error || !data) {
    // We expected exactly one row. Getting nothing means access is broken.
    return false;
  }

  return true;
}

The canary table is simple:

CREATE TABLE orchestrator_canary (
  id TEXT PRIMARY KEY
);

INSERT INTO orchestrator_canary (id) VALUES ('heartbeat-sentinel');

-- No RLS on this table — it exists only so the orchestrator can verify it can read
-- If you want RLS: add only a service-role policy, nothing user-facing

Now the orchestrator has three states instead of two:

Jobs found → process
No jobs, canary ok → genuinely idle
No jobs, canary failed → alert, do not idle

The third state was always real. We just had no way to observe it.

The deeper issue with autonomous agents and silent failures

Human-in-the-loop systems fail loudly. A user tries to load their queue, sees nothing, and files a ticket. An autonomous agent has no user. It reads, decides, acts. If the read is silently wrong, the decision is wrong, and nothing complains.

This failure mode matters more as systems get more autonomous. ARIA processes queued work without someone watching every poll cycle. The assumption baked into the polling loop was: an empty read means there is nothing to do. That assumption held until it didn't, and the system had no way to question it.

The fix is to make the orchestrator skeptical of its own empty results. Not paranoid — just skeptical enough to verify the precondition that makes "empty" meaningful.

What to check in your own system

If you're running any kind of queue worker against Postgres or Supabase:

Check which key your worker is using. Supabase service-role key bypasses RLS. Anon key does not. Confirm which one is in your edge function or backend environment.
List your RLS policies and check for missing bypasses. SELECT * FROM pg_policies WHERE tablename = 'your_queue_table';
Look at your idle heartbeats. If idle logging increased around the time you last modified permissions or added RLS, that's worth investigating.
Add a canary. Takes an hour. Catches this entire class of problem permanently.

An empty queue result is not evidence of an empty queue. It's evidence that the query ran. Those are different things, and in autonomous systems, the difference matters.

— Mike Clarke, founder of Elevare Digital.

The function was deployed, healthy, and never ran once

Mike Clarke — Tue, 14 Jul 2026 14:00:05 +0000

There's a category of bug where nothing is broken. The dashboard is green. The deployment succeeded. The function shows a recent update timestamp. And it has done zero work since you shipped it.

We hit this in ARIA, our autonomous agent system. A scheduled function was silently doing nothing — not erroring, not timing out, just... absent.

Key Takeaways

A Supabase edge function can be perfectly deployed and still never execute
JWT verification happens at the gateway, before your handler runs
Gateway-level 401s produce no application logs, no error rows, no telemetry inside the function
The only signal is the absence of invocation-log entries
Functions called by pg_cron need verify_jwt=false if they authenticate via service-role key in the body

What we saw

ARIA runs a multi-agent scheduler. Several edge functions are invoked on a schedule via pg_cron. One of them showed up in the Supabase dashboard as deployed, healthy, recently updated. We had no alerts firing.

What we didn't have: any invocation log rows. Not failed ones. Not slow ones. Zero rows.

Everything looked fine. Nothing was running.

The mechanism

Supabase edge functions have a verify_jwt flag. When it's set to true (the default), the gateway validates the bearer token on every request before the function body executes.

Our pg_cron job was calling the function with a service-role bearer token — correct credentials, correct format. But the gateway's JWT verification was rejecting it and returning a 401 before the handler ever ran.

The request never reached the function. The function had nothing to log.

Here's what the pg_cron invocation looks like:

select cron.schedule(
  'run-agent-task',
  '* * * * *',
  $$
  select net.http_post(
    url := 'https://<project>.supabase.co/functions/v1/agent-task',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || current_setting('app.service_role_key')
    ),
    body := '{}'::jsonb
  );
  $$
);

And here's the function config that caused the silent failure:

# supabase/functions/agent-task/config.toml
[functions.agent-task]
verify_jwt = true  # <-- gateway rejects the cron call here, handler never runs

The fix is one line:

[functions.agent-task]
verify_jwt = false  # gateway passes the request through; function handles auth itself

With verify_jwt=false, you move the authentication check inside the function body, where you can actually see what happens:

import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'

serve(async (req) => {
  const authHeader = req.headers.get('Authorization')
  const token = authHeader?.replace('Bearer ', '')

  // Validate against your service role key inside the handler
  // where failures are visible in your logs
  if (token !== Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')) {
    console.error('Unauthorized request to agent-task')
    return new Response('Unauthorized', { status: 401 })
  }

  // actual work happens here
  console.log('agent-task executing')
  // ...

  return new Response('ok', { status: 200 })
})

Now when auth fails, you get a log line. You get an invocation row. You get something to look at.

Why this is hard to catch

The normal debugging loop assumes that if something failed, there's a record of the failure. You look at logs, you find the error, you fix it.

Gateway-level rejections break that loop. The 401 happens in infrastructure you don't control and don't have direct log access to. From the function's perspective, the request never arrived. From the cron job's perspective, it fired successfully — net.http_post queued the request and moved on.

The only diagnostic signal is the shape of what's missing: no invocation rows in the function's log table, for a function that should be running every minute.

If you're not actively checking for expected invocations, you won't notice.

What we added after

Beyond fixing the flag, we added a simple liveness check: a separate monitor queries the invocation log and alerts if a scheduled function hasn't produced a row in longer than two of its scheduled intervals. Not sophisticated, but it catches the "deployed and doing nothing" class of failure.

-- Example: check that agent-task has run in the last 3 minutes
-- (for a function scheduled every minute)
select
  case
    when max(created_at) < now() - interval '3 minutes'
    then 'STALLED'
    else 'OK'
  end as status
from function_invocation_log
where function_name = 'agent-task';

The specific table structure depends on how you surface invocation logs in your setup — the point is to query for expected evidence of execution, not just absence of errors.

The thing worth internalizing

Deployment success and runtime success are different things. A function can pass every health check and still never execute a single line of your code.

For any function invoked by infrastructure (cron, queues, webhooks) rather than by a user, you need positive confirmation of execution — not just the absence of errors. Absence of errors and absence of execution look identical from the outside.

Check your cron-invoked functions. If verify_jwt=true and you're calling with a service-role token, check the invocation log. The rows might not be there.

— Mike Clarke, founder of Elevare Digital.

Your cron logged success every day for weeks. It approved nothing.

Mike Clarke — Sun, 12 Jul 2026 00:10:11 +0000

Your cron logged success every day for weeks. It approved nothing.

We run ARIA, an autonomous content pipeline at Elevare Digital. Agents generate drafts, a daily approver cron reviews them, approved content moves to publishing. Fully automated, gated by a human-review layer, self-healing in most places.

For several weeks, the approver cron ran on schedule, logged { status: 'success' }, and approved zero drafts. Nineteen drafts sat in the queue. Nobody was alerted. The system looked healthy.

Key Takeaways

A cron that processes zero rows and a cron that processes zero rows because there's nothing to process produce identical logs.
PostgREST inner joins silently exclude rows when the join condition matches nothing — no error, no warning, just an empty result set.
Producer/consumer type drift is invisible until you instrument the gap between "rows waiting" and "rows processed."
Alert on pending > 0 AND processed === 0, not just on thrown errors.
Autonomous systems fail most dangerously in the space between errors and correctness.

What the system was doing

ARIA's generator agents write content and store drafts as rows in a content_opportunities table. Each row has an opportunity_type — in practice, the generator was producing 'article' and 'listing' types.

The approver cron queries that table via Supabase / PostgREST and processes any pending drafts it finds. Here's the shape of what it was doing:

// approver-cron/index.ts (Deno edge function)

const { data: opportunities, error } = await supabase
  .from('content_opportunities')
  .select(`
    id,
    title,
    status,
    content_threads!inner(
      id,
      body
    )
  `)
  .eq('status', 'pending_review')
  .eq('opportunity_type', 'thread'); // <-- the problem

if (error) {
  console.error('Approver query failed', error);
  return new Response('error', { status: 500 });
}

console.log(`Approver run complete. Processed: ${opportunities.length}`);
// ... process opportunities

Two things combined to create the silent failure:

opportunity_type = 'thread' — the generator never wrote rows with this type. It wrote 'article' and 'listing'.
content_threads!inner(...) — PostgREST inner join syntax. If no matching rows exist in content_threads, the parent rows are excluded entirely.

The result: opportunities was always an empty array. No error was thrown. opportunities.length logged as 0. The function returned 200 OK.

Why this is the worst kind of failure

Most failures announce themselves. A network timeout throws. A missing env var crashes on startup. A malformed query returns a PostgREST error object.

This failure looked like rest.

In a queue system, there's a legitimate state where the consumer runs and finds nothing to do — because the queue is empty. That's healthy-idle. It logs Processed: 0 and exits cleanly.

Our broken state also logged Processed: 0 and exited cleanly.

From the outside, from a log aggregator, from a dashboard: identical.

// Healthy idle — queue is empty
Approver run complete. Processed: 0

// Broken — 19 drafts waiting, filter excludes all of them  
Approver run complete. Processed: 0

The only way to tell them apart is to check whether the queue actually had pending rows when the consumer ran.

The fix: broaden scope, instrument the gap

The immediate fix was to remove the incorrect type filter and fix the join. The approver should consume all pending drafts, not just ones of a type the generator never produces:

// Fixed: consume what the producer actually writes
const { data: opportunities, error } = await supabase
  .from('content_opportunities')
  .select(`
    id,
    title,
    status,
    opportunity_type
  `)
  .eq('status', 'pending_review');
  // No type filter. No inner join restricting to non-existent rows.

But the more important fix was the alert. After each consumer run, we now check whether there are pending rows that weren't touched:

// After the approver processes its batch:
const processed = opportunities?.length ?? 0;

const { count: stillPending } = await supabase
  .from('content_opportunities')
  .select('id', { count: 'exact', head: true })
  .eq('status', 'pending_review');

if (processed === 0 && (stillPending ?? 0) > 0) {
  // Consumer ran, touched nothing, but the queue has rows.
  // This is the danger zone.
  await triggerAlert({
    type: 'consumer_drift',
    message: `Approver processed 0 rows but queue has ${stillPending} pending drafts.`,
    severity: 'high',
  });
}

console.log(`Processed: ${processed}. Still pending: ${stillPending ?? 0}`);

This check doesn't care why the consumer touched zero rows. It cares that the queue had work and the consumer didn't do it. The reason might be a bad filter, a broken join, a type mismatch, or something else we haven't thought of yet. The alert fires regardless and forces a human to look.

The underlying pattern: producer/consumer drift

The generator and approver were written at different times. At some point, the generator's output types and the approver's filter drifted apart. Neither system knew about the other's contract. There was no shared schema validation between them, no test that asserted "the approver can actually see what the generator writes."

This is a normal way systems decay. The producer evolves. The consumer doesn't. Or vice versa. In a synchronous API call, this kind of mismatch usually causes a visible failure. In a queue-based async pipeline, it just causes the queue to grow while the consumer reports success.

Autonomous systems make this worse because there's no human in the loop watching the queue depth during normal operation. The whole point is that it runs without supervision. Which means the instrumentation is the supervision.

What we'd do differently

A few things that would have caught this earlier:

Log scanned vs. processed separately. If your consumer query returns zero rows, log that distinctly from "query returned rows and we processed them." scanned: 0, processed: 0 vs scanned: 5, processed: 5 are different states.

Track queue depth over time. If pending rows are accumulating across multiple cron runs, that's a signal even without a consumer error. A simple check: if the queue depth at the end of a run is higher than at the start, something isn't working.

Test the consumer against real producer output. Not just "does the function run without throwing" but "given a row the producer actually writes, does the consumer find and process it."

Name your contracts. If the producer writes opportunity_type: 'article' and the consumer filters on opportunity_type: 'thread', that's a broken contract. Treat it like one — define it explicitly, validate it, test it.

The honest summary: a filter we forgot about, combined with an inner join that excluded everything, turned our approver cron into a machine that ran on schedule, logged success, and accomplished nothing for weeks. The fix was straightforward. The lesson is that in autonomous pipelines, success-on-empty is a failure mode you have to design against explicitly, because the logs will never tell you on their own.

Alert on the gap between what the queue holds and what the consumer touches. Not just on errors.

— Mike Clarke, founder of Elevare Digital.

Smart Traffic Systems: Understanding and Configuring the Core Logic

Mike Clarke — Wed, 01 Jul 2026 06:00:19 +0000

Stuck in Traffic? Let's Build a Smarter Way Forward.

We've all been there: staring at a red light, no cross-traffic in sight, and wondering why it's taking so long. Traditional, fixed-time traffic light systems are notoriously inefficient. They don't adapt to real-world conditions. But what if we could make them smarter? What if our traffic infrastructure could think?

That's where smart traffic systems come in. As developers, we have the power to build these adaptive, intelligent solutions that promise smoother commutes, reduced congestion, and lower emissions. But how do you configure one?

The Brains Behind the Green Light: What Drives a Smart Traffic System?

At its heart, a smart traffic system is a dynamic control loop. It observes, analyzes, decides, and then acts. Here's a breakdown of the key components you'll be configuring:

Data Ingestion: This is your system's eyes and ears. We're talking about real-time input from diverse sources:
- Vehicle Sensors: Inductive loops, radar, lidar, cameras – detecting vehicle presence, speed, and count.
- Pedestrian Sensors: Infrared, pressure plates, computer vision for safe pedestrian crossings.
- Environmental Data: Time of day, weather conditions, historical traffic patterns.
Traffic Flow Analysis & Prediction: Once you have the data, you need to make sense of it. This involves:
- Queue Length Estimation: How many cars are waiting at each intersection approach?
- Travel Time Prediction: How long will it take to clear the intersection?
- Pattern Recognition: Identifying recurring congestion points or anomalies.
Decision-Making Engine (The Optimization Algorithm): This is the core intelligence. Based on the analyzed data, the algorithm determines the optimal light phasing and timing. Common approaches include:
- Reinforcement Learning: The system learns optimal strategies through trial and error over time.
- Fuzzy Logic: Handling imprecise or uncertain data (e.g., 'heavy traffic').
- Heuristic Algorithms: Rule-based systems designed to achieve specific goals (e.g., minimize wait time, maximize throughput).
Actuation: Finally, the system sends commands to the traffic light controllers to change their state (red, yellow, green) and duration.

A Glimpse Under the Hood: Pseudocode for a Basic Adaptive System

Let's consider a simplified scenario: a single intersection with four approaches. We want to minimize average wait time.

FUNCTION ConfigureSmartTrafficSystem(intersectionID):
    // 1. Initialize Sensors & Data Streams
    sensors = GetSensorDataStreams(intersectionID)
    historical_data = LoadHistoricalTrafficPatterns(intersectionID)

    // 2. Define Optimization Goals (e.g., minimize average wait time, maximize throughput)
    optimization_goal = MINIMIZE_AVG_WAIT_TIME
    constraints = { MIN_GREEN_TIME: 10_seconds, MAX_GREEN_TIME: 60_seconds, YELLOW_TIME: 3_seconds }

    // 3. Main Control Loop
    LOOP indefinitely:
        current_vehicle_counts = ReadSensorData(sensors)
        current_pedestrian_counts = ReadPedestrianSensorData(sensors)
        current_time_of_day = GetCurrentTime()

        // 4. Analyze Traffic State
        queue_lengths = CalculateQueueLengths(current_vehicle_counts)
        demand_per_approach = EstimateDemand(current_vehicle_counts, historical_data, current_time_of_day)

        // 5. Decision-Making (using a simplified heuristic)
        next_phase_durations = DetermineOptimalPhaseDurations(
            queue_lengths,
            demand_per_approach,
            optimization_goal,
            constraints
        )

        // Example heuristic: Prioritize approach with most vehicles, but ensure fairness
        FUNCTION DetermineOptimalPhaseDurations(queues, demand, goal, constraints):
            priorities = CalculatePriorities(queues, demand) // e.g., higher queue means higher priority
            selected_phase = SelectPhaseBasedOnPriorities(priorities) 

            // Calculate green time for selected phase
            green_time = CalculateDynamicGreenTime(queues[selected_phase], constraints.MIN_GREEN_TIME, constraints.MAX_GREEN_TIME)

            RETURN { selected_phase: green_time, other_phases: constraints.YELLOW_TIME }

        // 6. Actuate Traffic Lights
        ApplyTrafficLightPhasing(intersectionID, next_phase_durations)

        WAIT(sum(next_phase_durations)) // Wait for the current cycle to complete
END FUNCTION

Configuring this system involves fine-tuning your sensor interpretation, refining your prediction models, and, critically, selecting and optimizing your decision-making algorithm. Are you prioritizing throughput, fairness, emergency vehicle preemption, or perhaps a blend? Each choice impacts the system's behavior.

Why Practice Matters: Beyond the Pseudocode

Understanding the concepts is one thing; making them work in a complex, dynamic environment is another. Real-world traffic data is noisy, sensors can fail, and unexpected events (accidents, sudden surges in traffic) will constantly challenge your system. You'll need to consider:

Robustness: How does your system handle missing or erroneous data?
Scalability: Can it manage hundreds or thousands of intersections?
Latency: Can it react quickly enough to changing conditions?
Ethical Considerations: Are your algorithms fair across different districts or demographic areas?

This isn't just theory; it's about building resilient, impactful software. The best way to grasp these complexities is by doing. Simulating different scenarios, tweaking algorithms, and observing their outcomes is crucial.

Practice this concept interactively on CodeCityApp — free trial at codecityapp.com

Originally published on CodeCityApp