Self Hosting

System Overview

WoWSQL Self-Hosted is a collection of purpose-built services connected through a single API gateway. Each service is independently containerized and communicates over an internal Docker network.

            ┌─────────────────────┐
            │  Browser / SDK App  │
            └──────────┬──────────┘
                       │
                       ▼
        ┌──────────────────────────────┐
        │     Kong API Gateway :8080   │
        │     (single entry point)     │
        └──┬───────┬───────┬───────┬───┘
           │       │       │       │
           ▼       ▼       ▼       ▼
       ┌──────┐┌──────┐┌───────┐┌──────────┐
       │ REST ││ Auth ││ Store ││ Realtime │
       └──┬───┘└──┬───┘└──┬────┘└────┬─────┘
          │       │       │          │
          ▼       ▼       ▼          ▼
   ┌────────────────────────┐  ┌──────────┐
   │    PostgreSQL 18       │  │  Redis   │
   │        :5432           │  │  :6379   │
   └────────────────────────┘  └──────────┘

        ┌──────────────────────────────┐
        │       Studio (UI) :3000      │
        └──────────────┬───────────────┘
                       │
                       ▼
              Kong Gateway :8080
            (API calls from browser)

Request Flow

Every client request follows this path:

Client sends request to http://localhost:8080 with an apikey header
Kong receives the request and applies a Lua plugin that converts the apikey header into an Authorization: Bearer token
Kong routes the request to the appropriate service based on the URL path:
- /rest/v1/* goes to PostgREST
- /auth/v1/* goes to the Auth service
- /storage/v1/* goes to the Storage service
- /realtime/v1/* goes to the Realtime service
- /api/v1/* goes to the Backend service
The target service validates the JWT and executes the operation
Response flows back through Kong to the client

Service Details

PostgreSQL

The database is the core of the system. It stores all user data, authentication state, storage metadata, and system configuration.

Image: wowsql/postgres:18
Based on official PostgreSQL 18 with pre-installed extensions
Includes database roles: anon, authenticated, service_role, authenticator
Row Level Security (RLS) is supported and recommended for all user-facing tables

PostgREST

Generates a REST API automatically from your PostgreSQL schema. Any table or view in the public schema becomes an API endpoint.

Image: postgrest/postgrest:v12.2.0
Reads the JWT to determine the database role (anon or authenticated)
Supports filtering, pagination, ordering, and full-text search via URL parameters
Respects RLS policies defined on your tables

Kong API Gateway

Single entry point for all external traffic. Handles routing, CORS, and JWT transformation.

Image: kong:3.9
Declarative configuration (no database required)
Lua plugin extracts the JWT role and sets X-Consumer-Username for downstream services

Auth Service

Handles user registration, login, token refresh, and password reset for your application users.

Image: wowsql/auth:latest
Issues JWTs with the authenticated role for signed-in users
Stores user records in the auth.users table
Supports email/password and OAuth (Google, GitHub) when configured

Storage Service

File upload and download with bucket-based access control.

Image: wowsql/storage:latest
Files stored on the local filesystem (Docker volume)
Metadata stored in the storage schema
Access controlled by RLS policies on storage.objects

Realtime Service

WebSocket server that broadcasts database changes to connected clients.

Image: wowsql/realtime:latest
Listens to PostgreSQL logical replication
Clients subscribe to specific tables via WebSocket
Enable realtime per-table from the dashboard

Backend Service

Lightweight FastAPI service that powers the dashboard UI. Handles admin authentication, project settings, and database management operations.

Image: wowsql/backend:latest
Not used by SDK clients, only by the Studio dashboard
Single admin account (created on first visit)

Studio

Next.js web application providing a visual interface for database management.

Image: wowsql/studio:latest
Table editor, SQL editor, auth user management, settings
Communicates with the backend through Kong

Security Model

JWT-based access control

All API access is authenticated via JWT tokens. The token contains a role claim that maps to a PostgreSQL role:

JWT Role	PostgreSQL Role	Access Level
`anon`	`anon`	Public access, subject to RLS
`authenticated`	`authenticated`	Logged-in user access, subject to RLS
`service_role`	`service_role`	Full access, bypasses RLS

Row Level Security

RLS policies are the primary authorization mechanism. When enabled on a table, PostgreSQL enforces access rules at the row level based on the current role and user context.

-- Example: users can only read their own rows
CREATE POLICY "Users read own data"
  ON public.profiles
  FOR SELECT
  USING (auth.uid() = user_id);

Network isolation

All services communicate on an internal Docker network. Only Kong (port 8080), PostgreSQL (port 5432), and Studio (port 3000) are exposed to the host.

Data Persistence

Three Docker volumes store persistent data:

Volume	Contents
`db-data`	PostgreSQL data directory (all database content)
`redis-data`	Redis persistence (cache, realtime state)
`storage-data`	Uploaded files

Backup: Regularly back up the db-data volume. Use pg_dump for logical backups or snapshot the volume for physical backups.

Scaling Considerations

The default configuration is optimized for single-server deployments. For higher traffic:

Increase PostgreSQL shared_buffers and work_mem via custom postgresql.conf
Add connection pooling with PgBouncer between PostgREST and PostgreSQL
Run multiple PostgREST instances behind Kong for read-heavy workloads
Move Redis to a dedicated instance for large realtime workloads

PreviousVS Code NextSDK Connection Guide