Project sort_score Worker: Logic Review

Date: 2026-07-30 Subject: project_sorting_scores worker — project_score used to sort bookable projects Scope: 147 bookable published projects (the population the worker actually scores) Data Sources: sadb_real_estate_projects, sadb_stats_projects_stats, sadb_real_estate_project_interested_users, sadb_user_real_estate_project_views

Summary

The worker computes project_score = pacing × (quality + responsiveness + random) / 3.1 and writes it to MySQL and Elasticsearch. Reviewed against production data, the design is sound but one defect inverts the ranking for three hours a day, and a second systematically weakens the pacer the rest of the time.

#	Issue	Severity	Effect
1	Pacing allowance uses KSA hour against a UTC-day view counter	Critical	Every project with traffic scores exactly `0` during 00:00–02:59 KSA — 14.7% of daily project views served by an inverted, tie-collapsed ranking
2	Allowance is linear in time; real demand is not	High	Pacer over-permits all day — worst absolute gap 12.7pt at 12:00 KSA, worst relative +57% at 09:00 KSA
3	CRM webhook responsiveness floor of 0.6	High	Not a floor but a top-decile grant: +2.9 standard deviations of score for 11 projects
4	`Math.random()` re-rolled every run	Medium	~±24 rank positions of churn among the 80% of projects packed in a narrow score band
5	`cappedViews = 0` when `todayImpressions = 0`	Medium	3–7 project-days/day get pacing 0.98 + quality 1.0 regardless of real performance
6	Responsiveness has no smoothing or minimum-volume guard	Low (latent)	Inconsistent with the smoothing applied to quality; a trap as inventory grows
7	`project.icon != null` treats `''` as premium	Low (needs code check)	Empty-string icons would silently receive the premium cap

Verified as not problems: the day-boundary formula in getTodayStatsByProjectId correctly matches the stats writer; searcher_info_requested_at is per-lead and unaffected by the once-per-user WhatsApp flow; no negative response times exist; impression coverage is fine for bookable projects.

Population Being Scored

The worker filters to bookable = true AND is_bookable_project_published = true — 147 projects, not the full 596-project inventory.

Attribute	Count	Note
Bookable + published	147	The scored population
Has `icon` (premium cap tier)	54
Has `owner_webhook_url` (CRM floor)	11
Has explicit `daily_views_cap > 0`	0	Every project falls back to the config defaults

Daily views per project (2026-07-16 → 2026-07-29):

Tier	Projects	Median	P90	Max
Premium (has icon)	54	48.5	115.0	552
Standard (no icon)	93	36.0	101.8	5,233

Current score distribution: min 0.222, p25 0.375, median 0.425, p75 0.457, max 0.627, sd 0.062. 117 of 147 projects (80%) sit between 0.35 and 0.50 — the scores are tightly packed, which amplifies issues 3 and 4.

Issue 1 (Critical): Pacing Uses KSA Hour Against a UTC-Day Counter

The mismatch

getTodayStatsByProjectId derives the day key as parseInt(Date.now() / (1000*60*60*24)) — days since epoch, i.e. the UTC day. This is correct and matches both increment_stats.js and the empirically verified rollover of sadb_stats_projects_stats (first write just after 00:00 UTC, last at 23:59 UTC — see the pacing bucket finding). So todayViews accumulates over [00:00 UTC, 24:00 UTC) = 03:00 → 03:00 Riyadh.

But the denominator uses Riyadh hour:

const currentHour = (new Date().getUTCHours() + SAUDI_UTC_OFFSET_HOURS) % 24;   // KSA hour
const allowedViews = Math.max(dailyViewsCap * ((currentHour + 1) / 24), 1);      // fraction of a UTC day

The numerator is on the UTC day; the denominator is on the Riyadh day. They are 3 hours out of phase, and because of the % 24 wrap the error is not a mild 3-hour lead — it collapses at the end of the counter’s day:

KSA hour	UTC hour	Counter day actually elapsed	Code allows	Verdict
03:00	00	4.2% (day just reset)	16.7%	4x too generous
12:00	09	41.7%	54.2%	too generous
23:00	20	87.5%	100%	too generous
00:00	21	91.7%	4.2%	22x too strict
01:00	22	95.8%	8.3%	11x too strict
02:00	23	100%	12.5%	8x too strict

