SIBA
Back to blogs
•William Tran

Database Architecture

Real-time data layer architecture for SIBA Portfolio using Electric SQL sync.

Complete Data Flow

---
config:
  flowchart:
    defaultRenderer: "elk"
---
flowchart TB
subgraph Client ["Browser (Client)"]

subgraph Components ["📦 React Components"]

RC["🖥️ Route Components<br/>(SSR + Hydration)"]

AC["✏️ Admin Editor<br/>(Server Functions)"]

LC["⚡ Live Components<br/>(Real-time)"]

end

subgraph DataLayer ["📊 Client Data Layer"]

LD["useLoaderData()<br/>Static SSR Data"]

LQ["useLiveQuery()<br/>Real-time Data"]

end

subgraph Collections ["🗃️ TanStack DB Collections"]

PC["posts<br/>Collection"]

BC["backlinks<br/>Collection"]

end

end

subgraph Server ["🖥️ Server"]

subgraph TanStackStart ["⚡ TanStack Start"]

RL["📥 Route Loaders<br/>(SSR)"]

SF["🔧 Server Functions<br/>(createServerFn)"]

end

subgraph Electric ["⚡ Electric SQL"]

ES["🔄 Shape Server<br/>(Port 3002)"]

end

subgraph ORM ["🔷 Drizzle ORM"]

DQ["queries.ts<br/>(30+ functions)"]

DS["schema.ts<br/>(8 tables)"]

end

end

subgraph Database ["🗄️ PostgreSQL"]

PG[("📀 Database<br/>posts, backlinks, etc.")]

REP["📡 Logical<br/>Replication"]

end

%% SSR Flow

RC -->|"1️⃣ Initial Request"| RL

RL -->|"2️⃣ Data Fetch"| SF

SF -->|"3️⃣ Query"| DQ

DQ -->|"4️⃣ SQL"| PG

PG -->|"5️⃣ Response"| RL

RL -->|"6️⃣ SSR HTML"| LD

LD -->|"7️⃣ Hydrate"| RC

%% Electric Sync Flow

PG -->|"🔄 WAL Changes"| REP

REP -->|"🔄 Stream"| ES

ES -->|"🔄 Shape Sync"| PC

ES -->|"🔄 Shape Sync"| BC

PC --> LQ(fa:fa-spinner)

BC --> LQ

LQ -->|"⚡ Live Data"| LC

%% Write Flow

AC -->|"✍️ Save"| SF

SF -->|"✍️ Update"| DQ

DQ -->|"✍️ SQL"| PG(fa:fa-spinner)


class RC,AC,LC,LD,LQ,PC,BC client

class RL,SF,DQ,DS server

class PG,REP db

class ES electric

Read Flow (Page Load → Real-time Updates)

sequenceDiagram
    autonumber
    participant Browser
    participant TanStackRouter as TanStack Router
    participant ServerFn as Server Function
    participant Drizzle
    participant PostgreSQL
    participant Electric as Electric SQL
    participant TanStackDB as TanStack DB

    rect rgb(240, 255, 240)
    Note over Browser,PostgreSQL: 🚀 Initial Page Load (SSR)
    Browser->>TanStackRouter: Request /en/blogs/my-post
    TanStackRouter->>ServerFn: loader → getPostBySlug()
    ServerFn->>Drizzle: db.query.posts.findFirst()
    Drizzle->>PostgreSQL: SELECT * FROM posts WHERE...
    PostgreSQL-->>Drizzle: Post data
    Drizzle-->>ServerFn: Post object
    ServerFn-->>TanStackRouter: { post }
    TanStackRouter-->>Browser: SSR HTML + JSON
    end

    rect rgb(255, 240, 240)
    Note over Browser,TanStackDB: ⚡ Client Hydration + Electric Sync
    Browser->>Browser: setIsClient(true)
    Browser->>TanStackDB: useLiveQuery() mounts
    TanStackDB->>Electric: Subscribe to posts shape
    Electric->>PostgreSQL: Initial sync (via replication)
    PostgreSQL-->>Electric: All posts data
    Electric-->>TanStackDB: Populate collection
    TanStackDB-->>Browser: Live data ready
    end

    rect rgb(240, 240, 255)
    Note over Browser,TanStackDB: 🔄 Real-time Updates
    PostgreSQL->>Electric: WAL: Post updated
    Electric-->>TanStackDB: Shape update
    TanStackDB-->>Browser: Re-render with new data
    end

