Database Schema Audit Report

This document audits every database table and field against actual code usage to identify what's truly needed vs unused/legacy code.

1. checkins - Main check-in records
2. address_cache - Cached address data
3. profile_cache - User profile data
4. user_follows - Following relationships
5. anchor_users - User tracking for crawling
6. processing_log - Processing statistics
7. oauth_sessions - Authentication sessions
8. display_names - Display name tracking

9. checkins_v1 - Legacy version
10. address_cache_v1 - Legacy version
11. user_pdses - PDS tracking
12. migrations - Migration tracking

Status: ✅ ACTIVE - Core table used extensively by API

• id (TEXT, PRIMARY KEY) - ✅ USED - Required for record identification
• uri (TEXT, UNIQUE) - ✅ USED - AT Protocol record URI, used in API responses
• rkey (TEXT) - ✅ USED - Record key, used for queries
• did (TEXT) - ✅ USED - Author DID, used for user filtering and queries
• handle (TEXT) - ✅ USED - Author handle, displayed in API responses
• display_name (TEXT) - ✅ USED - Author display name, used in API responses
• avatar (TEXT) - ✅ USED - Author avatar URL, used in API responses
• text (TEXT) - ✅ USED - Check-in message, core content
• created_at (TEXT) - ✅ USED - Timestamp for sorting and cursors
• latitude (REAL) - ✅ USED - Location coordinate, used for distance calculations
• longitude (REAL) - ✅ USED - Location coordinate, used for distance calculations
• venue_name (TEXT) - ✅ USED - UNIFIED FIELD - Venue name displayed in API
• category (TEXT) - 🤔 STORED - Place category, not currently used in API responses
• category_group (TEXT) - 🤔 STORED - Category group, not currently used in API responses
• category_icon (TEXT) - 🤔 STORED - Category icon, not currently used in API responses
• address_street (TEXT) - ✅ USED - Street address, used in API responses
• address_locality (TEXT) - ✅ USED - City, used in API responses
• address_region (TEXT) - ✅ USED - State/province, used in API responses
• address_country (TEXT) - ✅ USED - Country, used in API responses
• address_postal_code (TEXT) - ✅ USED - Postal code, used in API responses
• indexed_at (TEXT) - ✅ USED - Metadata timestamp

Migration Impact: Migration 005 removed these unused fields:

• address_ref_uri - ❌ REMOVED - StrongRef URI, not used in API
• address_ref_cid - ❌ REMOVED - StrongRef CID, not used in API
• address_full - ❌ REMOVED - JSON field causing parsing issues
• address_resolved_at - ❌ REMOVED - Resolution timestamp, not used
• place_name - ❌ REMOVED - Migrated to unified venue_name
• cached_address_name - ❌ REMOVED - Migrated to unified venue_name

Status: ❓ UNCLEAR - Table exists but usage uncertain

• uri (TEXT, PRIMARY KEY) - ❓ UNKNOWN - Address record URI
• cid (TEXT) - ❓ UNKNOWN - Content hash
• name (TEXT) - ❓ UNKNOWN - Venue name
• street (TEXT) - ❓ UNKNOWN - Street address
• locality (TEXT) - ❓ UNKNOWN - City
• region (TEXT) - ❓ UNKNOWN - State/province
• country (TEXT) - ❓ UNKNOWN - Country
• postal_code (TEXT) - ❓ UNKNOWN - Postal code
• latitude (REAL) - ❓ UNKNOWN - Location coordinate
• longitude (REAL) - ❓ UNKNOWN - Location coordinate
• resolved_at (TEXT) - ❓ UNKNOWN - Resolution timestamp

Migration Impact: Migration 006 removed:

• full_data - ❌ REMOVED - JSON field causing parsing issues
• failed_at - ❌ REMOVED - Failure timestamp, not used

Status: ✅ ACTIVE - Used by ProfileResolver in API

• did (TEXT, PRIMARY KEY) - ✅ USED - User DID, key for profile lookups
• handle (TEXT) - ✅ USED - User handle, displayed in API
• display_name (TEXT) - ✅ USED - Display name, shown in API responses
• avatar_url (TEXT) - ✅ USED - Avatar URL, used in API responses
• description (TEXT) - 🤔 STORED - Profile description, not used in API responses
• followers_count (INTEGER) - 🤔 STORED - Follower count, not used in API responses
• following_count (INTEGER) - 🤔 STORED - Following count, not used in API responses
• posts_count (INTEGER) - 🤔 STORED - Post count, not used in API responses
• indexed_at (TEXT) - ✅ USED - Cache timestamp
• updated_at (TEXT) - ✅ USED - Update timestamp

Status: ✅ ACTIVE - Used for following feed functionality

• follower_did (TEXT, PRIMARY KEY) - ✅ USED - Follower DID, used in following queries
• following_did (TEXT, PRIMARY KEY) - ✅ USED - Following DID, used in following queries
• created_at (TEXT) - 🤔 STORED - Relationship creation time, not used in API
• synced_at (TEXT) - ✅ USED - Last sync timestamp

Status: ❓ UNCLEAR - Defined in schema but usage uncertain

• did (TEXT, PRIMARY KEY) - ❓ UNKNOWN - User DID
• handle (TEXT) - ❓ UNKNOWN - User handle
• pds (TEXT) - ❓ UNKNOWN - PDS URL
• last_checkin_crawl (TEXT) - ❓ UNKNOWN - Last crawl timestamp
• last_follower_crawl (TEXT) - ❓ UNKNOWN - Last follower crawl
• added_at (TEXT) - ❓ UNKNOWN - Added timestamp
• updated_at (TEXT) - ❓ UNKNOWN - Update timestamp

