Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

37.4 KB
Raw Blame
//! The day-chunk claim/CAS ledger in PostgreSQL: the sole owner of the `cohort_backfill_chunks` SQL.
//!
//! Written chunk statuses bind [`ChunkStatus::as_str`] as a parameter; the multi-status `IN`
//! predicates are hoisted into [`ACTIVE_STATUSES_SQL`] and [`SEEDABLE_RUN_STATUSES_SQL`], each
//! scanned by a unit test through the enum's `from_str` so the SQL vocabulary can never drift.
//! Every claim charges an attempt (saturating at the cap) and bumps the fencing `claim_epoch`;
//! expired `produced` leases are reclaimable without an attempt cap (their tiles are already in
//! Kafka and must reach `confirmed`), while expired `scanning` leases are capped and dead-lettered
//! by [`PgChunkStore::reap_poisoned_chunks`] so a chunk that hard-crashes its worker cannot
//! re-scan forever. A `failed` chunk additionally waits out the retry backoff its
//! [`PgChunkStore::fail`] stamped, which is the only claim arm that reads `next_attempt_at`.
//! Depends on `domain` (the pure chunk states it mints and the typed ids) and nothing above.

use chrono::{DateTime, NaiveDate, Utc};
use cohort_core::filters::TeamId;
use cohort_core::{day_idx_of_naive_date, DayIdx};
use sqlx::types::Json;
use sqlx::{Connection, FromRow, PgPool};
use std::fmt;
use std::num::NonZeroU16;
use std::time::Duration;
use uuid::Uuid;

use crate::domain::{
    bands_for_day, person_chunk_sentinel_day, AttemptCount, Band, BandSpec, BandSpecError, ChunkId,
    ChunkLease, ChunkSpec, ChunkStatus, ClaimCandidates, ClaimEpoch, ClaimKind, ClaimedChunk,
    DaySchedule, EnqueuedChunk, Halted, PersonRange, PersonRangeError, PlannedDay, ProduceHwms,
    ProducedChunk, RunId, SChunkMs, ScanVolume, StreamedChunk, UtcMillis,
};

use super::lease::LeaseHandle;
use super::runs::RunKind;
use super::{Claimant, LeaseDuration, MaxAttempts, RenderedError, PERSISTED_ERROR_LIMIT};

/// Chunk statuses holding a live lease — the targets of the fenced heartbeat/fail/confirm writes.
const ACTIVE_STATUSES_SQL: &str = "('scanning', 'produced')";

/// Run statuses whose chunks the seeder claims and heartbeats: the SQL twin of
/// [`super::runs::RunStatus::seed_phase`].
const SEEDABLE_RUN_STATUSES_SQL: &str = "('seeding', 'trailing')";

#[derive(Debug, Clone)]
pub struct PgChunkStore {
    pool: PgPool,
}

impl PgChunkStore {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    pub async fn plan_chunks(
        &self,
        run_id: RunId,
        days: impl IntoIterator<Item = PlannedDay>,
        bands_per_day: NonZeroU16,
    ) -> Result<PlanOutcome, ChunkStoreError> {
        let mut ids = Vec::new();
        let mut dates = Vec::new();
        let mut bands = Vec::new();
        let mut holds = Vec::new();
        for PlannedDay { day, schedule } in days {
            let date = date_for_day(day).ok_or(ChunkStoreError::InvalidDay(day))?;
            let hold = match schedule {
                DaySchedule::Historical => None,
                DaySchedule::Trailing { claimable_after } => Some(
                    DateTime::<Utc>::from_timestamp_millis(claimable_after.as_i64())
                        .ok_or(ChunkStoreError::InvalidClaimableAfter(claimable_after))?,
                ),
            };
            for band in bands_for_day(day, bands_per_day) {
                ids.push(Uuid::now_v7());
                dates.push(date);
                bands.push(band.0);
                holds.push(hold);
            }
        }
        if ids.is_empty() {
            return Ok(PlanOutcome::Planned { inserted: 0 });
        }

        let result = sqlx::query_as::<_, PlanRow>(
            r#"
            WITH target_run AS (
                -- Serializes against the completion CAS: an unlocked `seeding` read is evaluated
                -- against this statement's snapshot, so a run could reach `reconciling` carrying
                -- freshly inserted pending chunks and certify completion on unseeded days.
                SELECT id, team_id
                FROM cohort_backfill_runs
                WHERE id = $1 AND status = 'seeding'
                FOR UPDATE
            ), input AS (
                SELECT * FROM unnest($2::uuid[], $3::date[], $4::smallint[], $6::timestamptz[])
                    AS u(id, day, band, claimable_after)
            ), inserted AS (
                INSERT INTO cohort_backfill_chunks
                    (id, run_id, team_id, day, band, status, claim_epoch, claimed_by, attempts,
                     last_error, tiles_produced, claimable_after, created_at, updated_at)
                SELECT u.id, r.id, r.team_id, u.day, u.band, $5, 0, '', 0, '', 0,
                       u.claimable_after, now(), now()
                FROM input u CROSS JOIN target_run r
                ON CONFLICT (run_id, day, band) DO NOTHING
                RETURNING id
            )
            SELECT EXISTS(SELECT 1 FROM target_run) AS run_seeding,
                   count(*)::bigint AS inserted
            FROM inserted
            "#,
        )
        .bind(run_id)
        .bind(ids)
        .bind(dates)
        .bind(bands)
        .bind(ChunkStatus::Pending.as_str())
        .bind(holds)
        .fetch_one(&self.pool)
        .await?;
        if !result.run_seeding {
            return Ok(PlanOutcome::RunNotSeeding);
        }
        let inserted = u64::try_from(result.inserted)
            .map_err(|_| ChunkStoreError::InvalidInsertedCount(result.inserted))?;
        Ok(PlanOutcome::Planned { inserted })
    }