Write Flow (Admin Edit → All Clients Update)


sequenceDiagram

autonumber

participant Admin as Admin Browser

participant ServerFn as Server Function

participant Drizzle

participant PostgreSQL

participant Electric as Electric SQL

participant Client as Other Browsers

Note over Admin,Client: ✏️ Admin Saves Content

Admin->>Admin: Click Save

Admin->>ServerFn: updatePostContent({ postId, content })

ServerFn->>Drizzle: db.update(posts).set(...)

Drizzle->>PostgreSQL: UPDATE posts SET raw_content = ...

PostgreSQL-->>Drizzle: Success

Drizzle-->>ServerFn: { success: true }

ServerFn-->>Admin: Toast: "Saved!"

Note over PostgreSQL,Client: ⚡ Electric Propagates Change

PostgreSQL->>Electric: WAL: posts row updated

Electric->>Client: Shape update (WebSocket/HTTP)

Client->>Client: useLiveQuery re-renders

Note over Admin,Client: All browsers now show updated content

Component Patterns

SSR + Live Query Pattern

// Route component - handles both SSR and real-time
function BlogPage() {
  const loaderData = Route.useLoaderData(); // SSR data
  const [isClient, setIsClient] = useState(false);

  useEffect(() => setIsClient(true), []);

  return isClient ? (
    <LiveBlogContent fallback={loaderData.post} /> // Real-time
  ) : (
    <StaticBlogContent post={loaderData.post} /> // SSR
  );
}

// Client-only component with live query
function LiveBlogContent({ fallback }) {
  const { data: livePost } = usePostBySlug(slug, type, locale);
  const post = livePost || fallback; // Fallback prevents flicker
  return <BlogArticle post={post} />;
}

Admin Pattern (Server Functions Only)

// Admin doesn't use live queries (SSR issues)
// Uses server functions + manual refresh
function EditorPageClient({ initialData }) {
  const [data, setData] = useState(initialData);

  const refreshData = async () => {
    const fresh = await getAllPostsForEditor();
    setData(fresh);
  };

  const handleSave = async (content) => {
    await updatePostContent({ data: { postId, content } });
    await refreshData(); // Manual refresh after save
  };
}

Layer Responsibilities

Layer Technology Responsibility
UI Components React 19 Render UI, handle user interactions
Route Loaders TanStack Router SSR data fetching, caching
Live Queries TanStack DB Real-time reactive data
Collections TanStack DB + Electric Client-side data store with sync
Server Functions TanStack Start Type-safe RPC to server
ORM Drizzle SQL query building, migrations
Sync Engine Electric SQL PostgreSQL → Client replication
Database PostgreSQL Persistent storage, source of truth

Key Files

File Purpose
lib/db/schema.ts Drizzle table definitions (8 tables)
lib/db/queries.ts Server functions (30+)
lib/tanstack-db/collections.ts Electric-synced collections
lib/tanstack-db/hooks.ts useLiveQuery wrappers
src/router.tsx TanStack Router + QueryClient
docker-compose.electric.yml Electric SQL server config

Running Electric SQL

Sync Latency

Operation Typical Latency
SSR page load ~100-300ms
Electric initial sync ~500-1000ms
Real-time update propagation ~50-200ms
Server function RPC ~50-150ms

Limitations

  1. No SSR for live queries: useLiveQuery only works on client
  2. Admin uses server functions: To avoid SSR issues with hooks
  3. No offline support yet: Phase 3 planned feature
  4. Single-source joins: Electric doesn't support multi-table joins in shapes