Task S6T03: Batched km Distance Backfill Function¶

Sprint Context¶

• Sprint index: Sprint 6 Task Index
• Tags: database, database-refactor, sprint-6, task-index

Source¶

• DBA source: task-S6T03.dba.md
• Specification source: task-S6T03.specification.md

Resolved Decision¶

• S6T03 backfills only geokrety.gk_moves.km_distance.
• The canonical migration name is 20260310600200_create_backfill_km_distance.php.
• Any lower text referencing stats.gk_km_counter or stats.user_km_counter as S6T03 targets is obsolete.
• Historical aggregate tables are rebuilt elsewhere from canonical move history; they are not direct outputs of this task.

Purpose & Scope¶

Creates stats.fn_backfill_km_distance(p_period TSTZRANGE DEFAULT tstzrange('-infinity', 'infinity'), p_batch_size INT DEFAULT 50000) to populate historical geokrety.gk_moves.km_distance values from canonical predecessor links.

This task owns only row-level distance backfill on geokrety.gk_moves.

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310600200_create_backfill_km_distance.php

Full SQL DDL¶

CREATE OR REPLACE FUNCTION stats.fn_backfill_km_distance(
  p_period     TSTZRANGE DEFAULT tstzrange('-infinity', 'infinity'),
  p_batch_size INT       DEFAULT 50000
)
RETURNS BIGINT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_started_at TIMESTAMPTZ := clock_timestamp();
  v_rows_updated BIGINT := 0;
BEGIN
  -- Canonical implementation:
  -- 1. Batch qualifying moves inside the requested period.
  -- 2. Use previous_move_id-derived source and canonical move coordinates.
  -- 3. Set km_distance only for qualifying rows with a valid predecessor.

  INSERT INTO stats.job_log (job_name, status, metadata, started_at, completed_at)
  VALUES (
    'fn_backfill_km_distance',
    'ok',
    jsonb_build_object(
      'period', p_period,
      'batch_size', p_batch_size,
      'rows_updated', v_rows_updated
    ),
    v_started_at,
    clock_timestamp()
  );

  RETURN v_rows_updated;
END;
$$;

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateBackfillKmDistance extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE OR REPLACE FUNCTION stats.fn_backfill_km_distance(
  p_period     TSTZRANGE DEFAULT tstzrange('-infinity', 'infinity'),
  p_batch_size INT       DEFAULT 50000
)
RETURNS BIGINT LANGUAGE plpgsql SECURITY DEFINER
AS $$
BEGIN
  RETURN 0;
END;
$$;
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<'SQL'
DROP FUNCTION IF EXISTS stats.fn_backfill_km_distance(TSTZRANGE, INT);
SQL
        );
    }
}

The placeholder body above must be replaced by the canonical km_distance recomputation logic from REQ-721 through REQ-726.

Canonical Notes¶

• S6T03 does not seed stats.gk_km_counter or stats.user_km_counter.
• Any validation here must target geokrety.gk_moves.km_distance directly.
• Full-history orchestration belongs to S6T04.

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Create 20260310600200_create_backfill_km_distance.php
• 2. phinx migrate — no errors
• 3. Verify \df stats.fn_backfill_km_distance
• 4. Validate sample km_distance recomputation on known moves
• 5. Verify no off-spec counter-table dependency remains
• 6. Run pgTAP T-6.3.001 through T-6.3.007
• 7. phinx rollback — function dropped

Agent Loop Log¶

• 2026-03-10T21:05:00Z — dba: removed stale km-counter-table ownership from S6T03 and restored the correct migration file.
• 2026-03-10T21:05:00Z — critical-thinking: separated row-level distance recomputation from aggregate-table rebuild responsibilities.
• 2026-03-10T21:05:00Z — specification: canonized S6T03 as a geokrety.gk_moves.km_distance backfill task only.

Resolution¶

Q-037 is resolved by canonizing S6T03 as the km_distance backfill task only.