# Inside the Backend — How Requests Travel from Agent to Database

> A concrete walk-through of the Express server (server.ts), the three-layer architecture (routes → services → providers/infra), the PostgREST proxy pattern used for database record access, the Socket.IO realtime push, and the database migration system that bootstraps every InsForge instance.

- Repository: InsForge/InsForge
- GitHub: https://github.com/InsForge/InsForge
- Human wiki: https://grok-wiki.com/public/wiki/insforge-insforge-357039661319
- Complete Markdown: https://grok-wiki.com/public/wiki/insforge-insforge-357039661319/llms-full.txt

## Source Files

- `backend/src/server.ts`
- `backend/src/infra/database/database.manager.ts`
- `backend/src/infra/realtime/realtime.manager.ts`
- `backend/src/infra/socket/socket.manager.ts`
- `backend/src/api/routes/database/records.routes.ts`
- `backend/src/services/database/postgrest-proxy.service.ts`
- `backend/src/infra/database/migrations/000_create-base-tables.sql`
- `backend/src/infra/config/app.config.ts`

---

<details>
<summary>Relevant source files</summary>
The following files were used as context for generating this wiki page:

- [backend/src/server.ts](backend/src/server.ts)
- [backend/src/infra/config/app.config.ts](backend/src/infra/config/app.config.ts)
- [backend/src/infra/database/database.manager.ts](backend/src/infra/database/database.manager.ts)
- [backend/src/infra/realtime/realtime.manager.ts](backend/src/infra/realtime/realtime.manager.ts)
- [backend/src/infra/socket/socket.manager.ts](backend/src/infra/socket/socket.manager.ts)
- [backend/src/api/routes/database/index.routes.ts](backend/src/api/routes/database/index.routes.ts)
- [backend/src/api/routes/database/records.routes.ts](backend/src/api/routes/database/records.routes.ts)
- [backend/src/services/database/postgrest-proxy.service.ts](backend/src/services/database/postgrest-proxy.service.ts)
- [backend/src/services/database/database-migration.service.ts](backend/src/services/database/database-migration.service.ts)
- [backend/src/infra/database/migrations/000_create-base-tables.sql](backend/src/infra/database/migrations/000_create-base-tables.sql)
- [backend/src/infra/database/migrations/017_create-realtime-schema.sql](backend/src/infra/database/migrations/017_create-realtime-schema.sql)
- [backend/src/utils/seed.ts](backend/src/utils/seed.ts)
</details>

# Inside the Backend — How Requests Travel from Agent to Database

InsForge's backend is an Express server that bridges AI agents, frontend clients, and a PostgreSQL database. Understanding how a request enters the server, gets authorized, reaches the right service, touches the database, and optionally triggers a realtime push to connected clients is the key to reading—and modifying—almost any part of the codebase. This page traces that full path with concrete file citations at every step.

---

## Server Bootstrap: What Runs First

Everything starts in `createApp()` and `initializeServer()` in `backend/src/server.ts`.

**Before** Express even accepts a connection, three blocking initialization steps run in order:

1. `DatabaseManager.getInstance().initialize()` — creates the `pg` connection pool to PostgreSQL.
2. `StorageService.getInstance().initialize()` — prepares the storage layer.
3. `LogService.getInstance().initialize()` — connects to CloudWatch (or local logger).

Only then does the Express `app` object get built, middleware is attached, and routes are mounted. After `app.listen()` returns, two more services are layered on top of the same `http.Server` handle:

- **`SocketManager`** — attaches Socket.IO to the HTTP server (`socketService.initialize(server)`).
- **`RealtimeManager`** — opens a dedicated PostgreSQL `LISTEN` connection (`realtimeManager.initialize()`).

```ts
// backend/src/server.ts:327-337
const server = app.listen(PORT, () => { ... });
const socketService = SocketManager.getInstance();
socketService.initialize(server);          // Socket.IO on top of the same port

const realtimeManager = RealtimeManager.getInstance();
await realtimeManager.initialize();        // LISTEN realtime_message
```

This order matters: if `DatabaseManager` fails, the process exits. Socket.IO and the realtime listener come up last, so the HTTP API is already serving by the time WebSocket clients try to connect.

Sources: [backend/src/server.ts:65-355]()

---

## Middleware Stack and Route Mounting

Incoming HTTP requests pass through this ordered middleware chain before any route handler sees them:

| Order | Middleware | Purpose |
|-------|-----------|---------|
| 1 | `cors({ origin: true, credentials: true })` | Allow all origins; expose `Content-Range` and `Preference-Applied` headers |
| 2 | `cookieParser()` | Parse cookies for refresh-token flows |
| 3 | `rateLimit` (3 000 req / 15 min) | Global IP-based rate limiter; skips `/api/health` |
| 4 | Request logger | Instruments `res.send` / `res.json` to capture size and duration |
| 5 | `express.raw()` on `/api/webhooks` | Raw body before JSON parsing so webhook signatures can be verified |
| 6 | S3 gateway on `/storage/v1/s3` | Streams S3 protocol bodies untouched before JSON middleware |
| 7 | `express.json({ limit: '100mb' })` | JSON body parsing for all other routes |

