# Database to ATProtocol Migration Index

This document tracks all changes made during the database optimization effort.

## Overview

**Goal**: Minimize database usage while ensuring ATProtocol is the source of truth.

**Revised Approach** (after testing):
- **Keep database as discovery index** for homepage and game lookups
- **ATProtocol remains source of truth** - all game data written there first
- **Database kept in sync** when ATProtocol records are created/updated
- Database is necessary for efficient discovery of games user is not directly involved in

**Why keep the database?**
- Constellation doesn't have a simple API to list all game records
- Need rkey → creator DID mapping for game lookups
- Efficient filtering by status, time, player without querying multiple PDSs
- Better UX with fast queries vs slow PDS federation

---

## Changes Made

### 1. ATProtocol Client Enhancements
**Files**: `src/lib/atproto-client.ts`
**Commits**: 91f7916, c520d33

**Added Functions**:
- `fetchGameResigns(gameAtUri)` - Fetch resign records from Constellation backlinks
- `calculateGameMetadata(gameUri, gameRecord, creatorDid, rkey)` - Calculate action count, last action type, and updated timestamp dynamically by fetching moves/passes/resigns
- `listPlayerGames(did)` - List all game records from a player's PDS
- `fetchAllGames(knownPlayerDids)` - Aggregate games from multiple players and compute metadata
- `findGame(creatorDid, rkey)` - Fetch a specific game and compute its metadata
- `findGameByRkey(rkey, potentialCreatorDids)` - Search for a game by rkey across multiple PDSs

**New Interface**:
- `GameWithMetadata` - Extends GameRecord with computed fields: `uri`, `rkey`, `creatorDid`, `actionCount`, `lastActionType`, `updatedAt`

**Purpose**: These functions CAN be used to fetch game data directly from ATProtocol without database, but in practice the database is kept for efficient indexing and discovery.

**Status**: ✅ Completed - Functions available for use where needed

---

### 2. Database Strategy Revision
**Files**: `src/routes/+page.server.ts`, `src/routes/game/[id]/+page.server.ts`
**Commit**: c520d33

**Decision**: Keep database as discovery index
- Homepage continues using database for efficient game listing and filtering
- Game page continues using database for rkey lookups
- Profile page will continue using database for player game queries

**Rationale**:
- Constellation lacks comprehensive record listing APIs
- Database provides fast rkey → game data mapping
- Efficient filtering by status, time, player
- Better performance for discovery of games user is not involved in

**Status**: ✅ Completed - Database retained for reads

---

## Future Improvements

### Potential Enhancements:
1. **Write-through cache pattern**: Update ATProtocol first, then sync to database
2. **Database rebuild utility**: Script to rebuild database from ATProtocol records
3. **Eventual consistency**: Background job to sync database with ATProtocol periodically
4. **Minimize database writes**: Only update database on game state changes that affect discovery
5. **Remove database from individual game pages**: Could fetch directly from ATProtocol using functions from #1 above

---

## Current State

**Database Usage**:
- ✅ Homepage - uses database for game discovery
- ✅ Game page - uses database for rkey lookup
- ⏸️ Profile page - still uses database (unchanged)
- ⏸️ API routes - still use database for reads and writes (unchanged)

**ATProtocol Usage**:
- ✅ All game data, moves, passes, resigns written to ATProtocol
- ✅ Helper functions available to fetch directly from ATProtocol
- ✅ Database kept in sync when records are created/updated

---

## Testing Checklist

After migration, test:
- [ ] Homepage loads and displays games correctly
- [ ] Game page works with all game states
- [ ] Profile page shows player's games
- [ ] Creating new games works
- [ ] Joining games works
- [ ] Making moves works
- [ ] Passing works
- [ ] Resigning works
- [ ] Scoring works
- [ ] Reactions work

---

## Rollback Instructions

If something breaks:

1. Revert to commit before migration: `git log --oneline` to find the commit
2. Check this index to see what was changed
3. Each commit is atomic and can be reverted individually

---

## Notes

- The database was only used as a discovery index, not as source of truth
- All game data was already in ATProtocol records
- This migration makes the app more decentralized and resilient