extremely claude-assisted go game based on atproto! working on cleaning up and giving a more unique design, still has a bit of a slop vibe to it.
1/**
2 * Backfill games from UFOS (ATProto record index) into D1 database.
3 * Also checks Constellation for moves to determine player_two and status.
4 *
5 * Usage:
6 * # Generate SQL file
7 * npx tsx scripts/backfill-games.ts > scripts/backfill-games.sql
8 *
9 * # Run against D1
10 * npx wrangler d1 execute atprotogo-db --remote --file=scripts/backfill-games.sql
11 */
12
13const UFOS_API = 'https://ufos-api.microcosm.blue';
14const CONSTELLATION_API = 'https://constellation.microcosm.blue/xrpc';
15
16interface UfosRecord {
17 did: string;
18 collection: string;
19 rkey: string;
20 record: {
21 $type: string;
22 boardSize: number;
23 createdAt: string;
24 playerOne?: string;
25 playerTwo?: string;
26 status: string;
27 handicap?: number;
28 winner?: string;
29 };
30 time_us: number;
31}
32
33interface ConstellationBacklink {
34 did: string;
35 collection: string;
36 rkey: string;
37}
38
39interface ConstellationResponse {
40 records: ConstellationBacklink[];
41 total: number;
42 cursor: string | null;
43}
44
45async function fetchAllGames(): Promise<UfosRecord[]> {
46 const response = await fetch(`${UFOS_API}/records?collection=boo.sky.go.game`);
47 if (!response.ok) {
48 throw new Error(`UFOS API error: ${response.status}`);
49 }
50 return response.json();
51}
52
53async function fetchGameMoves(gameUri: string): Promise<ConstellationBacklink[]> {
54 const allMoves: ConstellationBacklink[] = [];
55 let cursor: string | undefined;
56
57 try {
58 do {
59 const params = new URLSearchParams({
60 subject: gameUri,
61 source: 'boo.sky.go.move:game',
62 limit: '100',
63 });
64 if (cursor) params.set('cursor', cursor);
65
66 const res = await fetch(
67 `${CONSTELLATION_API}/blue.microcosm.links.getBacklinks?${params}`,
68 { headers: { Accept: 'application/json' } }
69 );
70
71 if (!res.ok) break;
72
73 const body: ConstellationResponse = await res.json();
74 allMoves.push(...body.records);
75 cursor = body.cursor ?? undefined;
76 } while (cursor);
77 } catch (err) {
78 console.error(`Failed to fetch moves for ${gameUri}:`, err);
79 }
80
81 return allMoves;
82}
83
84async function fetchGamePasses(gameUri: string): Promise<ConstellationBacklink[]> {
85 const allPasses: ConstellationBacklink[] = [];
86 let cursor: string | undefined;
87
88 try {
89 do {
90 const params = new URLSearchParams({
91 subject: gameUri,
92 source: 'boo.sky.go.pass:game',
93 limit: '100',
94 });
95 if (cursor) params.set('cursor', cursor);
96
97 const res = await fetch(
98 `${CONSTELLATION_API}/blue.microcosm.links.getBacklinks?${params}`,
99 { headers: { Accept: 'application/json' } }
100 );
101
102 if (!res.ok) break;
103
104 const body: ConstellationResponse = await res.json();
105 allPasses.push(...body.records);
106 cursor = body.cursor ?? undefined;
107 } while (cursor);
108 } catch (err) {
109 console.error(`Failed to fetch passes for ${gameUri}:`, err);
110 }
111
112 return allPasses;
113}
114
115function escapeSQL(str: string | null | undefined): string {
116 if (!str) return '';
117 return str.replace(/'/g, "''");
118}
119
120async function main() {
121 console.error('Fetching games from UFOS...');
122 const games = await fetchAllGames();
123 console.error(`Found ${games.length} games`);
124 console.error('');
125
126 // Output SQL INSERT statements
127 console.log('-- Backfill games from UFOS with Constellation move data');
128 console.log('-- Generated at:', new Date().toISOString());
129 console.log(`-- Total games: ${games.length}`);
130 console.log('');
131
132 for (const game of games) {
133 const uri = `at://${game.did}/boo.sky.go.game/${game.rkey}`;
134 const now = new Date().toISOString();
135 const record = game.record;
136
137 // The creator is the DID that owns the record
138 const creatorDid = game.did;
139 // playerOne might be different from creator in some cases
140 let playerOne = record.playerOne || creatorDid;
141 let playerTwo = record.playerTwo || null;
142 let status = record.status;
143 let actionCount = 0;
144 let lastActionType: string | null = null;
145
146 console.error(`Processing ${game.rkey}...`);
147
148 // Fetch moves and passes from Constellation
149 const [moves, passes] = await Promise.all([
150 fetchGameMoves(uri),
151 fetchGamePasses(uri),
152 ]);
153
154 actionCount = moves.length + passes.length;
155
156 if (moves.length > 0 || passes.length > 0) {
157 // Find all unique DIDs that made moves/passes
158 const playerDids = new Set<string>();
159
160 for (const move of moves) {
161 playerDids.add(move.did);
162 }
163
164 for (const pass of passes) {
165 playerDids.add(pass.did);
166 }
167
168 // If we have moves but no playerTwo in the record, try to determine it
169 if (!playerTwo && playerDids.size > 0) {
170 // playerTwo is any DID that's not playerOne
171 for (const did of playerDids) {
172 if (did !== playerOne) {
173 playerTwo = did;
174 break;
175 }
176 }
177 }
178
179 // Update status to active if there are moves and status is waiting
180 if (status === 'waiting' && (moves.length > 0 || passes.length > 0)) {
181 status = 'active';
182 }
183
184 // Determine last action type (we don't have timestamps from backlinks, so just use 'move' or 'pass')
185 if (passes.length > 0) {
186 lastActionType = 'pass';
187 } else if (moves.length > 0) {
188 lastActionType = 'move';
189 }
190 }
191
192 console.error(` -> ${moves.length} moves, ${passes.length} passes, status: ${status}, playerTwo: ${playerTwo || 'none'}`);
193
194 const sql = `INSERT OR REPLACE INTO games (id, rkey, creator_did, player_one, player_two, board_size, status, action_count, last_action_type, winner, handicap, created_at, updated_at) VALUES (
195 '${escapeSQL(uri)}',
196 '${escapeSQL(game.rkey)}',
197 '${escapeSQL(creatorDid)}',
198 '${escapeSQL(playerOne)}',
199 ${playerTwo ? `'${escapeSQL(playerTwo)}'` : 'NULL'},
200 ${record.boardSize},
201 '${escapeSQL(status)}',
202 ${actionCount},
203 ${lastActionType ? `'${escapeSQL(lastActionType)}'` : 'NULL'},
204 ${record.winner ? `'${escapeSQL(record.winner)}'` : 'NULL'},
205 ${record.handicap || 0},
206 '${escapeSQL(record.createdAt)}',
207 '${escapeSQL(now)}'
208);`;
209 console.log(sql);
210 console.log('');
211 }
212
213 console.error('');
214 console.error('Done! Run with:');
215 console.error(' npx wrangler d1 execute atprotogo-db --remote --file=scripts/backfill-games.sql');
216}
217
218main().catch(console.error);