    /// Try to claim the cluster-wide planning slot for one person run, so at most one replica runs
    /// the full-table ClickHouse boundary aggregation per run. The claim is a session advisory
    /// lock on a connection detached from the pool: releasing (or crashing — the socket closes)
    /// frees it, and it can never leak back into the pool still holding the lock.
    pub async fn try_claim_person_planning(
        &self,
        run_id: RunId,
    ) -> Result<Option<PersonPlanningClaim>, ChunkStoreError> {
        let mut connection = self.pool.acquire().await?.detach();
        let claimed = sqlx::query_scalar::<_, bool>(PERSON_PLANNING_LOCK_SQL)
            .bind(run_id)
            .fetch_one(&mut connection)
            .await;
        match claimed {
            Ok(true) => Ok(Some(PersonPlanningClaim {
                connection: Some(connection),
                run_id,
            })),
            Ok(false) => {
                connection.close().await.ok();
                Ok(None)
            }
            Err(error) => {
                connection.close().await.ok();
                Err(error.into())
            }
        }
    }

    /// Plan a person run's UUID-range chunks under one all-or-nothing statement: `FOR UPDATE` on
    /// the `seeding` run row plus a `NOT EXISTS` chunks gate, one row per range under the
    /// far-future sentinel day with `band` = ordinal. Deliberately no `ON CONFLICT`: under READ
    /// COMMITTED a lock-waiting loser re-checks only the locked row against its stale snapshot, so
    /// its `NOT EXISTS` can pass after the winner commits — but band 0 always exists, so the
    /// `(run, day, band)` unique constraint aborts the loser atomically. Mixing two planners'
    /// boundary sets is thereby impossible.
    pub async fn plan_person_chunks(
        &self,
        run_id: RunId,
        ranges: &[PersonRange],
    ) -> Result<PlanOutcome, ChunkStoreError> {
        if ranges.is_empty() {
            return Ok(PlanOutcome::Planned { inserted: 0 });
        }
        let mut ids = Vec::with_capacity(ranges.len());
        let mut bands = Vec::with_capacity(ranges.len());
        let mut lows = Vec::with_capacity(ranges.len());
        let mut highs: Vec<Option<Uuid>> = Vec::with_capacity(ranges.len());
        for (ordinal, range) in ranges.iter().enumerate() {
            let band =
                i16::try_from(ordinal).map_err(|_| ChunkStoreError::PersonBandOverflow(ordinal))?;
            ids.push(Uuid::now_v7());
            bands.push(band);
            lows.push(range.lo());
            highs.push(range.hi());
        }

        let result = sqlx::query_as::<_, PlanRow>(
            r#"
            WITH target_run AS (
                SELECT id, team_id
                FROM cohort_backfill_runs
                WHERE id = $1 AND status = 'seeding'
                FOR UPDATE
            ), input AS (
                SELECT * FROM unnest($2::uuid[], $3::smallint[], $4::uuid[], $5::uuid[])
                    AS u(id, band, lo, hi)
            ), inserted AS (
                INSERT INTO cohort_backfill_chunks
                    (id, run_id, team_id, day, band, person_range_lo, person_range_hi, status,
                     claim_epoch, claimed_by, attempts, last_error, tiles_produced,
                     created_at, updated_at)
                SELECT u.id, r.id, r.team_id, $6, u.band, u.lo, u.hi, $7, 0, '', 0, '', 0,
                       now(), now()
                FROM input u CROSS JOIN target_run r
                WHERE NOT EXISTS (SELECT 1 FROM cohort_backfill_chunks WHERE run_id = $1)
                RETURNING id
            )
            SELECT EXISTS(SELECT 1 FROM target_run) AS run_seeding,
                   count(*)::bigint AS inserted
            FROM inserted
            "#,
        )
        .bind(run_id)
        .bind(ids)
        .bind(bands)
        .bind(lows)
        .bind(highs)
        .bind(person_chunk_sentinel_day())
        .bind(ChunkStatus::Pending.as_str())
        .fetch_one(&self.pool)
        .await;
        let result = match result {
            Ok(result) => result,
            // Only the (run, day, band) constraint means "another planner won"; any other unique
            // violation must surface rather than be misread as an idempotent no-op.
            Err(sqlx::Error::Database(error))
                if error.code().as_deref() == Some("23505")
                    && error.constraint() == Some("cohort_bfc_run_day_band_uq") =>
            {
                return Ok(PlanOutcome::AlreadyPlanned);
            }
            Err(error) => return Err(error.into()),
        };
        if !result.run_seeding {
            return Ok(PlanOutcome::RunNotSeeding);
        }
        if result.inserted == 0 {
            return Ok(PlanOutcome::AlreadyPlanned);
        }
        let inserted = u64::try_from(result.inserted)
            .map_err(|_| ChunkStoreError::InvalidInsertedCount(result.inserted))?;
        Ok(PlanOutcome::Planned { inserted })
    }

