Refactor models/providers storage from Zustand to SQL-based ProviderDb

[sh1ftred]

Problem

The chat app currently stores models and provider data using an inefficient Zustand-based system that serializes everything to localStorage as large JSON blobs. Specifically:

Current Issues

1. Inefficient Storage Pattern (sdk/storage/store.ts):

   • modelsFromAllProviders stored as Record<string, Model[]> JSON blob
   • infoFromAllProviders stored as Record<string, ProviderInfo> JSON blob
   • mintsFromAllProviders stored as Record<string, string[]> JSON blob
   • Entire blob must be loaded/serialized for any update

2. No Query Optimization:

   • Can't efficiently filter models by provider
   • Can't efficiently search by model ID
   • No pagination support
   • Every update requires full blob serialization

3. Data Integrity Issues:

   • No transactional guarantees
   • Corrupted storage can break entire system
   • No schema validation

4. Duplication of Logic:

   • sdk/storage/store.ts has Zustand-like store for SDK
   • utils/storageUtils.ts has separate localStorage helpers for UI
   • useApiState.ts manages state separately from SDK store

Proposed Solution

Create a new ProviderDb system following the same architecture as @sdk/storage/usageTracking/ which successfully uses SQLite/IndexedDB drivers.

Reference Implementation

The UsageTrackingDriver provides an excellent pattern to follow.

Interface pattern (usageTracking/interfaces.ts):

export interface UsageTrackingDriver {
  migrate(): Promise<void>;
  append(entry: UsageTrackingEntry): Promise<void>;
  appendMany(entries: UsageTrackingEntry[]): Promise<void>;
  list(options?: ListUsageTrackingOptions): Promise<UsageTrackingEntry[]>;
  count(options?: Omit<ListUsageTrackingOptions, "limit">): Promise<number>;
  deleteOlderThan(timestamp: number): Promise<number>;
  clear(): Promise<void>;
}

Implementation Steps

1. Create Driver Interface and Types (sdk/storage/providerDb/interfaces.ts)

export interface Provider {
  baseUrl: string;  // Primary key
  name?: string;
  lastUpdate: number;
  isDisabled: boolean;
}

export interface ProviderModel {
  baseUrl: string;  // FK to Provider
  modelId: string;
  model: Model;  // Full model object
  lastUpdate: number;
}

export interface ProviderDbDriver {
  migrate(): Promise<void>;
  
  // Provider operations
  getProviders(): Promise<Provider[]>;
  getProvider(baseUrl: string): Promise<Provider | null>;
  upsertProvider(provider: Provider): Promise<void>;
  setProviderDisabled(baseUrl: string, disabled: boolean): Promise<void>;
  deleteProvider(baseUrl: string): Promise<void>;
  
  // Model operations
  getModelsForProvider(baseUrl: string): Promise<Model[]>;
  getModelById(modelId: string): Promise<ProviderModel | null>;
  upsertModels(providerBaseUrl: string, models: Model[]): Promise<void>;
  deleteModelsForProvider(baseUrl: string): Promise<void>;
  
  // Query operations
  getAllModels(): Promise<ProviderModel[]>;
  getModelsByIds(modelIds: string[]): Promise<ProviderModel[]>;
  getDisabledProviders(): Promise<string[]>;
  
  // Cleanup
  clear(): Promise<void>;
}

2. Implement SQLite Driver (sdk/storage/providerDb/sqlite.ts)

Following the usageTracking/sqlite.ts pattern:

• Create proper schema with indexes
• Migration from legacy storage
• Batch insert for models

Schema:

CREATE TABLE providers (
  base_url TEXT PRIMARY KEY,
  name TEXT,
  last_update INTEGER NOT NULL,
  is_disabled INTEGER DEFAULT 0
);