After the shared middleware, the API surface is organised under a single `/api` prefix, then split into named sub-routers:

```
/api/auth          authRouter
/api/database      databaseRouter   ← main focus of this page
/api/storage       storageRouter
/api/realtime      realtimeRouter
/api/functions     functionsRouter
... (14 more routers)
```

Sources: [backend/src/server.ts:86-224]()

---

## Three-Layer Architecture

The backend follows a clean routes → services → infrastructure layering:

```text
┌─────────────────────────────────────────────┐
│  HTTP Route Handler  (api/routes/**)        │  validate, auth, orchestrate
│    e.g. records.routes.ts                   │
└──────────────────┬──────────────────────────┘
                   │ calls
┌──────────────────▼──────────────────────────┐
│  Service Layer   (services/**)              │  business logic, retries
│    e.g. PostgrestProxyService               │
│         DatabaseMigrationService            │
└──────────────────┬──────────────────────────┘
                   │ uses
┌──────────────────▼──────────────────────────┐
│  Infrastructure  (infra/**)                 │  connections, pools, sockets
│    DatabaseManager (pg Pool)                │
│    SocketManager   (Socket.IO)              │
│    RealtimeManager (pg LISTEN)              │
└─────────────────────────────────────────────┘
```

**Routes** are thin: they authenticate the request, validate table names, and delegate to a service. They do not query the database directly.

**Services** own the non-trivial logic: the PostgREST proxy adds retry logic and admin-token injection; `DatabaseMigrationService` validates SQL ASTs before running them.

**Infrastructure** classes are singletons that hold expensive long-lived resources (connection pools, Socket.IO server, a dedicated LISTEN client). Routes and services call them via `getInstance()`.

Sources: [backend/src/api/routes/database/index.routes.ts:1-111](), [backend/src/api/routes/database/records.routes.ts:1-123](), [backend/src/services/database/postgrest-proxy.service.ts:1-170]()

---

## The Database Sub-Router

`/api/database` is itself further split into six sub-routes mounted in `backend/src/api/routes/database/index.routes.ts`:

| Mount path | Router file | Purpose |
|------------|-------------|---------|
| `/tables` | `tables.routes.ts` | DDL: create/alter/drop tables |
| `/records` | `records.routes.ts` | CRUD on user table rows — **proxied to PostgREST** |
| `/rpc` | `rpc.routes.ts` | Call PostgreSQL stored functions |
| `/advance` | `advance.routes.ts` | Raw SQL execution |
| `/migrations` | `migrations.routes.ts` | Apply custom versioned migrations |
| `/admin` | `admin.routes.ts` | Schema introspection (schemas, indexes, policies, triggers) |

Sources: [backend/src/api/routes/database/index.routes.ts:18-23]()

---

## The PostgREST Proxy Pattern for Record Access

The most interesting design decision in the database layer is that **row-level CRUD is not implemented in Express at all**. Instead, a PostgREST sidecar runs on `localhost:5430`, and the Express route simply forwards the request to it.

### Why PostgREST?

PostgREST translates REST requests directly into PostgreSQL queries, honoring Row Level Security (RLS) policies defined in SQL. This means the backend gets filtering, ordering, pagination, and access control essentially for free, without writing custom query-builder logic.

### The Forwarding Path

```
Client
  → GET /api/database/records/orders?status=eq.open
      ↓
  records.routes.ts  (verifyUser middleware)
      ↓
  PostgrestProxyService.forward({ method, path, query, headers, body })
      ↓
  Axios → http://localhost:5430/orders?status=eq.open
      ↓
  PostgREST → PostgreSQL query under the user's JWT role
      ↓
  Response forwarded back to client
```

**Key implementation details in `PostgrestProxyService`:**

1. **Connection pooling** — A dedicated `axios` instance reuses keep-alive HTTP connections to PostgREST (`keepAlive: true`, up to 20 sockets), avoiding handshake overhead on every request.

2. **Admin token injection** — If the caller provides a valid API key, the service replaces the `Authorization` header with an admin-level JWT, granting full table access regardless of RLS.

3. **Retry with exponential backoff** — Network errors (not HTTP-level errors) trigger up to 3 retries. The delay follows `min(200ms × 2.5^(attempt-1), 1000ms)`.