    pub async fn claim_next(
        &self,
        candidates: &ClaimCandidates,
        claimant: &Claimant,
        lease: LeaseDuration,
        max_attempts: MaxAttempts,
    ) -> Result<Option<Claim>, ChunkStoreError> {
        let run_ids = candidates
            .iter()
            .map(|candidate| candidate.run_id.0)
            .collect::<Vec<_>>();
        let team_loads = candidates
            .iter()
            .map(|candidate| i64::from(candidate.load.team))
            .collect::<Vec<_>>();
        let run_loads = candidates
            .iter()
            .map(|candidate| i64::from(candidate.load.run))
            .collect::<Vec<_>>();
        let sql = format!(
            r#"
            WITH candidate AS (
                SELECT * FROM unnest($1::uuid[], $10::bigint[], $11::bigint[])
                    AS u(run_id, team_load, run_load)
            ), next_chunk AS (
                SELECT c.id, c.status IN {active} AS was_reclaim
                FROM cohort_backfill_chunks c
                JOIN candidate u ON u.run_id = c.run_id
                JOIN cohort_backfill_runs r ON r.id = c.run_id
                -- `ANY($1)` repeats the join so that the planner reads only the claimable chunks
                -- through `cohort_bfc_run_status_day_idx`. With the join alone, it probes each
                -- candidate through the `(run_id, day, band)` key and filters out every
                -- confirmed chunk row by row. Only a plan built for the bound arrays does this,
                -- so the statement is not cached.
                WHERE c.run_id = ANY($1) AND r.status IN {seedable}
                  -- A trailing day is scanned only once it has ended and its run is `trailing`.
                  -- Claimed while the run still seeds, it would lose its lease when the
                  -- reconciling CAS fires, because the heartbeat joins the same run statuses.
                  -- The newest-day index depends on this too. Readiness does not wait for a
                  -- trailing day. A trailing day scanned while a historical chunk still sends
                  -- would make that chunk skip its tiles, and readiness could finish before the
                  -- trailing day's tile lands.
                  AND (c.claimable_after IS NULL
                       OR (c.claimable_after <= now() AND r.status = 'trailing'))
                  AND ((c.status = $7 AND c.attempts < $4)
                       -- Only the failed→claim transition is gated on the backoff stamp. A chunk
                       -- that never ran (`pending`) and an expired lease reclaim both stay
                       -- immediate: neither is the tight failure loop the wait exists to break.
                       OR (c.status = $8 AND c.attempts < $4
                           AND (c.next_attempt_at IS NULL OR c.next_attempt_at <= now()))
                       OR (c.status = $6 AND c.lease_expires_at < now())
                       OR (c.status = $5 AND c.lease_expires_at < now() AND c.attempts < $4))
                -- `r.backfill_kind = $9` is true only for person runs, and `false` sorts first,
                -- so behavioral chunks (readiness-gating, fence-sensitive) claim before person
                -- chunks. The kind key must lead: person chunks sit on a far-future sentinel day,
                -- which `day DESC` alone would claim first. Of the claimable chunks, only trailing
                -- days have a `claimable_after`, so the next key puts them ahead of every load,
                -- because their cohorts already serve as ready and under-count the boundary day
                -- until the trailing tiles apply. The load keys come next: the team holding
                -- the fewest of this replica's slots of the kind goes first, then the run holding
                -- the fewest. A heavy run's chunk holds its slot for hours while a cheap one frees
                -- its slot in seconds, so under day order alone the heavy run ends up holding
                -- every slot and every other run advances one day per freed slot. A team alone
                -- still gets every slot. Among equal loads, newest day first lets a short-window
                -- cohort, whose days are all recent, finish without waiting behind a long run's
                -- older days.
                ORDER BY (r.backfill_kind = $9), (c.claimable_after IS NULL), u.team_load,
                         u.run_load, c.day DESC, c.band
                LIMIT 1
                FOR UPDATE OF c SKIP LOCKED
            )
            UPDATE cohort_backfill_chunks c
            SET status = $5, claim_epoch = c.claim_epoch + 1, claimed_by = $2,
                claimed_at = now(), lease_expires_at = now() + make_interval(secs => $3),
                s_chunk_at = now(), next_attempt_at = NULL,
                attempts = CASE WHEN c.attempts < $4 THEN c.attempts + 1 ELSE c.attempts END,
                updated_at = now()
            FROM next_chunk
            WHERE c.id = next_chunk.id
            RETURNING c.id, c.run_id, c.team_id, c.day, c.band, c.claim_epoch, c.attempts,
                      c.person_range_lo, c.person_range_hi,
                      (extract(epoch FROM c.s_chunk_at) * 1000)::bigint AS s_chunk_ms,
                      (SELECT count(*) FROM cohort_backfill_chunks s
                       WHERE s.run_id = c.run_id AND s.day = c.day) AS num_bands,
                      next_chunk.was_reclaim
            "#,
            active = ACTIVE_STATUSES_SQL,
            seedable = SEEDABLE_RUN_STATUSES_SQL,
        );
        let row = sqlx::query_as::<_, ClaimedRow>(&sql)
            // Postgres can switch a cached statement to a generic plan after five runs. That plan
            // loops over the candidates and reads every chunk of each, confirmed ones included.
            .persistent(false)
            .bind(run_ids)
            .bind(claimant.as_str())
            .bind(lease.as_secs())
            .bind(max_attempts.get())
            .bind(ChunkStatus::Scanning.as_str())
            .bind(ChunkStatus::Produced.as_str())
            .bind(ChunkStatus::Pending.as_str())
            .bind(ChunkStatus::Failed.as_str())
            .bind(RunKind::PersonProperty.as_str())
            .bind(team_loads)
            .bind(run_loads)
            .fetch_optional(&self.pool)
            .await?;

        row.map(|row| self.assemble_claim(row, claimant, lease))
            .transpose()
    }

