databaseSync.ts•7.25 kB
import { logger } from "../utils/logger.js";
import type { ThoughtRecord } from "./structuredThinking.js";
import { SQLitePlannerService } from "./sqlitePlanner.js";
export interface SyncStatus {
readonly lastSyncAt: string;
readonly pendingLocal: number;
readonly pendingRemote: number;
readonly conflictsDetected: number;
readonly syncState: "idle" | "syncing" | "error";
readonly lastError?: string;
}
export interface SyncConfig {
readonly postgresConnectionString?: string;
readonly syncIntervalMs: number;
readonly batchSize: number;
readonly conflictResolution: "last-write-wins" | "manual" | "merge";
readonly enableAutoSync: boolean;
}
export interface ConflictRecord {
readonly thoughtId: string;
readonly localVersion: ThoughtRecord;
readonly remoteVersion: ThoughtRecord;
readonly detectedAt: string;
}
/**
* DatabaseSyncService manages synchronization between local SQLite cache
* and production PostgreSQL database.
*
* **Architecture**:
* - SQLite: Fast local cache, offline resilience, command queue
* - PostgreSQL: Source of truth, distributed coordination, audit trail
*
* **Sync Strategy**:
* - Background worker pushes local changes every N seconds
* - Pulls remote changes on startup and periodically
* - Conflict resolution via configurable strategies
* - Offline queue persists commands when network unavailable
*/
export class DatabaseSyncService {
private readonly config: SyncConfig;
private readonly localPlanner: SQLitePlannerService;
private syncIntervalTimer: NodeJS.Timeout | null = null;
private currentlySyncing = false;
private status: SyncStatus = {
lastSyncAt: new Date().toISOString(),
pendingLocal: 0,
pendingRemote: 0,
conflictsDetected: 0,
syncState: "idle",
};
public constructor(
localPlanner: SQLitePlannerService,
config: Partial<SyncConfig> = {},
) {
this.localPlanner = localPlanner;
this.config = {
syncIntervalMs: config.syncIntervalMs ?? 60000, // Default 1 minute
batchSize: config.batchSize ?? 50,
conflictResolution: config.conflictResolution ?? "last-write-wins",
enableAutoSync: config.enableAutoSync ?? true,
postgresConnectionString: config.postgresConnectionString,
};
if (this.config.enableAutoSync && this.config.postgresConnectionString) {
this.startAutoSync();
}
}
/**
* Start background sync worker
*/
public startAutoSync(): void {
if (this.syncIntervalTimer) {
logger.warn("Auto-sync already running");
return;
}
if (!this.config.postgresConnectionString) {
logger.warn("Cannot start auto-sync: PostgreSQL connection string not configured");
return;
}
logger.info("Starting auto-sync", {
intervalMs: this.config.syncIntervalMs,
batchSize: this.config.batchSize,
});
this.syncIntervalTimer = setInterval(() => {
void this.syncNow();
}, this.config.syncIntervalMs);
}
/**
* Stop background sync worker
*/
public stopAutoSync(): void {
if (this.syncIntervalTimer) {
clearInterval(this.syncIntervalTimer);
this.syncIntervalTimer = null;
logger.info("Auto-sync stopped");
}
}
/**
* Manually trigger immediate sync
*/
public async syncNow(): Promise<SyncStatus> {
if (this.currentlySyncing) {
logger.debug("Sync already in progress, skipping");
return this.status;
}
if (!this.config.postgresConnectionString) {
logger.warn("Cannot sync: PostgreSQL not configured");
return this.status;
}
this.currentlySyncing = true;
this.status = { ...this.status, syncState: "syncing" };
try {
logger.debug("Starting sync cycle");
// Phase 1: Push local changes to PostgreSQL
await this.pushLocalChanges();
// Phase 2: Pull remote changes from PostgreSQL
await this.pullRemoteChanges();
// Phase 3: Resolve conflicts
await this.resolveConflicts();
this.status = {
lastSyncAt: new Date().toISOString(),
pendingLocal: 0,
pendingRemote: 0,
conflictsDetected: this.status.conflictsDetected,
syncState: "idle",
};
logger.info("Sync completed successfully", this.status);
} catch (error) {
const errorMessage = error instanceof Error ? error.message : String(error);
this.status = {
...this.status,
syncState: "error",
lastError: errorMessage,
};
logger.error("Sync failed", { error: errorMessage });
} finally {
this.currentlySyncing = false;
}
return this.status;
}
/**
* Get current sync status
*/
public getStatus(): SyncStatus {
return { ...this.status };
}
/**
* Check if PostgreSQL is configured and reachable
*/
public async checkPostgresHealth(): Promise<boolean> {
if (!this.config.postgresConnectionString) {
return false;
}
try {
// TODO: Implement actual PostgreSQL health check
// Example: SELECT 1 query with timeout
logger.debug("Checking PostgreSQL health (not yet implemented)");
return false; // Stub: return false until implemented
} catch (error) {
logger.error("PostgreSQL health check failed", { error });
return false;
}
}
/**
* Push local SQLite changes to PostgreSQL
*/
private async pushLocalChanges(): Promise<void> {
// TODO: Implement push logic
// 1. Get local thoughts modified since last sync
// 2. Batch upload to PostgreSQL structured_thoughts table
// 3. Mark as synced in local metadata
logger.debug("Push local changes (not yet implemented)");
}
/**
* Pull remote PostgreSQL changes to local SQLite
*/
private async pullRemoteChanges(): Promise<void> {
// TODO: Implement pull logic
// 1. Query PostgreSQL for thoughts modified since last sync
// 2. Download in batches
// 3. Insert into local SQLite
// 4. Update sync metadata
logger.debug("Pull remote changes (not yet implemented)");
}
/**
* Resolve conflicts between local and remote versions
*/
private async resolveConflicts(): Promise<void> {
// TODO: Implement conflict resolution
// Strategy options:
// - last-write-wins: Take newest timestamp
// - manual: Store conflicts for user review
// - merge: Combine metadata, prefer newer content
logger.debug("Resolve conflicts (not yet implemented)", {
strategy: this.config.conflictResolution,
});
}
/**
* Get list of unresolved conflicts
*/
public async getConflicts(): Promise<ConflictRecord[]> {
// TODO: Query local conflicts table
logger.debug("Get conflicts (not yet implemented)");
return [];
}
/**
* Manually resolve a conflict
*/
public async resolveConflict(
thoughtId: string,
resolution: "keep-local" | "keep-remote" | "merge",
): Promise<void> {
logger.debug("Manual conflict resolution (not yet implemented)", {
thoughtId,
resolution,
});
// TODO: Apply resolution and remove from conflicts table
}
/**
* Cleanup: stop sync worker
*/
public destroy(): void {
this.stopAutoSync();
logger.info("DatabaseSyncService destroyed");
}
}