xiaoshu-app/scripts/sql/payroll-read-timeout-recovery.sql

-- Incident 2026-10-15: keep JIT compilation out of interactive payroll reads.
-- Scope is these functions only; restore the backed-up definitions/view to roll back.
BEGIN;
SET LOCAL lock_timeout='5s';
SELECT pg_advisory_xact_lock(hashtext('xs-payroll-read-recovery'));
ALTER FUNCTION xs_payroll_sources(text) SET jit=off;
ALTER FUNCTION xs_payroll_source_version(text) SET jit=off;
ALTER FUNCTION xs_payroll_cutoff_snapshot(text,text) SET jit=off;
-- Preserve every column and correction rule while allowing tenant/month predicates
-- to reach the indexed receipt table before projecting corrected amounts.
CREATE OR REPLACE VIEW "ReviewRewardIncome" AS
SELECT
  r."objectId",
  r."company",
  r."taskId",
  r."taskKey",
  r."coachId",
  r."coachName",
  r."studentId",
  r."studentName",
  r."courseName",
  r."storeId",
  r."storeName",
  r."plannedDate",
  r."completedAt",
  r."incomeMonth",(r.amount+COALESCE(c.amount,0))::numeric(12,2) AS amount,
  CASE WHEN c."objectId" IS NULL THEN r.status ELSE 'ineligible' END AS status,
  r."exception",
  r."createdAt",
  CASE WHEN c."objectId" IS NULL THEN r."ineligibleReason" ELSE 'corrected_overdue' END AS "ineligibleReason",
  r.amount AS "originalAmount",
  c."objectId" AS "correctionId",
  c.reason AS "correctionReason",
  c.amount AS "correctionAmount",
  c."adjustmentBatchId"
FROM "ReviewReward" r LEFT JOIN "ReviewRewardCorrection" c ON c.company=r.company AND c."rewardId"=r."objectId";

COMMIT;