    /// Dead-letter `scanning` chunks whose lease expired at the attempt cap. A clean failure caps
    /// out through `fail` plus the claim gate, but a worker that dies without reaching `fail`
    /// (panic, OOM, SIGKILL) leaves its chunk `scanning`; once its attempts saturate, the claim
    /// predicate no longer reclaims it, and this sweep moves it to the same terminal `failed`
    /// state a clean cap-out reaches, so it surfaces to operators instead of stalling invisibly.
    /// Returns the number of chunks dead-lettered.
    pub async fn reap_poisoned_chunks(
        &self,
        run_ids: &[RunId],
        max_attempts: MaxAttempts,
    ) -> Result<u64, ChunkStoreError> {
        if run_ids.is_empty() {
            return Ok(0);
        }
        let run_ids = run_ids.iter().map(|run_id| run_id.0).collect::<Vec<_>>();
        let result = sqlx::query(
            r#"
            UPDATE cohort_backfill_chunks
            SET status = $3, last_error = left($4, $5), updated_at = now()
            WHERE run_id = ANY($1) AND status = $2
              AND lease_expires_at < now() AND attempts >= $6
            "#,
        )
        .bind(run_ids)
        .bind(ChunkStatus::Scanning.as_str())
        .bind(ChunkStatus::Failed.as_str())
        .bind("lease expired at the attempt cap without a clean failure; worker crash suspected")
        .bind(PERSISTED_ERROR_LIMIT)
        .bind(max_attempts.get())
        .execute(&self.pool)
        .await?;
        Ok(result.rows_affected())
    }

    /// Runs holding ≥1 chunk that exhausted its retry budget, with a representative chunk and its
    /// persisted error.
    ///
    /// The `attempts >= max_attempts` half is the whole point: a `failed` chunk still under the cap
    /// is reclaimable by [`Self::claim_next`] and will retry, while a capped one never will. Only
    /// the capped ones are terminal, and only they wedge the run — the completion CAS demands every
    /// readiness chunk `confirmed`, so a run carrying one sits in `seeding` forever and holds its
    /// cohort's uniqueness slot against every future run, and a `trailing` run carrying one never
    /// completes.
    pub async fn runs_with_exhausted_chunks(
        &self,
        run_ids: &[RunId],
        max_attempts: MaxAttempts,
    ) -> Result<Vec<ExhaustedRun>, ChunkStoreError> {
        if run_ids.is_empty() {
            return Ok(Vec::new());
        }
        let run_ids = run_ids.iter().map(|run_id| run_id.0).collect::<Vec<_>>();
        let rows = sqlx::query_as::<_, ExhaustedRunRow>(
            r#"
            SELECT DISTINCT ON (c.run_id)
                   c.run_id,
                   count(*) OVER (PARTITION BY c.run_id) AS exhausted,
                   c.id::text AS chunk_id,
                   c.last_error
            FROM cohort_backfill_chunks c
            JOIN cohort_backfill_runs r ON r.id = c.run_id
            WHERE c.run_id = ANY($1) AND c.status = $2 AND c.attempts >= $3
              -- Readiness does not wait for a trailing chunk, so its failure fails the run only
              -- once the run has stamped readiness and is `trailing`.
              AND (c.claimable_after IS NULL OR r.status = 'trailing')
            ORDER BY c.run_id, c.id
            "#,
        )
        .bind(run_ids)
        .bind(ChunkStatus::Failed.as_str())
        .bind(max_attempts.get())
        .fetch_all(&self.pool)
        .await?;
        Ok(rows.into_iter().map(ExhaustedRun::from).collect())
    }

    /// The mark for both paths: every seed is already enqueued, so only the count sent crosses the
    /// `scanning`→`produced` CAS.
    pub async fn mark_produced(
        &self,
        chunk: StreamedChunk,
    ) -> Result<EnqueuedChunk, Halted<StreamedChunk, ChunkStoreError>> {
        let spec = chunk.spec();
        let seeds_produced = chunk.seeds_produced();
        let volume = chunk.volume();
        if let Err(error) = self
            .mark_produced_raw(spec.lease, seeds_produced, volume)
            .await
        {
            return Err(Halted::failed(chunk, error));
        }
        Ok(EnqueuedChunk::new(spec, seeds_produced))
    }

    pub async fn confirm(
        &self,
        chunk: ProducedChunk,
    ) -> Result<ChunkLease, Halted<ProducedChunk, ChunkStoreError>> {
        let spec = chunk.spec();
        if let Err(error) = self.confirm_raw(spec.lease, chunk.hwms()).await {
            return Err(Halted::failed(chunk, error));
        }
        Ok(spec.lease)
    }

    /// Fail a chunk for retry, holding it out of the claim gate for `retry_delay`.
    ///
    /// A post-mark failure is stamped too, so a chunk whose `confirm` lost a race waits before it
    /// retries. The wait is not free: it is added to the run's wall-clock, and the run cannot
    /// complete until every chunk is `confirmed`. It is charged anyway because the retry re-scans
    /// from the start rather than re-running the failed write, and a `confirm` that failed once is
    /// likely to fail again immediately. At the first attempts the wait is small next to the day
    /// band it spaces out; by the time it reaches the cap it plausibly exceeds that scan, which is
    /// the point at which the attempt budget, not the wait, is what should stop the chunk.
    pub async fn fail(
        &self,
        lease: ChunkLease,
        error: &RenderedError,
        retry_delay: Duration,
    ) -> Result<(), ChunkStoreError> {
        let updated = sqlx::query_scalar::<_, ChunkId>(&format!(
            r#"
            UPDATE cohort_backfill_chunks
            SET status = $3, last_error = left($4, $5),
                next_attempt_at = now() + make_interval(secs => $6), updated_at = now()
            WHERE id = $1 AND claim_epoch = $2 AND status IN {active}
            RETURNING id
            "#,
            active = ACTIVE_STATUSES_SQL,
        ))
        .bind(lease.chunk_id())
        .bind(lease.epoch())
        .bind(ChunkStatus::Failed.as_str())
        .bind(error.as_str())
        .bind(PERSISTED_ERROR_LIMIT)
        .bind(retry_delay.as_secs_f64())
        .fetch_optional(&self.pool)
        .await?;
        fenced(updated, lease, ChunkOperation::Fail)
    }

    pub async fn unclaim(&self, lease: ChunkLease) -> Result<(), ChunkStoreError> {
        let updated = sqlx::query_scalar::<_, ChunkId>(
            r#"
            UPDATE cohort_backfill_chunks
            SET status = $3, claimed_by = '', claimed_at = NULL, lease_expires_at = NULL,
                attempts = GREATEST(attempts - 1, 0), updated_at = now()
            WHERE id = $1 AND claim_epoch = $2 AND status = 'scanning'
            RETURNING id
            "#,
        )
        .bind(lease.chunk_id())
        .bind(lease.epoch())
        .bind(ChunkStatus::Pending.as_str())
        .fetch_optional(&self.pool)
        .await?;
        fenced(updated, lease, ChunkOperation::Unclaim)
    }

    /// Unconfirmed chunks the runs' readiness still waits for, which leaves out trailing chunks.
    pub async fn remaining_chunks(&self, run_ids: &[RunId]) -> Result<u64, ChunkStoreError> {
        if run_ids.is_empty() {
            return Ok(0);
        }
        let run_ids = run_ids.iter().map(|run_id| run_id.0).collect::<Vec<_>>();
        let count: i64 = sqlx::query_scalar(
            r#"
            SELECT count(*)::bigint
            FROM cohort_backfill_chunks
            WHERE run_id = ANY($1) AND status <> $2 AND claimable_after IS NULL
            "#,
        )
        .bind(run_ids)
        .bind(ChunkStatus::Confirmed.as_str())
        .fetch_one(&self.pool)
        .await?;
        u64::try_from(count).map_err(|_| ChunkStoreError::InvalidRemainingCount(count))
    }

