• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

Freegle / Iznik / 21438

15 Jun 2026 08:00AM UTC coverage: 70.75% (-0.5%) from 71.2%
21438

push

circleci

edwh
docs(spatial): complete spatial-server docs + plain-English volunteer overview

Documentation for the spatial subsystem merged in #459:

- iznik-spatial-go/README.md (NEW): the KNN "finder" service had no README at
  all. Documents all six datasets, every public + admin endpoint, env vars,
  the nightly/15-min sync scheduler, SQLite index persistence, Docker wiring,
  callers, and the /swagger OpenAPI generation.
- iznik-routing-go/README.md: completeness pass — adds the five live endpoints
  that were undocumented (/v1/ripple-schedule, POST /v1/ripple-eval,
  /v1/posts-for-member, /v1/digest-simulator, /swagger), the
  ROUTING_DRIVE_SPEED_FACTOR env var, the JWT-on-external-port note, and a
  cross-link to the spatial service.
- SPATIAL-SERVERS.md (NEW, repo root): a non-technical, plain-English overview
  for volunteers/moderators/staff — what the two services do ("finder" +
  "travel-time mapper"), why they exist, fairness, and the rippling-out idea —
  with no code, linking down to the technical READMEs.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

10952 of 14552 branches covered (75.26%)

Branch coverage included in aggregate %.

118160 of 167938 relevant lines covered (70.36%)

35.47 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

92.82
/iznik-batch/app/Services/MessageSpatialService.php
1
<?php
2

3
namespace App\Services;
4

5
use App\Models\Message;
6
use App\Models\MessageGroup;
7
use Illuminate\Support\Facades\DB;
8
use Illuminate\Support\Facades\Log;
9

10
class MessageSpatialService
11
{
12
    // V1: MessageCollection::RECENTPOSTS = "Midnight 31 days ago"
13
    private const RECENT_DAYS = 31;
14
    private const SRID = 3857;
15

16
    private SpatialAdminService $spatialAdmin;
17

18
    public function __construct(SpatialAdminService $spatialAdmin)
11✔
19
    {
20
        $this->spatialAdmin = $spatialAdmin;
11✔
21
    }
22

23
    public function updateSpatialIndex(bool $dryRun = false): array
7✔
24
    {
25
        $stats = [
7✔
26
            'upserted_recent' => $this->upsertRecentMessages($dryRun),
7✔
27
            'outcomes_updated' => $this->updateOutcomesAndPromises($dryRun),
7✔
28
            'removed_deleted' => $this->removeDeletedMessages($dryRun),
7✔
29
            'removed_old' => $this->removeOldMessages($dryRun),
7✔
30
            'removed_non_approved' => $this->removeNonApprovedMessages($dryRun),
7✔
31
        ];
7✔
32

33
        $total = array_sum($stats);
7✔
34
        $stats['total'] = $total;
7✔
35

36
        Log::info("MessageSpatialIndex: " . ($dryRun ? 'would update ' : 'updated ') . "{$total} entries", $stats);
7✔
37

38
        return $stats;
7✔
39
    }
40

41
    private function upsertRecentMessages(bool $dryRun = false): int
7✔
42
    {
43
        $cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));
7✔
44

45
        $msgs = DB::table('messages')
7✔
46
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
7✔
47
            ->join('users', 'users.id', '=', 'messages.fromuser')
7✔
48
            ->leftJoin('messages_spatial', 'messages_spatial.msgid', '=', 'messages_groups.msgid')
7✔
49
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
7✔
50
            ->where('messages_groups.arrival', '>=', $cutoff)
7✔
51
            ->whereNotNull('messages.lat')
7✔
52
            ->whereNotNull('messages.lng')
7✔
53
            ->whereNull('messages.deleted')
7✔
54
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
7✔
55
            ->whereNull('users.deleted')
7✔
56
            ->where(function ($q) {
7✔
57
                // Include messages with no outcome, or Taken/Received (same as V1: outcome IS NULL OR outcome IN ('Taken','Received'))
58
                $q->whereNull('messages_outcomes.outcome')
7✔
59
                    ->orWhereIn('messages_outcomes.outcome', [Message::OUTCOME_TAKEN, Message::OUTCOME_RECEIVED]);
7✔
60
            })
