Gee
  1. Work
  2. Integrations and data platforms

Resumable multi-source sync engine for project analytics

Pluggable connectors pull project and time data from many SaaS tools into one store with checkpointed, resumable runs, locking with stale-lock recovery, deduplication and identity resolution.

Problem

A services company had its project, time and service-work data scattered across many SaaS tools: project management, time tracking, a support desk, accounting and billing. Nobody could see project status or where time was going without manual exports, and simple scripts timed out, overran API limits or double-counted records when they were rerun.

Goal

Hands-off scheduled syncs that load every source into one store correctly even when runs are cut off and rerun, never overrun a source's limits, fit within a serverless platform's request time limit, and feed project status and time-by-category views without anyone exporting anything.

My role

Designed and built the full stack: the data model, the sync framework and every connector, scheduling, CI/CD with staging and production, the API routes and the dashboard UI.

Solution

A pluggable multi-source sync engine: one connector per SaaS source, an orchestrator with atomic per-source locks, checkpointed resumable runs, one deduplicated event store with identity resolution, and time buckets computed in advance feeding project dashboards.

How it works

A managed scheduler triggers the sync runs on a fixed cadence.

The project-management sync runs in phases (projects, tasks, time entries, status updates) and saves a checkpoint to the database after each chunk, so a run cut off by the platform's request time limit resumes exactly where it stopped. It rate-limits itself and backs off when a source pushes back.

An orchestrator runs pluggable connectors, each behind one shared interface. Each takes an atomic database lock for its source before it starts; a lock left behind by a crashed run is recognised as stale and recovered, so a dead instance never blocks the next run and two scaled instances never sync the same source at once.

Connectors cope with several authentication schemes at once: OAuth with rotating refresh tokens, a delegated service account, and a source with its own daily request budget.

Records are normalised into one event table, deduplicated by source plus external identifier, and mapped to one person record through an identity table, so the same colleague carrying a different username in every tool is counted once.

Events are indexed in advance into fixed local-time blocks, so the dashboards aggregate small indexes instead of scanning raw events.

SQL aggregation routes feed a project status board, weekly summaries and time-by-category charts, and an optional LLM step with a typed output schema suggests a category for time entries with no category.

Technical challenges

Long-running syncs against a serverless request time limit: phased runs and database checkpoints.

Preventing overlapping runs across scaled instances: atomic per-source locks with stale-lock recovery.

Exactly-once results from at-least-once runs: deduplication on source plus external identifier.

The same person carrying different identifiers in every tool: an identity table rather than guesswork.

Several authentication schemes at once, including rotating refresh tokens, a delegated service account and a daily request budget.

Dashboards that stay fast as history grows: time blocks computed in advance instead of raw scans.

LLM output that the rest of the system can trust: a typed schema, and suggestions rather than silent writes.

Capabilities

  • Pluggable connectors behind one interface
  • Checkpointed, resumable sync runs
  • Atomic per-source locks with stale-lock recovery
  • Self-imposed rate limiting and backoff
  • Deduplicated unified event store
  • Cross-tool identity resolution
  • Time-block indexing for fast aggregation
  • Project status board, weekly summary and time-by-category charts
  • AI category suggestions with a typed schema
  • Staging and production deploy pipeline

Technology

  • Next.js App Router and TypeScript
  • Prisma with PostgreSQL
  • React charting
  • TanStack Query
  • Tailwind CSS
  • Radix UI
  • Serverless container platform and a managed scheduler
  • Docker multi-stage builds
  • CI/CD with staging and production
  • OAuth 2.0 and service-account authentication
  • LLM framework with typed output schemas

Impact

Scheduled syncs land every source in one store, reruns are safe by design, and a connector framework makes adding a source a contained job, so project status and time allocation by category are available without manual exports. No metrics are claimed.

Skills demonstrated

  • Data engineering and integration
  • Idempotent and resumable batch design
  • Concurrency control
  • API client engineering with rate limiting and backoff
  • OAuth and service-account authentication
  • Relational data modelling
  • Full-stack TypeScript
  • LLM structured output

Demo

Planned

A mock sync console with invented connectors ("TaskBoard", "HelpDesk", "Ledger", "Calendar") and synthetic events for a fictional studio: a run resumes after a simulated time limit, a second run is refused by the lock, a stale lock left by a "crashed" run is recovered, duplicates drop, and two usernames merge into one person record. It ends on project-status and time-by-category charts. No per-person rankings, hours scores or productivity labels anywhere.

Not built yet. A demo here uses only invented names and data.

All work