Consequence: scores round to exactly zero

PACING_BETA = 4 (derived from the code’s own comment: ratio 2.0 → 0.018 and ratio 0.5 → 0.881 both solve to β = 4). Simulating a project whose intraday accumulation follows the measured demand curve, at 100% and 25% of its daily cap:

UTC hour	KSA hour	Demand elapsed	Code allowance	pacing @100% cap	pacing @25% cap
0	03	0.0331	0.1667	0.961	0.978
9	12	0.2894	0.5417	0.866	0.970
14	17	0.5356	0.7500	0.758	0.964
20	23	0.8533	1.0000	0.643	0.959
21	00	0.9059	0.0417	0.000000	0.000000
22	01	0.9577	0.0833	0.000000	0.000557
23	02	1.0000	0.1250	0.000000	0.017986

The true values at KSA 00:00 are ~1e-36 (at cap) and ~2e-8 (at quarter-cap). round6 maps anything below 5e-7 to exactly 0, so project_score is written as 0 for essentially every project with meaningful traffic during those three hours.

Solving for what survives: a project keeps a non-zero score at KSA 00:00 only if it ends the day below ~21% of its daily cap (at KSA 02:00, below ~58%).

Two things follow, and the second is worse than the first:

Inversion — the only projects with a non-zero score during the busiest hours are those with the least traffic relative to their cap.
Tie collapse — everyone else is tied at exactly 0, so Elasticsearch falls back to its internal tiebreak. That order is arbitrary but stable, so the same projects win the tie every time rather than the position rotating.

These are the peak hours. KSA 00:00, 01:00 and 02:00 carry 5.26% + 5.18% + 4.23% = 14.7% of all daily project views.

Fix

The numerator is already on the UTC day, so the denominator must be too. Combined with issue 2, the one-line fix is to index the measured demand curve by UTC hour:

// Cumulative share of a day's project-view demand by end of each UTC hour.
// Source: findings/2026-07-30_project_hourly_views_pacing_buckets
const HOURLY_VIEW_CUM = [
  0.03309, 0.06252, 0.09216, 0.11406, 0.13539, 0.15926, 0.18634, 0.21671,
  0.25046, 0.28944, 0.33680, 0.38641, 0.43463, 0.48407, 0.53555, 0.58584,
  0.63990, 0.69181, 0.74498, 0.79958, 0.85332, 0.90590, 0.95772, 1.00000,
];

const currentHourUtc = new Date().getUTCHours();               // counter's day, not KSA
const allowedViews = Math.max(dailyViewsCap * HOURLY_VIEW_CUM[currentHourUtc], 1);

Keep the KSA hour for the log line if it is useful to operators, but it must not enter the pacing math.

Issue 2 (High): Linear Allowance vs. Real Demand

Even after the phase fix, (hour + 1) / 24 assumes project views arrive uniformly. They do not — demand is back-loaded, with a 2.5x peak-to-trough swing. Linear allowance therefore over-permits at every hour of the day:

UTC hour	KSA hour	Real demand elapsed	Linear allowance	Gap	Over-permissive by
3	06	11.4%	16.7%	5.3pt	+46%
6	09	18.6%	29.2%	10.5pt	+57% (worst relative)
9	12	28.9%	41.7%	12.7pt (worst absolute)	+44%
10	13	33.7%	45.8%	12.2pt	+36%
13	16	48.4%	58.3%	9.9pt	+21%
17	20	69.2%	75.0%	5.8pt	+8%
22	01	95.8%	95.8%	0.1pt	0%

A project can burn most of its daily cap by mid-afternoon while the pacer still reads it as on schedule; the correction then arrives late, concentrated into the evening.

Using the demand curve instead makes the sigmoid behave as documented. The comment says pacing is “0.5 at exactly the cap” — with a demand-aware allowance that holds at every hour (a project tracking exactly at cap returns 0.5 all day, since ratio = U·C[u]/C[u] = U). With a linear allowance the same project drifts between 0.69 and 0.52, and with the current code it swings from 0.96 to 0.

Issue 3 (High): The CRM Webhook Floor Is a Top-Decile Grant

The intent is defensible — projects with owner_webhook_url handle leads in their own CRM, so searcher_info_requested_at understates them. But 0.6 is not calibrated against what other projects actually earn.

Replicating RESPONSIVENESS_SQL over a 30-day window:

Group	Projects	Avg computed responsiveness	Median	Applied after floor
Has webhook	11	0.0515	0.000	0.600
No webhook	136	0.3071	0.310	0.307

The premise checks out: webhook projects genuinely do not populate the column (median computed responsiveness 0.0). But the remedy overshoots — only 10 of 136 non-webhook projects score above 0.6, so the floor places all 11 webhook projects in the top ~7% of the responsiveness distribution.

Score impact: responsiveness enters as pacing × responsiveness / 3.1, so moving 0.05 → 0.6 adds 0.55 / 3.1 × pacing ≈ +0.17 to project_score at a typical pacing of ~0.97. Against a cross-project standard deviation of 0.062, that is +2.8 sd — enough to pin those projects near the top of the ranking regardless of their views, CTR or actual lead handling.

Recommendation: set the floor to the non-webhook median (~0.31), not 0.6 — that removes the unearned penalty without granting an unearned advantage. Better still, measure webhook responsiveness from the CRM callback (webhook delivery ACK or lead status change) so these projects are scored on evidence rather than a constant.

Issue 4 (Medium): Random Term Re-Rolled Every Run

random = Math.random() / 10 is regenerated on every execution for every project, contributing up to 0.1 / 3.1 ≈ 0.032 of score.

With 117 projects packed into the 0.35–0.50 band, local density is ~780 projects per unit of score, so the 0.032 swing spans roughly 25 rank positions of pure noise, re-rolled each run. That is a deliberate exploration mechanism, but at this magnitude it is competing with the quality and responsiveness signals rather than gently perturbing them.

Recommendation: if the intent is exploration, seed it per project per day (hash(project_id + utcDate)) so ranks are stable within a day while still rotating over time. If the intent is tie-breaking, cut the magnitude to ~0.01 and keep it random.

Issue 5 (Medium): Zero Impressions Zeroes the Views

const cappedViews = Math.min(todayViews, todayImpressions * ctrCeiling);

When todayImpressions = 0, cappedViews = 0 regardless of actual views. The project then gets pacingRatio = 0 → pacing ≈ 0.982, and smoothedCtr = AVG_CTR → quality = 1.0. A project with real traffic but unlogged impressions is scored as a fresh, perfectly average project — a free ride at the top of the pacing curve.

Frequency is low but non-zero: across 2026-07-20 → 2026-07-30, 3–7 project-days per day had impressions = 0 with views > 0 (out of ~145). Impression coverage for bookable projects is otherwise good — the platform-wide “impressions barely tracked” pattern is confined to non-bookable projects this worker never scores.

Recommendation: only apply the CTR ceiling when impressions are present: const cappedViews = todayImpressions > 0 ? Math.min(todayViews, todayImpressions * ctrCeiling) : todayViews;

Issue 6 (Low, Latent): Responsiveness Lacks the Smoothing Quality Gets

Quality is smoothed toward the platform mean via CTR_SMOOTHING_K, which correctly stops low-impression projects from posting extreme CTRs. Responsiveness receives no equivalent treatment:

A project with 1 lead answered in 30 minutes scores ~0.99.
A project with no leads in the window is absent from the SQL result and defaults to 0 via || 0 — the worst possible value, not a neutral one.

So a brand-new project is smoothed to neutral on quality but penalised to worst on responsiveness, costing up to 1/3.1 = 32% of the maximum score.

Currently low-impact: all 147 projects have leads in a 30-day window (median 72), and only 8 have ≤10. It becomes a real problem as bookable inventory grows or if the window shortens.

Recommendation: apply the same Bayesian treatment as CTR — (responded + k·platform_rate) / (leads + k) — which converges to the platform mean for low-volume projects and to the true rate for high-volume ones.

Issue 7 (Low, Needs Code-Side Check): Empty-String Icon

project.icon != null ? config.DEFAULT_DAILY_VIEWS_CAP_PREMIUM : config.DEFAULT_DAILY_VIEWS_CAP_STANDARD