7✔
61
            ->where(function ($q) {
7✔
62
                $q->whereNull('messages_spatial.msgid')
7✔
63
                    ->orWhereRaw('ST_X(messages_spatial.point) != messages.lng')
7✔
64
                    ->orWhereRaw('ST_Y(messages_spatial.point) != messages.lat')
7✔
65
                    ->orWhereNull('messages_spatial.groupid')
7✔
66
                    ->orWhereRaw('messages_spatial.groupid != messages_groups.groupid')
7✔
67
                    ->orWhereRaw('messages_groups.arrival != messages_spatial.arrival');
7✔
68
            })
7✔
69
            ->select(
7✔
70
                'messages.id',
7✔
71
                'messages.lat',
7✔
72
                'messages.lng',
7✔
73
                'messages_groups.groupid',
7✔
74
                'messages_groups.arrival',
7✔
75
                'messages_groups.msgtype',
7✔
76
            )
7✔
77
            ->distinct()
7✔
78
            ->get();
7✔
79

80
        $count = 0;
7✔
81
        foreach ($msgs as $msg) {
7✔
82
            if (!$dryRun) {
7✔
83
                // Coordinates come from DB, not user input — safe to embed in WKT.
84
                $wkt = "POINT({$msg->lng} {$msg->lat})";
7✔
85
                $srid = self::SRID;
7✔
86

87
                DB::statement(
7✔
88
                    "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
7✔
89
                     VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
7✔
90
                     ON DUPLICATE KEY UPDATE
91
                       point = ST_GeomFromText('$wkt', $srid),
7✔
92
                       groupid = ?,
93
                       msgtype = ?,
94
                       arrival = ?",
7✔
95
                    [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
7✔
96
                     $msg->groupid, $msg->msgtype, $msg->arrival]
7✔
97
                );
7✔
98
            }
99
            $count++;
7✔
100
        }
101

102
        return $count;
7✔
103
    }
104

105
    private function updateOutcomesAndPromises(bool $dryRun = false): int
7✔
106
    {
107
        $msgs = DB::table('messages_spatial')
7✔
108
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages_spatial.msgid')
7✔
109
            ->leftJoin('messages_promises', 'messages_promises.msgid', '=', 'messages_spatial.msgid')
7✔
110
            ->select(
7✔
111
                'messages_spatial.id',
7✔
112
                'messages_spatial.msgid',
7✔
113
                'messages_spatial.successful',
7✔
114
                'messages_spatial.promised',
7✔
115
                'messages_outcomes.outcome',
7✔
116
                'messages_promises.promisedat',
7✔
117
            )
7✔
118
            ->orderByDesc('messages_outcomes.timestamp')
7✔
119
            ->get();
7✔
120

121
        $count = 0;
7✔
122
        $deletedMsgids = [];
7✔
123
        foreach ($msgs as $msg) {
7✔
124
            if ($msg->outcome === Message::OUTCOME_WITHDRAWN || $msg->outcome === Message::OUTCOME_EXPIRED) {
7✔
125
                if (!$dryRun) {
2✔
126
                    DB::table('messages_spatial')->where('id', $msg->id)->delete();
2✔
127
                    $deletedMsgids[] = $msg->msgid;
2✔
128
                }
129
                $count++;
2✔
130
            } elseif ($msg->outcome === Message::OUTCOME_TAKEN || $msg->outcome === Message::OUTCOME_RECEIVED) {
7✔
131
                if (!$msg->successful) {
7✔
132
                    if (!$dryRun) {
7✔
133
                        DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 1]);
7✔
134
                    }
135
                    $count++;
7✔
136
                }
137
            } elseif ($msg->successful) {
7✔
138
                if (!$dryRun) {
×
139
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 0]);
×
140
                }
141
                $count++;
×
142
            }
143

144
            if ($msg->promised && !$msg->promisedat) {
7✔
145
                if (!$dryRun) {
×
146
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 0]);
×
147
                }
148
                $count++;
×
149
            } elseif (!$msg->promised && $msg->promisedat) {
7✔
150
                if (!$dryRun) {
×
151
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 1]);
×
152
                }
153
                $count++;
×
154
            }
155
        }
156

157
        if (!empty($deletedMsgids)) {
7✔
158
            $this->spatialAdmin->removeItems('messages', $deletedMsgids);
2✔
159
        }
160

161
        return $count;
7✔
162
    }
163

164
    private function removeDeletedMessages(bool $dryRun = false): int