CREATE TABLE models (
  id TEXT PRIMARY KEY,
  base_url TEXT NOT NULL,
  model_json TEXT NOT NULL,
  last_update INTEGER NOT NULL,
  FOREIGN KEY (base_url) REFERENCES providers(base_url) ON DELETE CASCADE
);

CREATE INDEX idx_models_base_url ON models(base_url);
CREATE INDEX idx_models_last_update ON models(last_update);

3. Implement IndexedDB Driver (sdk/storage/providerDb/indexedDB.ts)

Following the usageTracking/indexedDB.ts pattern:

• Object stores for providers and models
• Indexes on base_url, model_id, last_update
• Migration from legacy storage

4. Create Memory Driver (sdk/storage/providerDb/memory.ts)

For testing and SSR scenarios.

5. Create Bun SQLite Driver (sdk/storage/providerDb/bunSqlite.ts)

Bun-specific implementation using bun:sqlite.

6. Create Migration Strategy

1. Phase 1: New driver coexists with old storage
2. Phase 2: One-time migration on first access
3. Phase 3: Remove legacy storage (after verification)

Migration code:

private async migrateFromLegacy(): Promise<void> {
  const migrated = await this.legacyStorageDriver.getItem<boolean>(
    'provider_db_migration_v1',
    false
  );
  if (migrated) return;

  const models = await this.legacyStorageDriver.getItem<Record<string, Model[]>>(
    SDK_STORAGE_KEYS.MODELS_FROM_ALL_PROVIDERS,
    {}
  );
  const info = await this.legacyStorageDriver.getItem<Record<string, ProviderInfo>>(
    SDK_STORAGE_KEYS.INFO_FROM_ALL_PROVIDERS,
    {}
  );

  for (const [baseUrl, providerModels] of Object.entries(models)) {
    await this.upsertProvider({
      baseUrl,
      name: info[baseUrl]?.name,
      lastUpdate: Date.now(),
      isDisabled: false,
    });
    await this.upsertModels(baseUrl, providerModels);
  }

  await this.legacyStorageDriver.setItem('provider_db_migration_v1', true);
}

7. Update SDK Store Integration (sdk/storage/store.ts)

• Remove modelsFromAllProviders, infoFromAllProviders, mintsFromAllProviders from SdkStorageState
• Add providerDb: ProviderDbDriver to store options
• Create createProviderDbFromStore() adapter
• Deprecate old methods with warnings

8. Update UI Components

The useApiState.ts hook and ModelSelector.tsx component will need updates:

• Replace localStorage helpers with providerDb driver
• Use async methods instead of synchronous state
• Handle loading states properly

Files to Create/Modify

New Files:

• sdk/storage/providerDb/interfaces.ts - Driver interface and types
• sdk/storage/providerDb/sqlite.ts - SQLite implementation
• sdk/storage/providerDb/indexedDB.ts - IndexedDB implementation
• sdk/storage/providerDb/memory.ts - Memory implementation
• sdk/storage/providerDb/bunSqlite.ts - Bun-specific SQLite
• sdk/storage/providerDb/index.ts - Public exports

Modified Files:

• sdk/storage/store.ts - Integrate providerDb, deprecate old fields
• sdk/storage/index.ts - Export new module
• hooks/useApiState.ts - Use providerDb instead of localStorage
• components/chat/ModelSelector.tsx - Update to async model loading
• utils/storageUtils.ts - Remove duplicated logic (or deprecate)
• utils/modelUtils.ts - Update to use providerDb

Acceptance Criteria

1. ProviderDbDriver interface is defined and well-documented
2. SQLite implementation works in Node.js environments
3. IndexedDB implementation works in browser environments
4. Migration from legacy storage completes without data loss
5. All existing functionality is preserved (model selection, provider switching, etc.)
6. Performance is improved (selective queries vs full blob loading)
7. SDK store integration is clean and backward-compatible
8. Tests cover new functionality
9. Documentation explains migration path for existing data

Priority

High - This is a core infrastructure improvement that affects model/provider management throughout the app.