Fix N+1 query in turret solar-system sync

[rustydb]

Priority: P2

Source

Senior Architect Code Review (2026-04-03)

Problem

PostgresTurretSolarSystemRepository.sync() in apps/api/src/repositories.ts executes one INSERT ... SELECT query per turret inside a for loop — a classic N+1 query pattern. With many turrets, this causes:

• Linear growth in database round-trips
• Increased latency for the sync operation
• Unnecessary connection pool pressure

async sync(turrets: TurretNodeRelation[]): Promise<number> {
    let updated = 0;
    for (const turret of turrets) {
        // One query per turret
        const result = await this.pool.query(
            `INSERT INTO turret_solar_system_mappings ...`,
            [turret.turretId, turret.nodeId],
        );
        updated += result.rowCount ?? 0;
    }
    return updated;
}

Affected Files

• apps/api/src/repositories.ts (PostgresTurretSolarSystemRepository.sync)

Recommendation

Batch the operation into a single multi-row INSERT using unnest or a VALUES list:

INSERT INTO turret_solar_system_mappings (turret_id, solar_system_id, solar_system_name, source_node_id)
SELECT t.turret_id, n.solar_system_id, n.solar_system_name, t.node_id
FROM unnest($1::text[], $2::text[]) AS t(turret_id, node_id)
JOIN network_node_mappings n ON n.node_id = t.node_id
ON CONFLICT (turret_id) DO UPDATE SET ...

This reduces N queries to 1 regardless of turret count.

Acceptance Criteria

• Sync operation executes a single query regardless of turret count
• Existing unit tests continue to pass
• Integration test verifies batch behavior with multiple turrets
• In-memory repository behavior remains consistent with Postgres implementation