4. **Empty-value filtering** — Before forwarding `POST`/`PATCH`/`PUT` bodies, the route handler consults `DatabaseManager.getColumnTypeMap()` (cached for 5 minutes) and strips empty strings from UUID columns to prevent type coercion errors.

```ts
// backend/src/services/database/postgrest-proxy.service.ts:8
const postgrestUrl = process.env.POSTGREST_BASE_URL || 'http://localhost:5430';

// backend/src/services/database/postgrest-proxy.service.ts:138-155
for (let attempt = 1; attempt <= maxRetries; attempt++) {
  try {
    response = await postgrestAxios(axiosConfig);
    break;
  } catch (error) {
    const shouldRetry = axios.isAxiosError(error) && !error.response && attempt < maxRetries;
    if (shouldRetry) {
      const backoffDelay = Math.min(200 * Math.pow(2.5, attempt - 1), 1000);
      await new Promise((resolve) => setTimeout(resolve, backoffDelay));
    } else { throw error; }
  }
}
```

Sources: [backend/src/services/database/postgrest-proxy.service.ts:1-170](), [backend/src/api/routes/database/records.routes.ts:32-117]()

### Socket Broadcast on Mutations

After a successful `POST` or `DELETE`, the records route fires a real-time side-effect. It broadcasts a `DATA_UPDATE` Socket.IO event to every admin client so the dashboard can refresh:

```ts
// backend/src/api/routes/database/records.routes.ts:100-110
if (['POST', 'DELETE'].includes(method)) {
  const socket = SocketManager.getInstance();
  socket.broadcastToRoom(
    'role:project_admin',
    ServerEvents.DATA_UPDATE,
    { resource: DataUpdateResourceType.DATABASE,
      data: { changes: [{ type: 'records', name: tableName }] } },
    'system'
  );
}
```

Sources: [backend/src/api/routes/database/records.routes.ts:99-113]()

---

## Socket.IO Realtime Push

`SocketManager` wraps Socket.IO and is the single point for all WebSocket communication. It attaches to the same HTTP server as Express (no separate port), so clients use one connection for both REST and WebSocket traffic.

### Authentication

Every Socket.IO connection is authenticated by middleware before any event handler runs. Two auth paths are supported:

- **API key** (`socket.handshake.auth.apiKey`) — verified via `SecretService`. On success, the socket gets a synthetic `api-key-client` user identity.
- **JWT** (`socket.handshake.auth.token`) — verified via `TokenManager.verifyToken()`. The decoded role and user ID are stored on `socket.data.user`.

Sources: [backend/src/infra/socket/socket.manager.ts:73-141]()

### Room Model

When a socket connects, it is automatically placed in two rooms:

- `user:<userId>` — for targeted per-user messages.
- `role:<role>` — for role-based broadcasts (e.g., the `DATA_UPDATE` above goes to `role:project_admin`).

When a client subscribes to a realtime channel (event `REALTIME_SUBSCRIBE`), the server joins the socket to a third room: `realtime:<channelName>`. Presence tracking records who is in each room.

Sources: [backend/src/infra/socket/socket.manager.ts:183-199](), [backend/src/infra/socket/socket.manager.ts:280-350]()

---

## The Realtime Pipeline: From Database Trigger to Browser

This is the most architecturally distinctive feature. InsForge routes realtime messages entirely through PostgreSQL's notification system, not through in-process queues.

### How it works, step by step

```mermaid
sequenceDiagram
    participant DB as PostgreSQL
    participant RM as RealtimeManager<br/>(LISTEN client)
    participant SM as SocketManager
    participant WS as Browser WebSocket

    Note over DB: A trigger calls realtime.publish(...)
    DB->>DB: INSERT INTO realtime.messages
    DB->>DB: trg_message_notify fires pg_notify('realtime_message', message_id)
    DB-->>RM: notification event (message_id only)
    RM->>DB: SELECT * FROM realtime.messages WHERE id = message_id
    RM->>DB: SELECT * FROM realtime.channels WHERE id = channel_id
    RM->>SM: broadcastToRoom('realtime:<channelName>', eventName, payload)
    SM->>WS: Socket.IO emit to subscribed clients
    RM->>DB: UPDATE realtime.messages SET ws_audience_count = N
```

**Why send only a UUID in `pg_notify`?** PostgreSQL imposes an 8 KB size limit on notification payloads. Sending just the `message_id` and fetching the full row afterwards bypasses this limit entirely.

```sql
-- backend/src/infra/database/migrations/017_create-realtime-schema.sql:202-210
CREATE OR REPLACE FUNCTION realtime.notify_on_message_insert()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('realtime_message', NEW.id::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```

```ts
// backend/src/infra/realtime/realtime.manager.ts:55-56
await this.listenerClient.query('LISTEN realtime_message');
this.listenerClient.on('notification', (msg) => {
  void this.handlePGNotification(msg.payload);   // msg.payload is just the UUID
});
```