7✔
165
    {
166
        $rows = DB::table('messages_spatial')
7✔
167
            ->join('messages', 'messages_spatial.msgid', '=', 'messages.id')
7✔
168
            ->leftJoin('users', 'users.id', '=', 'messages.fromuser')
7✔
169
            ->where(function ($q) {
7✔
170
                $q->whereNull('messages.fromuser')
7✔
171
                    ->orWhereNotNull('messages.deleted')
7✔
172
                    ->orWhereNotNull('users.deleted');
7✔
173
            })
7✔
174
            ->select('messages_spatial.id', 'messages_spatial.msgid')
7✔
175
            ->get();
7✔
176

177
        if ($rows->isEmpty()) {
7✔
178
            return 0;
5✔
179
        }
180

181
        if (!$dryRun) {
2✔
182
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
2✔
183
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
2✔
184
        }
185

186
        return $rows->count();
2✔
187
    }
188

189
    private function removeOldMessages(bool $dryRun = false): int
7✔
190
    {
191
        $cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));
7✔
192

193
        $rows = DB::table('messages_spatial')
7✔
194
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')
7✔
195
            ->where('messages_groups.arrival', '<', $cutoff)
7✔
196
            ->select('messages_spatial.id', 'messages_spatial.msgid')
7✔
197
            ->get();
7✔
198

199
        if ($rows->isEmpty()) {
7✔
200
            return 0;
6✔
201
        }
202

203
        if (!$dryRun) {
1✔
204
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
1✔
205
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
1✔
206
        }
207

208
        return $rows->count();
1✔
209
    }
210

211
    private function removeNonApprovedMessages(bool $dryRun = false): int
7✔
212
    {
213
        $rows = DB::table('messages_spatial')
7✔
214
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')
7✔
215
            ->where('messages_groups.collection', '!=', MessageGroup::COLLECTION_APPROVED)
7✔
216
            ->select('messages_spatial.id', 'messages_spatial.msgid')
7✔
217
            ->get();
7✔
218

219
        if ($rows->isEmpty()) {
7✔
220
            return 0;
7✔
221
        }
222

223
        if (!$dryRun) {
×
224
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
×
225
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
×
226
        }
227

228
        return $rows->count();
×
229
    }
230

231
    /**
232
     * Add a single just-approved message to the spatial index immediately, so it
233
     * appears in browse/search without waiting for the every-5-minute reconciler.
234
     *
235
     * No-op unless the message is Approved, has a location, and has no outcome —
236
     * messages_spatial backs the public browse/map, so Pending/Spam/Rejected
237
     * messages must never be added here. Safe to call inside the same transaction
238
     * that set the collection to Approved (it reads its own uncommitted write).
239
     */
240
    public function addApprovedMessage(int $msgid): void
4✔
241
    {
242
        $msg = DB::table('messages')
4✔
243
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
4✔
244
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
4✔
245
            ->where('messages.id', $msgid)
4✔
246
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
4✔
247
            ->where('messages_groups.deleted', 0)
4✔
248
            ->whereNull('messages.deleted')
4✔
249
            ->whereNotNull('messages.lat')
4✔
250
            ->whereNotNull('messages.lng')
4✔
251
            ->whereNull('messages_outcomes.id')
4✔
252
            ->orderByDesc('messages_groups.arrival')
4✔
253
            ->select(
4✔
254
                'messages.id',
4✔
255
                'messages.lat',
4✔
256
                'messages.lng',
4✔
257
                DB::raw('messages.type as msgtype'),
4✔
258
                'messages_groups.groupid',
4✔
259
                'messages_groups.arrival',
4✔
260
            )
4✔
261
            ->first();
4✔
262

263
        if (!$msg) {
4✔
264
            return;
3✔
265
        }
266

267
        // Coordinates come from the DB, not user input — safe to embed in WKT.
268
        $wkt  = "POINT({$msg->lng} {$msg->lat})";
1✔
269
        $srid = self::SRID;
1✔
270

271
        DB::statement(
1✔
272
            "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
1✔
273
             VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
1✔
274
             ON DUPLICATE KEY UPDATE
275
               point = ST_GeomFromText('$wkt', $srid),
1✔
276
               groupid = ?,
277
               msgtype = ?,
278
               arrival = ?",
1✔
279
            [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
1✔
280
             $msg->groupid, $msg->msgtype, $msg->arrival]
1✔
281
        );
1✔
282
    }
283
}
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc