Log inSign up
tijs
tijslocation-feed-generator
This is the Anchor AppView - location based feed generator
Public
Like
1
location-feed-generator
Code
21
Val Town is a collaborative website to build and scale JavaScript apps.
Deploy APIs, crons, & store data – all from the browser, and deployed in milliseconds.
Sign up now
Code
/
DATABASE_AUDIT.md
Search
8/11/2025
Viewing readonly version of main branch: v596
View latest version
DATABASE_AUDIT.md

Database Schema Audit Report

Overview

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

Tables Found in Codebase

Core Tables (from schema.ts)

  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

Legacy/Version Tables

  1. checkins_v1 - Legacy version
  2. address_cache_v1 - Legacy version
  3. user_pdses - PDS tracking
  4. migrations - Migration tracking

Detailed Field Analysis

Table 1: checkins - Main check-in records

Status: ✅ ACTIVE - Core table used extensively by API

Fields Analysis:

  • 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

Table 2: address_cache - Cached address data

Status: ❓ UNCLEAR - Table exists but usage uncertain

Fields Analysis:

  • 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

Table 3: profile_cache - User profile data

Status: ✅ ACTIVE - Used by ProfileResolver in API

Fields Analysis:

  • 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

Table 4: user_follows - Following relationships

Status: ✅ ACTIVE - Used for following feed functionality

Fields Analysis:

  • 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

Table 5: anchor_users - User tracking for crawling

Status: ❓ UNCLEAR - Defined in schema but usage uncertain

Fields Analysis:

  • 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

Table 6: processing_log - Processing statistics

Status: ✅ ACTIVE - Used in stats API endpoint

Fields Analysis:

  • 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

Table 7: oauth_sessions - Authentication sessions

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

Fields Analysis:

  • 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

Table 8: display_names - Display name tracking

Status: ❓ UNCLEAR - Table exists but usage uncertain

Fields Analysis:

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

Legacy Tables (Should be removed)

  • 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

Active System Tables (Keep)

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

Summary of Findings

Currently Used Fields (API Active)

  • 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

Questionable Fields (Stored but unused)

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

Unclear Tables (Need investigation)

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

Legacy Tables to Remove

  • checkins_v1
  • address_cache_v1

Active System Tables (KEEP - Not Legacy)

  • user_pdses - ✅ CRITICAL PDS crawler infrastructure

⚠️ CRITICAL FINDINGS FROM REVERSE CODE AUDIT

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

Field Name Mismatches Found:

  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

ACTUAL Tables Found in Backend Code:

✅ 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)

✅ ACTIONS COMPLETED:

  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

Current Status (Updated August 11, 2025):

  • 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

✅ ADDITIONAL ACTIONS COMPLETED:

  1. ✅ 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

Remaining Recommendations

  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

Architecture Status: ✅ STABLE

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