Sources: [backend/src/infra/database/migrations/017_create-realtime-schema.sql:197-217](), [backend/src/infra/realtime/realtime.manager.ts:45-120]()

### The `realtime.publish()` SQL function

Developers can publish events from their own database triggers by calling:

```sql
PERFORM realtime.publish(
  'order:' || NEW.id::text,   -- channel name (may match wildcard like 'order:%')
  'order_updated',             -- event name
  jsonb_build_object('id', NEW.id, 'status', NEW.status)
);
```

The function resolves the channel by exact match first, then by `LIKE` pattern, inserts the message, and the `pg_notify` trigger fires automatically. The function is `SECURITY DEFINER` and only executable by the backend role — end users cannot invoke it directly.

Sources: [backend/src/infra/database/migrations/017_create-realtime-schema.sql:149-196]()

### Reconnection Resilience

`RealtimeManager` uses a dedicated `pg.Client` (not the pool) because pooled connections cannot hold a persistent `LISTEN` state. If the connection drops, it retries up to 10 times with exponential backoff starting at 5 seconds.

Sources: [backend/src/infra/realtime/realtime.manager.ts:195-220]()

---

## Migration System: Bootstrapping Every Instance

InsForge ships 44+ numbered SQL migration files (as of this writing) under `backend/src/infra/database/migrations/`. They run sequentially when a new instance is first set up and are tracked in a system table so they are never applied twice.

### What migration 000 creates

The very first migration (`000_create-base-tables.sql`) establishes all the internal system tables that the backend depends on at startup:

| Table | Purpose |
|-------|---------|
| `_config` | Key-value system configuration |
| `_metadata` | Version and creation date |
| `_storage_buckets` / `_storage` | File storage registry |
| `_mcp_usage` | AI tool call tracking |
| `_ai_configs` / `_ai_usage` | AI provider configuration and token usage |
| `_edge_functions` | Edge function code storage |
| `_user` / `_account` | Auth users and OAuth connections |

A shared `update_updated_at_column()` trigger function is also created here and reused by later migrations.

Sources: [backend/src/infra/database/migrations/000_create-base-tables.sql:1-142]()

### Custom migrations via the API

The `DatabaseMigrationService` lets developers apply their own versioned migrations through `/api/database/migrations`. Before running any SQL, it validates the AST with `libpg-query` to block:

- `BEGIN` / `COMMIT` / `ROLLBACK` statements (migrations must not manage their own transactions).
- Writes to managed schemas (`auth`, `realtime`, `storage`).
- Writes to system-internal schemas.

This guardrail prevents migrations from corrupting the tables InsForge itself depends on.

Sources: [backend/src/services/database/database-migration.service.ts:27-62]()

---

## Configuration

All environment-driven settings are typed in `backend/src/infra/config/app.config.ts`. The key variables for the backend request path:

| Variable | Default | Controls |
|----------|---------|---------|
| `PORT` | `7130` | Express listen port |
| `POSTGRES_HOST/DB/USER/PASSWORD` | `localhost/insforge/postgres/postgres` | `pg.Pool` connection |
| `POSTGREST_BASE_URL` | `http://localhost:5430` | PostgREST proxy target |
| `JWT_SECRET` | `your_jwt_secret` | Token signing/verification |
| `ACCESS_API_KEY` | `your_api_key` | API key for admin access |
| `MAX_JSON_BODY_SIZE` | `100mb` | Express JSON body limit |
| `DENO_RUNTIME_URL` | `http://localhost:7133` | Edge function runtime |
| `FLY_API_TOKEN` + `FLY_ORG` | (empty) | Opt-in compute layer |

Sources: [backend/src/infra/config/app.config.ts:30-63](), [backend/src/server.ts:185-190](), [backend/src/services/database/postgrest-proxy.service.ts:8]()

---

## Summary

An HTTP request to InsForge's backend travels from Express middleware (CORS → rate limiter → auth) through a three-layer stack (route handler → service → infrastructure) and ultimately reaches PostgreSQL either directly via the `pg.Pool` or indirectly via the PostgREST sidecar. On write operations, a Socket.IO broadcast notifies admin dashboards immediately. For developer-defined realtime events, the path goes through PostgreSQL itself: a trigger calls `realtime.publish()`, which inserts a message and fires `pg_notify`; the `RealtimeManager`'s dedicated listener picks up the UUID, fetches the full record, and fans it out to subscribed WebSocket clients. The entire schema needed for this to work is bootstrapped by a numbered migration sequence, starting from `000_create-base-tables.sql`, ensuring every self-hosted InsForge instance begins from a consistent, reproducible state.

Sources: [backend/src/server.ts:324-355](), [backend/src/infra/realtime/realtime.manager.ts:86-120]()