Backend: PostgreSQL

Instructions for generating, building, and deploying the PostgreSQL backend.

Do NOT read SpacetimeDB SDK rule files. This backend uses standard Node.js/TypeScript patterns.

Architecture

• Server: Node.js + Express + Drizzle ORM + Socket.io
• Client: React + Vite + TypeScript + Socket.io-client
• Database: PostgreSQL (running in Docker)

The server handles:

• REST API endpoints for CRUD operations
• Socket.io for real-time events (messages, typing, presence, etc.)
• Drizzle ORM for database queries
• Session/identity management

PostgreSQL Connection

PostgreSQL is already running in a Docker container.

Pre-flight Check

docker exec spacetime-web-postgres-1 psql -U spacetime -d spacetime -c "SELECT 1"

If PostgreSQL is not reachable, STOP and report the error.

Directory Structure

<app-dir>/
  server/
    package.json
    tsconfig.json
    drizzle.config.ts
    .env
    src/
      schema.ts      # Drizzle ORM table definitions
      index.ts       # Express server + Socket.io + routes
  client/
    package.json
    vite.config.ts
    tsconfig.json
    index.html
    src/
      main.tsx       # React entry point
      App.tsx        # Main application component
      styles.css     # Dark theme styling

Phase 1: Generate Server

Create the Express + Socket.io server:

• server/package.json:

  jsonCopy

  {
    "name": "chat-server",
    "type": "module",
    "scripts": {
      "dev": "tsx watch src/index.ts",
      "start": "tsx src/index.ts"
    },
    "dependencies": {
      "express": "^4.18.2",
      "@types/express": "^4.17.21",
      "drizzle-orm": "^0.39.0",
      "pg": "^8.13.0",
      "@types/pg": "^8.11.0",
      "socket.io": "^4.7.4",
      "cors": "^2.8.5",
      "@types/cors": "^2.8.17",
      "dotenv": "^16.4.5",
      "drizzle-kit": "^0.30.0",
      "tsx": "^4.19.0",
      "typescript": "^5.4.0"
    }
  }
  

• server/tsconfig.json:

  jsonCopy

  {
    "compilerOptions": {
      "target": "ES2022",
      "module": "ES2022",
      "moduleResolution": "bundler",
      "esModuleInterop": true,
      "strict": true,
      "outDir": "dist",
      "rootDir": "src",
      "skipLibCheck": true
    },
    "include": ["src/**/*"]
  }
  

• server/.env:

  Copy

  DATABASE_URL=postgresql://spacetime:spacetime@localhost:6432/spacetime
  PORT=6001
  

• server/drizzle.config.ts:

  typescriptCopy

  import { defineConfig } from 'drizzle-kit';
  
  export default defineConfig({
    schema: './src/schema.ts',
    out: './drizzle',
    dialect: 'postgresql',
    dbCredentials: {
      url: process.env.DATABASE_URL || 'postgresql://spacetime:spacetime@localhost:6432/spacetime',
    },
  });
  

• server/src/schema.ts — Drizzle ORM table definitions for all features

• server/src/index.ts — Express server with:

  • CORS configured for http://localhost:6273
  • Socket.io with CORS
  • REST endpoints for the app's resources (per the feature spec)
  • Socket.io events for real-time updates (per the feature spec)
  • Database queries via Drizzle ORM

Install and push schema:

cd <server-dir> && npm install
npx drizzle-kit push

Phase 2: (No bindings step)

Skip — PostgreSQL has no binding generation. The client calls REST/Socket.io APIs directly.

Phase 3: Generate Client

• client/package.json:

  jsonCopy

  {
    "name": "chat-client",
    "type": "module",
    "scripts": {
      "dev": "vite",
      "build": "tsc -b && vite build"
    },
    "dependencies": {
      "react": "^18.3.1",
      "react-dom": "^18.3.1",
      "socket.io-client": "^4.7.4"
    },
    "devDependencies": {
      "@types/react": "^18.3.12",
      "@types/react-dom": "^18.3.1",
      "@vitejs/plugin-react": "^4.3.4",
      "typescript": "^5.4.0",
      "vite": "^6.0.0"
    }
  }
  

• client/vite.config.ts — port 6273 (NOT 6173 — that's SpacetimeDB), proxy /api and /socket.io to http://localhost:6001

  typescriptCopy

  import { defineConfig } from 'vite';
  import react from '@vitejs/plugin-react';
  
  export default defineConfig({
    plugins: [react()],
    server: {
      port: 6273,
      proxy: {
        '/api': 'http://localhost:6001',
        '/socket.io': {
          target: 'http://localhost:6001',
          ws: true,
        },
      },
    },
  });
  

• client/tsconfig.json

• client/index.html

• client/src/main.tsx — React entry point

• client/src/App.tsx — Main component using fetch('/api/...') + Socket.io client

• client/src/styles.css — Dark theme styling

The client connects to the server via the Vite proxy — no hardcoded localhost:6001 in client code.

Critical: Initialize the socket.io client without a hardcoded URL so it routes through the Vite proxy (e.g. io() or io({ path: '/socket.io' })). Hardcoding http://localhost:6001 bypasses the proxy and breaks WebSocket upgrades.

Phase 4: Verify

# Server
cd <server-dir> && npm install && npx tsc --noEmit

# Client
cd <client-dir> && npm install && npx tsc --noEmit && npm run build

Both must pass. If either fails:

1. Read the error
2. Fix the code
3. Retry (up to 3 attempts)
4. Each fix counts as a reprompt — log it

Phase 5: Deploy

# Kill any existing servers
npx kill-port 6273 2>/dev/null || true
npx kill-port 6001 2>/dev/null || true

# Start the API server in background
cd <server-dir> && npx tsx src/index.ts &

# Wait for API server to be ready (poll http://localhost:6001 up to 30s)

# Start client dev server in background
cd <client-dir> && npm run dev &

Wait for both servers to be ready:

• API server at http://localhost:6001
• Client dev server at http://localhost:6273

Redeploy (for fix iterations)

• If server changed: kill and restart the Express server
  bashCopy

  npx kill-port 6001 2>/dev/null || true
  cd <server-dir> && npx tsx src/index.ts &
  

• If schema changed: push new schema before restarting
  bashCopy

  cd <server-dir> && npx drizzle-kit push
  

• If client changed: Vite HMR handles it automatically (or restart dev server if needed)

Key Differences from SpacetimeDB

For context on what makes this backend different (this helps the benchmark comparison):

App Identity

• HTML <title> MUST be "PostgreSQL Chat" (not "Chat App", not "SpacetimeDB Chat")
• The app MUST show "PostgreSQL Chat" as the visible header/title in the UI
• This distinguishes it from the SpacetimeDB version during testing

Port Configuration

Reference Files

The language and feature prompt files are provided as absolute paths in the launch prompt. No additional reference files are needed — this backend uses standard Node.js/TypeScript patterns.