In JavaScript '' != null is true, so a project whose icon is an empty string — rather than NULL — receives the premium cap. This cannot be settled from the warehouse: the mirrored icon column is a non-nullable String, so PeerDB has already flattened any NULL to '', and 93 of 147 projects show ''.

Check against MySQL directly: SELECT COUNT(*) FROM real_estate_projects WHERE icon = '' AND bookable = 1; If that is non-zero, switch the test to a truthiness check (project.icon ? ... : ...).

Verified Correct

Worth recording, since several of these looked like plausible defects before checking:

Day-boundary formula. parseInt(Date.now() / 86400000) matches the stats writer and the observed UTC rollover of sadb_stats_projects_stats. The numerator is right; only the denominator’s hour is wrong.
searcher_info_requested_at is per-lead. The project interest finding established that the WhatsApp qualification flow fires once per user per 3-month window with the response denormalized across that user’s leads — which would badly distort a per-lead response rate. It does not apply here: among users with multiple leads in 30 days, the per-lead response rate is higher than for single-lead users (0.369 vs 0.241) and 653 multi-lead users had every lead responded. The column is set per lead.
Response times are clean. Zero leads have searcher_info_requested_at < createdAt; median response is 41 minutes, p90 is 24.1 hours, and only 4.9% exceed the 48-hour cap — so MAX_RESPONSE_HOURS = 48 truncates a genuinely small tail.
View capping works as intended. todayImpressions × AVG_CTR × 2 bounds campaign-driven view spikes, and bookable-project CTR runs ~16.7% platform-wide, so the ceiling is not routinely binding.

One caveat on the NULL question: because the mirrored searcher_info_requested_at is a non-nullable DateTime64, MySQL NULL and a zero-date are indistinguishable in the warehouse (22,089 of 29,503 30-day leads sit at the epoch). The worker’s IS NOT NULL test is almost certainly correct, but if that column can ever hold '0000-00-00', those rows would count as responded with a nonsensical negative duration. One query against MySQL settles it.

Recommendations

Priority	Change	Effort
1	Index the pacing allowance by UTC hour (issue 1) — stops the daily 3-hour ranking inversion	One line
2	Replace linear allowance with `HOURLY_VIEW_CUM` (issue 2)	One line, same change
3	Lower the CRM floor to the non-webhook median (~0.31), or measure it (issue 3)	Small
4	Guard `cappedViews` when impressions are 0 (issue 5)	One line
5	Seed `random` per project per day (issue 4)	Small
6	Smooth responsiveness like CTR (issue 6)	Small
7	Confirm the `icon` NULL-vs-`''` question in MySQL (issue 7)	One query
8	Add a monitor: alert if >20% of projects write `project_score = 0` in a run	Small

Recommendation 8 is worth taking seriously regardless of the others — issue 1 has presumably been running in production unnoticed because nothing watches for mass-zero scores, and the affected window sits in the middle of the night for the team but at peak traffic for users.

SQL Queries

Scored population and factor snapshot

SELECT
  count() AS bookable_published,
  countIf (icon != '') AS has_icon,
  countIf (owner_webhook_url != '') AS has_webhook,
  countIf (daily_views_cap > 0) AS has_explicit_cap,
  round(quantile (0.5) (project_score), 6) AS median_score,
  round(quantile (0.5) (pacing), 6) AS median_pacing,
  round(quantile (0.5) (quality), 4) AS median_quality,
  round(quantile (0.5) (responsiveness_bonus), 4) AS median_resp,
  round(stddevPop (project_score), 4) AS sd_score,
  countIf (project_score BETWEEN 0.35 AND 0.5) AS projects_in_mid_band
FROM
  sadb_real_estate_projects FINAL
WHERE
  _peerdb_is_deleted = 0
  AND bookable = true
  AND is_bookable_project_published = true

Pacing simulation (reproduces the issue 1 and 2 tables)

WITH
  [
    0.03309,
    0.06252,
    0.09216,
    0.11406,
    0.13539,
    0.15926,
    0.18634,
    0.21671,
    0.25046,
    0.28944,
    0.33680,
    0.38641,
    0.43463,
    0.48407,
    0.53555,
    0.58584,
    0.63990,
    0.69181,
    0.74498,
    0.79958,
    0.85332,
    0.90590,
    0.95772,
    1.00000
  ] AS C,
  4.0 AS beta -- PACING_BETA, derived from the code comment