Status: ✅ ACTIVE - Used in stats API endpoint

• id (INTEGER, PRIMARY KEY) - ✅ USED - Record ID
• run_at (TEXT) - ✅ USED - Processing run timestamp
• events_processed (INTEGER) - ✅ USED - Event count
• errors (INTEGER) - ✅ USED - Error count
• duration_ms (INTEGER) - ✅ USED - Processing duration

Status: ❓ UNCLEAR - Table exists but usage uncertain in current API

• did (TEXT, PRIMARY KEY) - ❓ UNKNOWN - User DID
• handle (TEXT) - ❓ UNKNOWN - User handle
• pds_url (TEXT) - ❓ UNKNOWN - PDS URL
• access_token (TEXT) - ❓ UNKNOWN - OAuth access token
• refresh_token (TEXT) - ❓ UNKNOWN - OAuth refresh token
• dpop_private_key (TEXT) - ❓ UNKNOWN - DPoP private key
• dpop_public_key (TEXT) - ❓ UNKNOWN - DPoP public key
• session_id (TEXT) - ❓ UNKNOWN - Session cookie ID
• created_at (INTEGER) - ❓ UNKNOWN - Creation timestamp
• updated_at (INTEGER) - ❓ UNKNOWN - Update timestamp

Status: ❓ UNCLEAR - Table exists but usage uncertain

• did (TEXT, PRIMARY KEY) - ❓ UNKNOWN - User DID
• display_name (TEXT) - ❓ UNKNOWN - Display name
• updated_at (TEXT) - ❓ UNKNOWN - Update timestamp

• checkins_v1 - ❌ LEGACY - Old version, still referenced in legacy code but main API uses checkins
• address_cache_v1 - ❌ LEGACY - Old version, still used by address resolver but should migrate

• user_pdses - ✅ ACTIVE - CRITICAL PDS CRAWLER TABLE - Tracks PDS reference counting and crawling coordination

• checkins: 16/21 fields actively used
• profile_cache: 6/10 fields actively used
• user_follows: 3/4 fields actively used
• processing_log: All 5 fields used

• checkins: category, category_group, category_icon (not in API responses)
• profile_cache: description, followers_count, following_count, posts_count
• user_follows: created_at

• address_cache: Entire table usage unclear
• anchor_users: Entire table usage unclear
• oauth_sessions: Entire table usage unclear
• display_names: Entire table usage unclear

• checkins_v1
• address_cache_v1

• user_pdses - ✅ CRITICAL PDS crawler infrastructure

MAJOR FIELD NAME INCONSISTENCIES DISCOVERED - This likely explains the venue name issues!

1. Address fields in checkins table:

   • API code (anchor-api.ts) uses: addressStreet, addressLocality, addressRegion, etc.
   • Backend code (queries.ts) uses: cached_address_street, cached_address_locality, etc.
   • Schema defines: address_street, address_locality, etc.

2. Author fields:

   • API code uses: did, handle
   • Legacy code uses: author_did, author_handle

✅ CONFIRMED ACTIVE:

• checkins (new API format)
• checkins_v1 (legacy - universal-storage.ts, address-resolver.ts)
• profile_cache (storage-provider.ts, queries.ts)
• address_cache_v1 (universal-storage.ts, address-resolver.ts)
• user_follows (followers-processor.ts, API)
• anchor_users (user-tracking.ts, followers-processor.ts)
• user_pdses (user-tracking.ts - PDS crawler)
• oauth_sessions (oauth/session.ts, oauth/dpop.ts, oauth/endpoints.ts)
• processing_log (API stats)

❌ NOT FOUND IN ACTIVE CODE:

• address_cache (only in schema/migrations)
• display_names (only in schema/migrations)

1. ✅ Fixed field name inconsistencies - All code now uses consistent venueName field
2. ✅ Unified API queries - All endpoints use same Drizzle queries with consistent field names
3. ✅ Removed duplicate endpoints - Eliminated /api/feed, consolidated to /api/global
4. ✅ Cleaned up unused functions - Removed getRecentCheckins() after endpoint consolidation
5. ✅ Fixed TypeScript errors - Resolved Drizzle query builder issues preventing deployment
6. ✅ Verified venue name resolution - Field name inconsistencies that caused venue name issues are resolved

• Database Schema: Fully migrated to Drizzle with type safety
• Field Names: All unified (venueName, addressStreet, etc.)
• API Endpoints: Single set serves both web and mobile clients
• Query Consistency: All feeds use same Drizzle queries and field names
• Deployment: Successfully deployed and tested at dropanchor.app

7. ✅ Removed unused tables - Successfully dropped address_cache and display_names tables
   • Migration 007_remove_unused_tables executed successfully
   • Verified removal via admin endpoint /api/admin/check-unused-tables

1. Migrate legacy tables - Update remaining code to use current schemas instead of v1 tables
   • checkins_v1: Still used by universal-storage.ts and address-resolver.ts
   • address_cache_v1: Still used by address resolver system
2. Continue migration system - Current Drizzle migration approach is working well

All critical field name inconsistencies have been resolved. The venue name issues that prompted this audit are now fixed.