Task S4T09: Social Relation Trigger¶

Sprint Context¶

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

Source¶

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

Resolved Decision¶

• The canonical relation trigger is tr_gk_moves_after_relations on geokrety.gk_moves, firing AFTER INSERT OR UPDATE OR DELETE.
• Canonical relation tables are stats.gk_related_users(geokrety_id, user_id, interaction_count, first_interaction, last_interaction) and stats.user_related_users(user_id, related_user_id, shared_geokrety_count, first_seen_at, last_seen_at).
• Exact reconciliation is required: touched GeoKret rows and affected user-pair rows are recomputed from current qualifying geokrety.gk_moves state instead of applying fragile per-move decrements.

Purpose & Scope¶

Creates the trigger function geokrety.fn_gk_moves_relations() and attaches it as trigger tr_gk_moves_after_relations on geokrety.gk_moves (AFTER INSERT OR UPDATE OR DELETE). This trigger maintains two social-graph tables:

• stats.gk_related_users — which users have touched which GeoKrets
• stats.user_related_users — which user pairs have co-touched the same GeoKret

Qualifying moves: Only move_type IN (0, 1, 3, 5) — DROP, GRAB, SEEN, DIP. COMMENT (2) and ARCHIVE (4) do NOT establish social relations.

Skip conditions:

• NEW.author IS NULL (anonymous): skip entirely — no user to record
• move_type NOT IN (0,1,3,5): skip entirely

Reconciliation flow:

• Recompute stats.gk_related_users exactly for the touched geokrety_id values
• Recompute stats.user_related_users exactly for affected users so shared_geokrety_count remains distinct-shared-GK based

When a qualifying move (move_type IN (0,1,3,5)) is logged by an authenticated user on geokrety.gk_moves, this trigger builds and maintains the social graph between users who co-touched the same GeoKret.

It writes to two tables:

• stats.gk_related_users — (GeoKret, user) pair with touch counts
• stats.user_related_users — (user A, user B) pair when both touched the same GeoKret

Scope: Trigger function + attachment. Requires S4T06 (gk_related_users) and S4T07 (user_related_users).

Requirements¶

Acceptance Criteria¶

Migration File¶

20260310400800_create_relation_trigger.php

Full Phinx Migration PHP Code¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateRelationTrigger extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE OR REPLACE FUNCTION geokrety.fn_gk_moves_relations()
  RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  v_geokrety_ids INT[] := ARRAY[]::INT[];
  v_user_ids INT[] := ARRAY[]::INT[];
BEGIN
  IF TG_OP IN ('DELETE', 'UPDATE')
     AND OLD.author IS NOT NULL
     AND OLD.move_type IN (0,1,3,5) THEN
    v_geokrety_ids := array_append(v_geokrety_ids, OLD.geokret_id);
    v_user_ids := array_append(v_user_ids, OLD.author);
  END IF;

  IF TG_OP IN ('INSERT', 'UPDATE')
     AND NEW.author IS NOT NULL
     AND NEW.move_type IN (0,1,3,5) THEN
    v_geokrety_ids := array_append(v_geokrety_ids, NEW.geokret_id);
    v_user_ids := array_append(v_user_ids, NEW.author);
  END IF;

  IF cardinality(v_geokrety_ids) = 0 THEN
    RETURN COALESCE(NEW, OLD);
  END IF;

  SELECT array_agg(DISTINCT geokrety_id)
  INTO v_geokrety_ids
  FROM unnest(v_geokrety_ids) AS t(geokrety_id);

  SELECT array_agg(DISTINCT user_id)
  INTO v_user_ids
  FROM (
    SELECT unnest(v_user_ids) AS user_id
    UNION
    SELECT m.author
    FROM geokrety.gk_moves m
    WHERE m.author IS NOT NULL
      AND m.move_type IN (0,1,3,5)
      AND m.geokret_id = ANY(v_geokrety_ids)
  ) AS affected_users;

  DELETE FROM stats.gk_related_users
  WHERE geokrety_id = ANY(v_geokrety_ids);

  INSERT INTO stats.gk_related_users (
    geokrety_id,
    user_id,
    interaction_count,
    first_interaction,
    last_interaction
  )
  SELECT
    m.geokret_id,
    m.author,
    COUNT(*)::BIGINT,
    MIN(m.moved_on_datetime),
    MAX(m.moved_on_datetime)
  FROM geokrety.gk_moves m
  WHERE m.author IS NOT NULL
    AND m.move_type IN (0,1,3,5)
    AND m.geokret_id = ANY(v_geokrety_ids)
  GROUP BY m.geokret_id, m.author;

  IF v_user_ids IS NOT NULL AND cardinality(v_user_ids) > 0 THEN
    DELETE FROM stats.user_related_users
    WHERE user_id = ANY(v_user_ids)
       OR related_user_id = ANY(v_user_ids);

    INSERT INTO stats.user_related_users (
      user_id,
      related_user_id,
      shared_geokrety_count,
      first_seen_at,
      last_seen_at
    )
    SELECT
      a.user_id,
      b.user_id AS related_user_id,
      COUNT(DISTINCT a.geokrety_id)::BIGINT,
      MIN(LEAST(a.first_interaction, b.first_interaction)),
      MAX(GREATEST(a.last_interaction, b.last_interaction))
    FROM stats.gk_related_users a
    JOIN stats.gk_related_users b
      ON a.geokrety_id = b.geokrety_id
     AND a.user_id <> b.user_id
    WHERE a.user_id = ANY(v_user_ids)
       OR b.user_id = ANY(v_user_ids)
    GROUP BY a.user_id, b.user_id;
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER tr_gk_moves_after_relations
  AFTER INSERT OR UPDATE OR DELETE ON geokrety.gk_moves
  FOR EACH ROW EXECUTE FUNCTION geokrety.fn_gk_moves_relations();
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<'SQL'
DROP TRIGGER IF EXISTS tr_gk_moves_after_relations ON geokrety.gk_moves;
DROP FUNCTION IF EXISTS geokrety.fn_gk_moves_relations();
SQL
        );
    }
}