SELECT
  u AS hour_utc,
  (u + 3) % 24 AS hour_ksa,
  round(C[u + 1], 4) AS demand_elapsed,
  round(((u + 3) % 24 + 1) / 24, 4) AS code_allowance,
  round(
    1 / (
      1 + exp(
        beta * ((1.00 * C[u + 1]) / (((u + 3) % 24 + 1) / 24) - 1)
      )
    ),
    6
  ) AS pacing_at_cap_current,
  round(
    1 / (
      1 + exp(
        beta * ((0.25 * C[u + 1]) / (((u + 3) % 24 + 1) / 24) - 1)
      )
    ),
    6
  ) AS pacing_at_quarter_cap_current,
  round(
    1 / (1 + exp(beta * ((1.00 * C[u + 1]) / ((u + 1) / 24) - 1))),
    4
  ) AS pacing_phase_fixed_linear,
  round(1 / (1 + exp(beta * (1.00 - 1))), 4) AS pacing_demand_aware
FROM
  (
    SELECT
      arrayJoin (range(24)) AS u
  )
ORDER BY
  u

Responsiveness by webhook group (replicates RESPONSIVENESS_SQL)

WITH
  bp AS (
    SELECT
      id,
      if (owner_webhook_url != '', 1, 0) AS has_webhook
    FROM
      sadb_real_estate_projects FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND bookable = true
      AND is_bookable_project_published = true
  ),
  r AS (
    SELECT
      project_id,
      countIf (toYear (searcher_info_requested_at) > 1970) / count() AS response_rate,
      avgIf (
        dateDiff ('minute', createdAt, searcher_info_requested_at) / 60,
        toYear (searcher_info_requested_at) > 1970
      ) AS avg_response_hours
    FROM
      sadb_real_estate_project_interested_users FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND createdAt >= now() - INTERVAL 30 DAY
    GROUP BY
      project_id
  )
SELECT
  bp.has_webhook AS has_webhook,
  count() AS projects,
  round(avg(computed), 4) AS avg_computed_responsiveness,
  round(quantile (0.5) (computed), 4) AS median_computed,
  countIf (computed < 0.6) AS below_floor_0_6
FROM
  bp
  LEFT JOIN (
    SELECT
      project_id,
      least (
        greatest (
          response_rate * (
            (
              48 - least (
                if (isNaN (avg_response_hours), 48, avg_response_hours),
                48
              )
            ) / 48
          ),
          0
        ),
        1
      ) AS computed
    FROM
      r
  ) rr ON bp.id = rr.project_id
GROUP BY
  has_webhook
ORDER BY
  has_webhook

Response-time health check

SELECT
  count() AS responded_leads,
  countIf (searcher_info_requested_at < createdAt) AS negative_response_time,
  round(
    quantile (0.5) (
      dateDiff ('minute', createdAt, searcher_info_requested_at) / 60
    ),
    3
  ) AS median_hours,
  round(
    quantile (0.9) (
      dateDiff ('minute', createdAt, searcher_info_requested_at) / 60
    ),
    3
  ) AS p90_hours,
  countIf (
    dateDiff ('minute', createdAt, searcher_info_requested_at) / 60 >= 48
  ) AS beyond_48h
FROM
  sadb_real_estate_project_interested_users FINAL
WHERE
  _peerdb_is_deleted = 0
  AND createdAt >= now() - INTERVAL 30 DAY
  AND toYear (searcher_info_requested_at) > 1970

Impression-coverage check (issue 5)

WITH
  bp AS (
    SELECT
      id
    FROM
      sadb_real_estate_projects FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND bookable = true
      AND is_bookable_project_published = true
  )
SELECT
  day_date,
  count() AS project_rows,
  countIf (impressions = 0 AND views > 0) AS zero_impr_with_views,
  sum(views) AS tot_views,
  sum(impressions) AS tot_impr
FROM
  sadb_stats_projects_stats FINAL
WHERE
  _peerdb_is_deleted = 0
  AND day_date >= '2026-07-20'
  AND project_id IN (
    SELECT
      id
    FROM
      bp
  )
GROUP BY
  day_date
ORDER BY
  day_date