    /// Progress over the chunks the run's readiness waits for, which leaves out its trailing chunks.
    pub async fn chunk_progress(&self, run_id: RunId) -> Result<ChunkProgress, ChunkStoreError> {
        let row = sqlx::query_as::<_, ChunkProgressRow>(
            r#"
            SELECT count(*)::bigint AS total,
                   count(*) FILTER (WHERE status <> $2)::bigint AS remaining
            FROM cohort_backfill_chunks
            WHERE run_id = $1 AND claimable_after IS NULL
            "#,
        )
        .bind(run_id)
        .bind(ChunkStatus::Confirmed.as_str())
        .fetch_one(&self.pool)
        .await?;
        let total = u64::try_from(row.total)
            .map_err(|_| ChunkStoreError::InvalidChunkProgress(row.total, row.remaining))?;
        let remaining = u64::try_from(row.remaining)
            .map_err(|_| ChunkStoreError::InvalidChunkProgress(row.total, row.remaining))?;
        if remaining > total {
            return Err(ChunkStoreError::InvalidChunkProgress(
                row.total,
                row.remaining,
            ));
        }
        Ok(ChunkProgress { total, remaining })
    }

    pub(crate) async fn heartbeat(
        &self,
        lease: ChunkLease,
        claimant: &Claimant,
        lease_duration: LeaseDuration,
    ) -> Result<(), ChunkStoreError> {
        let updated = sqlx::query_scalar::<_, ChunkId>(&format!(
            r#"
            UPDATE cohort_backfill_chunks c
            SET lease_expires_at = now() + make_interval(secs => $3), updated_at = now()
            FROM cohort_backfill_runs r
            WHERE c.id = $1 AND c.claim_epoch = $2 AND c.claimed_by = $4
              AND c.status IN {active}
              AND r.id = c.run_id AND r.status IN {seedable}
            RETURNING c.id
            "#,
            active = ACTIVE_STATUSES_SQL,
            seedable = SEEDABLE_RUN_STATUSES_SQL,
        ))
        .bind(lease.chunk_id())
        .bind(lease.epoch())
        .bind(lease_duration.as_secs())
        .bind(claimant.as_str())
        .fetch_optional(&self.pool)
        .await?;
        fenced(updated, lease, ChunkOperation::Heartbeat)
    }

    /// The `scanning`→`produced` CAS, recording what the chunk produced and what its scan moved.
    ///
    /// Only this transition writes the byte columns, matching `tiles_produced`: a failed or
    /// cancelled chunk is re-scanned from the start, so persisting a partial volume against it
    /// would double-count once the retry succeeds. The counters still cover those scans.
    ///
    /// A retry that turns out vacuous — every referencing window slid past the day, or the plan
    /// resolved to no scan at all — reports zero and overwrites whatever the earlier attempt
    /// measured. The ledger row then reads like a chunk that never moved anything. That is the same
    /// trade `tiles_produced` already makes, and the counters keep the real bytes; only the
    /// per-chunk column loses them.
    pub(crate) async fn mark_produced_raw(
        &self,
        lease: ChunkLease,
        tiles_produced: u64,
        volume: ScanVolume,
    ) -> Result<(), ChunkStoreError> {
        let tiles_produced = i64::try_from(tiles_produced)
            .map_err(|_| ChunkStoreError::TilesProducedOutOfRange(tiles_produced))?;
        let received_bytes = scan_bytes_column(volume.received_bytes())?;
        let decoded_bytes = scan_bytes_column(volume.decoded_bytes())?;
        let updated = sqlx::query_scalar::<_, ChunkId>(
            r#"
            UPDATE cohort_backfill_chunks
            SET status = $3, tiles_produced = $4, scan_received_bytes = $5,
                scan_decoded_bytes = $6, updated_at = now()
            WHERE id = $1 AND claim_epoch = $2 AND status = 'scanning'
            RETURNING id
            "#,
        )
        .bind(lease.chunk_id())
        .bind(lease.epoch())
        .bind(ChunkStatus::Produced.as_str())
        .bind(tiles_produced)
        .bind(received_bytes)
        .bind(decoded_bytes)
        .fetch_optional(&self.pool)
        .await?;
        fenced(updated, lease, ChunkOperation::MarkProduced)
    }