SQL Usage Examples¶

-- Verify trigger attached
SELECT tgname FROM pg_trigger
WHERE tgrelid = 'geokrety.gk_moves'::regclass
  AND tgname = 'tr_gk_moves_after_relations';

-- Check social graph for GK 123: who touched it?
SELECT user_id, interaction_count FROM stats.gk_related_users WHERE geokrety_id = 123;

-- Who are user 9876's social connections via shared GKs?
SELECT related_user_id, shared_geokrety_count
FROM stats.user_related_users
WHERE user_id = 9876
ORDER BY shared_geokrety_count DESC LIMIT 20;

-- Verify symmetry: for each (A→B), (B→A) must exist
SELECT COUNT(*) AS asymmetric_pairs
FROM stats.user_related_users a
WHERE NOT EXISTS (
  SELECT 1 FROM stats.user_related_users b
  WHERE b.user_id = a.related_user_id AND b.related_user_id = a.user_id
);
-- Must return 0

pgTAP Unit Tests¶

Implementation Checklist¶

• 1. Write 20260310400800_create_relation_trigger.php with up() + down()
• 2. phinx migrate — no errors
• 3. Trigger tr_gk_moves_after_relations present in \d geokrety.gk_moves
• 4. Function listed in \df geokrety.fn_gk_moves_relations
• 5. Insert moves by two users on same GK; confirm user_related_users symmetry
• 6. Test COMMENT move → no relation rows
• 7. Test anonymous move → no relation rows
• 8. Test interaction_count increment on repeated move by same user
• 9. Symmetry check query returns 0 asymmetric pairs
• 10. UPDATE / DELETE reconciliation rebuilds exact relation rows for touched GeoKrety and affected users
• 11. Run pgTAP T-4.9.001 through T-4.9.011 — all pass

• 12. phinx rollback — trigger and function dropped

• 1. Write 20260310400800_create_relation_trigger.php with up() + down()

• 2. phinx migrate — no errors
• 3. Trigger present; function exists
• 4. Simulate 2-user DROP scenario; verify both directions in user_related_users
• 5. Test COMMENT and ARCHIVE moves → no relation rows
• 6. Test anonymous move → no relation rows
• 7. Symmetry check query returns 0 asymmetric rows
• 8. DELETE scenario tested; row removed at count 0
• 9. Run pgTAP T-4.9.001 through T-4.9.011 — all pass
• 10. phinx rollback — clean removal

Full SQL DDL — Trigger Function¶

The canonical function body is the same one embedded in the migration block above under 20260310400800_create_relation_trigger.php. This duplicate lower DDL section was intentionally collapsed to avoid keeping stale merged SQL that used obsolete relation columns and incremental per-move pair logic.

Full SQL DDL — Trigger Attachment¶

CREATE TRIGGER tr_gk_moves_after_relations
  AFTER INSERT OR UPDATE OR DELETE
  ON geokrety.gk_moves
  FOR EACH ROW
  EXECUTE FUNCTION geokrety.fn_gk_moves_relations();

COMMENT ON TRIGGER tr_gk_moves_after_relations ON geokrety.gk_moves
  IS 'Fires on INSERT/UPDATE/DELETE to maintain stats.gk_related_users and stats.user_related_users';

Qualifying Move Types Reference¶

Master-Spec Alignment¶

This task is governed by ../00-SPEC-DRAFT-v1.obsolete.md, Sections 5.6 and 8.4.

• Canonical table names and columns are stats.gk_related_users(geokrety_id, user_id, interaction_count, first_interaction, last_interaction) and stats.user_related_users(user_id, related_user_id, shared_geokrety_count, first_seen_at, last_seen_at).
• tr_gk_moves_after_relations must be documented as AFTER INSERT OR UPDATE OR DELETE with exact OLD removal plus NEW application semantics.
• shared_geokrety_count counts distinct shared GeoKrety for a user pair, not raw move rows.
• Any lower draft text that uses names such as geokret_id, touch_count, or decrements shared counts per move row is obsolete and superseded by this alignment block.

Agent Loop Log¶

• 2026-03-10T18:40:00Z — dba: replaced stale incremental relation logic with exact recomputation for touched GeoKrety and affected user pairs using canonical column names.
• 2026-03-10T18:40:00Z — critical-thinking: distinct shared-GK counts cannot be maintained safely by raw per-move decrement logic, so the spec now rebuilds the affected relation surface exactly.
• 2026-03-10T18:40:00Z — specification: aligned trigger events, requirements, tests, and checklist with Q-028 and the Sprint 4 table contracts from S4T06/S4T07.

Resolution¶

Q-028 is resolved by canonizing exact INSERT / UPDATE / DELETE reconciliation for relation tables in this task.

Objects Created¶

Side-effects (rows modified in stats.*)¶

Edge Cases to Test¶