    pub(crate) async fn confirm_raw(
        &self,
        lease: ChunkLease,
        produce_hwms: &ProduceHwms,
    ) -> Result<(), ChunkStoreError> {
        let updated = sqlx::query_scalar::<_, ChunkId>(&format!(
            r#"
            UPDATE cohort_backfill_chunks
            SET status = $3, confirmed_at = now(), produce_hwms = $4, updated_at = now()
            WHERE id = $1 AND claim_epoch = $2 AND status IN {active}
            RETURNING id
            "#,
            active = ACTIVE_STATUSES_SQL,
        ))
        .bind(lease.chunk_id())
        .bind(lease.epoch())
        .bind(ChunkStatus::Confirmed.as_str())
        .bind(Json(produce_hwms))
        .fetch_optional(&self.pool)
        .await?;
        fenced(updated, lease, ChunkOperation::Confirm)
    }

    fn assemble_claim(
        &self,
        row: ClaimedRow,
        claimant: &Claimant,
        lease_duration: LeaseDuration,
    ) -> Result<Claim, ChunkStoreError> {
        let band = BandSpec::new(row.band.0, row.num_bands)?;
        let lease = ChunkLease::new(row.id, row.run_id, row.claim_epoch);
        // `lo IS NOT NULL` ⇔ person chunk. Beyond the empty `lo == hi` shape, the decode must not
        // reject ranges (no ordering checks) — a rejected claim strands the chunk `scanning`.
        let person_range = row
            .person_range_lo
            .map(|lo| PersonRange::new(lo, row.person_range_hi))
            .transpose()?;
        let spec = ChunkSpec {
            lease,
            team_id: TeamId(row.team_id),
            day: day_idx_of_naive_date(row.day),
            band,
            s_chunk: SChunkMs(row.s_chunk_ms),
            person_range,
            attempt: AttemptCount::from_row(row.attempts),
        };
        let handle = LeaseHandle::start(self.clone(), lease, claimant.clone(), lease_duration);
        Ok(Claim {
            chunk: ClaimedChunk::new(spec),
            kind: ClaimKind::from_was_reclaim(row.was_reclaim),
            lease: handle,
        })
    }
}

/// A freshly claimed chunk: its pure state, whether it was a reclaim (for the app's metric), and the
/// heartbeat handle to hold alongside it. Assembled entirely by the store — the pure state never
/// calls back into the store.
pub struct Claim {
    pub chunk: ClaimedChunk,
    pub kind: ClaimKind,
    pub lease: LeaseHandle,
}

const PERSON_PLANNING_LOCK_SQL: &str = "SELECT pg_try_advisory_lock(hashtextextended($1::text, 0))";

/// The held cluster-wide planning claim; release explicitly, or drop to free via connection close.
pub struct PersonPlanningClaim {
    connection: Option<sqlx::PgConnection>,
    run_id: RunId,
}

impl PersonPlanningClaim {
    pub async fn release(mut self) {
        if let Some(mut connection) = self.connection.take() {
            sqlx::query("SELECT pg_advisory_unlock(hashtextextended($1::text, 0))")
                .bind(self.run_id)
                .execute(&mut connection)
                .await
                .ok();
            connection.close().await.ok();
        }
    }
}

/// The result of planning a run's chunks. `RunNotSeeding` is control flow (the run left the
/// `seeding` state between discovery and planning), not an error; `AlreadyPlanned` is the person
/// planner's all-or-nothing gate reporting another planner won.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum PlanOutcome {
    Planned { inserted: u64 },
    RunNotSeeding,
    AlreadyPlanned,
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum ChunkOperation {
    Heartbeat,
    MarkProduced,
    Confirm,
    Fail,
    Unclaim,
}

impl fmt::Display for ChunkOperation {
    fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
        formatter.write_str(match self {
            Self::Heartbeat => "heartbeat",
            Self::MarkProduced => "mark-produced",
            Self::Confirm => "confirm",
            Self::Fail => "fail",
            Self::Unclaim => "unclaim",
        })
    }
}

#[derive(Debug, thiserror::Error)]
pub enum ChunkStoreError {
    #[error("PostgreSQL chunk operation failed")]
    Pg(#[from] sqlx::Error),
    #[error("chunk {lease:?} lost its lease during {operation}")]
    LeaseLost {
        lease: ChunkLease,
        operation: ChunkOperation,
    },
    #[error("tiles produced {0} exceeds PostgreSQL bigint range")]
    TilesProducedOutOfRange(u64),
    #[error("scan byte count {0} exceeds PostgreSQL bigint range")]
    ScanBytesOutOfRange(u64),
    #[error("day index {0} is outside chrono's date range")]
    InvalidDay(DayIdx),
    #[error("claim hold {0:?} is outside chrono's timestamp range")]
    InvalidClaimableAfter(UtcMillis),
    #[error(transparent)]
    Band(#[from] BandSpecError),
    #[error(transparent)]
    PersonRange(#[from] PersonRangeError),
    #[error("person chunk ordinal {0} exceeds the band column's smallint range")]
    PersonBandOverflow(usize),
    #[error("chunk planner returned invalid insert count {0}")]
    InvalidInsertedCount(i64),
    #[error("chunk counter returned invalid remaining count {0}")]
    InvalidRemainingCount(i64),
    #[error("chunk counter returned invalid progress total={0}, remaining={1}")]
    InvalidChunkProgress(i64, i64),
}

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct ChunkProgress {
    total: u64,
    remaining: u64,
}

impl ChunkProgress {
    pub const fn total(self) -> u64 {
        self.total
    }

    pub const fn remaining(self) -> u64 {
        self.remaining
    }
}

/// A run wedged by chunks that exhausted their retry budget, as reported by
/// [`PgChunkStore::runs_with_exhausted_chunks`].
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ExhaustedRun {
    pub run_id: RunId,
    pub exhausted: u64,
    /// One of the exhausted chunks, for the operator-facing error text. Which one is arbitrary but
    /// stable (lowest id) — the point is a concrete row to go read, not a complete list.
    pub chunk_id: String,
    /// That same chunk's error, never another one's: an operator handed chunk A with chunk B's
    /// failure text reads the wrong row. [`NO_ERROR_RECORDED`] when the chunk recorded none.
    pub last_error: String,
}

/// Stands in for an empty `last_error` so the operator-facing message never ends in a bare colon.
/// A capped chunk normally carries one, but `attempts` can reach the cap without a persisted error.
pub const NO_ERROR_RECORDED: &str = "no error recorded";

impl From<ExhaustedRunRow> for ExhaustedRun {
    fn from(row: ExhaustedRunRow) -> Self {
        Self {
            run_id: row.run_id,
            // `count(*) OVER (PARTITION BY run_id)` is ≥ 1 and bounded by the run's chunk count, so
            // the cast cannot lose information.
            exhausted: row.exhausted.max(0) as u64,
            chunk_id: row.chunk_id.unwrap_or_default(),
            last_error: row
                .last_error
                .filter(|error| !error.is_empty())
                .unwrap_or_else(|| NO_ERROR_RECORDED.to_owned()),
        }
    }
}

#[derive(Debug, FromRow)]
struct ExhaustedRunRow {
    run_id: RunId,
    exhausted: i64,
    chunk_id: Option<String>,
    last_error: Option<String>,
}

#[derive(Debug, FromRow)]
struct ClaimedRow {
    id: ChunkId,
    run_id: RunId,
    team_id: i32,
    day: NaiveDate,
    band: Band,
    claim_epoch: ClaimEpoch,
    attempts: i32,
    person_range_lo: Option<Uuid>,
    person_range_hi: Option<Uuid>,
    s_chunk_ms: i64,
    num_bands: i64,
    was_reclaim: bool,
}

#[derive(Debug, FromRow)]
struct PlanRow {
    run_seeding: bool,
    inserted: i64,
}

#[derive(Debug, FromRow)]
struct ChunkProgressRow {
    total: i64,
    remaining: i64,
}

fn fenced(
    updated: Option<ChunkId>,
    lease: ChunkLease,
    operation: ChunkOperation,
) -> Result<(), ChunkStoreError> {
    updated
        .map(|_| ())
        .ok_or(ChunkStoreError::LeaseLost { lease, operation })
}

fn scan_bytes_column(bytes: u64) -> Result<i64, ChunkStoreError> {
    i64::try_from(bytes).map_err(|_| ChunkStoreError::ScanBytesOutOfRange(bytes))
}

fn date_for_day(day: DayIdx) -> Option<NaiveDate> {
    let epoch = NaiveDate::from_ymd_opt(1970, 1, 1)?;
    epoch.checked_add_signed(chrono::Duration::days(i64::from(day)))
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::store::runs::RunStatus;

    fn sql_fragment_names(fragment: &str) -> impl Iterator<Item = &str> {
        fragment
            .trim_matches(|c| c == '(' || c == ')')
            .split(',')
            .map(|token| token.trim().trim_matches('\''))
    }

    #[test]
    fn seedable_run_statuses_sql_names_exactly_the_seed_phase_statuses() {
        let named = sql_fragment_names(SEEDABLE_RUN_STATUSES_SQL)
            .map(|status| status.parse::<RunStatus>())
            .collect::<Result<Vec<_>, _>>()
            .expect("SQL fragment names a non-vocabulary run status");
        for status in RunStatus::ALL {
            assert_eq!(
                named.contains(&status),
                status.seed_phase().is_some(),
                "{status:?} disagrees between the SQL fragment and seed_phase()"
            );
        }
    }

    #[test]
    fn hoisted_status_fragments_only_name_live_chunk_statuses() {
        for status in sql_fragment_names(ACTIVE_STATUSES_SQL) {
            assert!(
                status.parse::<ChunkStatus>().is_ok(),
                "SQL fragment names non-vocabulary status {status:?}"
            );
        }
    }
}