Installation and configuration

Requirements

Component	Version	Notes
PostgreSQL	15 or newer (17 recommended)	Needs the pgcrypto extension (part of standard PostgreSQL)
Node.js	20 or newer	.nvmrc pins the version used in development
Docker (optional)	any recent	For the bundled development database

pgkiln does not need any other services; there is no separate web listener (like ORDS for APEX). For REST APIs you can run PostgREST next to it (chapter 13).

Quick start (development)

git clone git@github.com:pgkiln/pgkiln.git pgkiln
cd pgkiln
npm install
cp .env.example .env
npm run setup        # starts PostgreSQL 17 in Docker on port 5434 and installs pgkiln (the migrations)
npm run dev          # starts pgkiln on http://127.0.0.1:3100 and restarts on code changes

Open the builder at http://127.0.0.1:3100/builder, sign in as admin / admin (change the password on the Developers page straight away) and create your first application from a table (Create application), or follow the tutorial.

The example application (optional)

pgkiln comes with an example application, HR: employees, departments, leave requests with an approval task, a dashboard, a REST API, translations, documents and more, built the way you would build your own (examples/hr/). It is not part of pgkiln; install it to see the features at work:

npm run example:hr   # http://127.0.0.1:3100/a/hr: king, blake, allen or demo (password = username)

The tests use it as their fixture: npm test and npm run test:e2e install it first.

Using your own PostgreSQL instead of Docker

1. Create a database and an owner login (a superuser is simplest for development):

   create role pgkiln login password 'choose-a-password' superuser;
   create database pgkiln owner pgkiln;

2. Point DATABASE_URL in .env at it, then run npm run db:migrate (and npm run example:hr for the example application).
3. Set RUNTIME_DATABASE_URL for the pgkiln_runtime role that the first migration creates (see below).

The owner doesn't have to be a superuser: a role with CREATEROLE that owns the database is enough (pgcrypto is a trusted extension).

Installing into an existing database

pgkiln can live next to your own schemas: it adds the schema meta, three bookkeeping tables in public (pgkiln_migration, pgkiln_seed, pgkiln_install_log) and the login roles pgkiln_runtime, pgkiln_authenticator and pgkiln_anon (roles belong to the whole server). It changes nothing else. When the pgkiln owner is not the database's owner, a database administrator grants, once:

create role pgkiln login createrole password 'choose-a-password';
grant connect, create on database shop to pgkiln;
grant create on schema public to pgkiln;   -- since PostgreSQL 15 only the database owner may by default

Applications run as a role of their own, which the builder grants rights on its parsing schema. For a schema the pgkiln owner doesn't own, its owner passes those rights on first:

-- as the owner of the schema sales
grant usage on schema sales to pgkiln with grant option;
grant select, insert, update, delete on all tables in schema sales to pgkiln with grant option;
grant usage, select on all sequences in schema sales to pgkiln with grant option;

(or the administrator grants them to the application's role, app_<alias>, directly).

The two database connections

pgkiln deliberately uses two database logins:

Connection	Setting	Role	Used for
Owner	DATABASE_URL	the owner of the meta schema (e.g. pgkiln)	migrations, the builder, the SQL Workshop
Runtime	RUNTIME_DATABASE_URL	pgkiln_runtime (created by migration 001)	running applications

The runtime role is least privilege. It can read application definitions and manage sessions, but it cannot read developer accounts, password hashes or instance secrets. It is NOINHERIT, so it reaches application data only by switching to an application's own database role (SET LOCAL ROLE) for the duration of a request. If RUNTIME_DATABASE_URL is missing, pgkiln falls back to the owner connection and prints a warning; don't run like that in production.

The migration creates pgkiln_runtime with the password pgkiln_runtime. Change it:

alter role pgkiln_runtime password 'a-long-random-password';

Configuration reference

All settings are environment variables. They can also be placed in .env in the project root, which is read at startup; real environment variables take precedence.

Variable	Default	Meaning
DATABASE_URL	postgres://pgkiln:pgkiln@localhost:5434/pgkiln	Owner connection (builder, migrations)
RUNTIME_DATABASE_URL	(falls back to DATABASE_URL)	Least-privilege connection that runs applications
PORT	3100	HTTP port
HOST	127.0.0.1	Interface to listen on; use 0.0.0.0 in a container
PUBLIC_URL	http://127.0.0.1:<PORT>	The address users reach pgkiln at (e.g. https://apps.example.com). Single sign-on redirect URIs are built from it
API_URL	http://127.0.0.1:3000	Where PostgREST serves the REST API (chapter 13)
API_JWT_SECRET	(none)	Signs REST API tokens; at least 32 characters, the same as PostgREST's jwt-secret. Without it, tokens can't be issued
COOKIE_SECURE	false	true behind HTTPS: marks cookies Secure and sends HSTS
TRUST_PROXY	false	Behind a reverse proxy, so client IPs (used by login throttling) come from X-Forwarded-For: true for one proxy, a number for several in a row (e.g. a CDN in front of nginx: 2), or the proxies' addresses or subnets (10.0.0.0/8,192.168.1.10). Only the entries those proxies added are believed, never the ones a client sends along
PGKILN_AUTH_HEADER_PROXIES	(none)	Comma-separated IPs and CIDRs (e.g. 10.0.0.5, 192.168.10.0/24) of the reverse proxies whose user header apps with HTTP header authentication trust; checked against the connection's own address, never X-Forwarded-For. Unset: header sign-in is refused (chapter 8)
SESSION_IDLE_MINUTES	60	A session ends after this long without requests. This and the next four can also be set in the builder (Workspace utilities → Instance settings), which wins over the variable
SESSION_MAX_HOURS	8	A session ends this long after sign-in, whatever the activity
LOGIN_WINDOW_MINUTES	15	Window for counting failed sign-ins
LOGIN_MAX_FAILURES_PER_USER	5	Failed sign-ins per username (since their last success) before a lock
LOGIN_MAX_FAILURES_PER_IP	50	Failed sign-ins per IP address before a lock
STATEMENT_TIMEOUT	30s	Maximum run time of any application SQL statement
DB_POOL_SIZE	10	Connections per pool (there are two pools)
MAX_UPLOAD_MB	10	Largest file a file item accepts (an item's max_mb can only lower it)
DATA_LOAD_MAX_MB	50	Largest file for SQL Workshop → Load Data and Create → From a file
DATA_LOAD_MAX_ROWS	100000	Most rows loaded from one file
PDF_MAX_ROWS	5000	Most rows in a report PDF (1 to 100,000; read with a cursor in batches)
DOWNLOAD_MAX_ROWS	1000000	Most rows in a report's CSV or Excel download and in a SQL Workshop → Unload Data file (streamed; at most 1,048,575)
SAMPLE_DATA_STATEMENT_TIMEOUT	5min	Statement timeout of SQL Workshop → Sample Data (each insert of a preview or a run)
SAMPLE_DATA_MAX_ROWS	100000	Most rows one Sample Data run generates (all tables together)
UNLOAD_STATEMENT_TIMEOUT	5min	Statement timeout of SQL Workshop → Unload Data (each statement: the cursor and every batch of rows)
REGION_CACHE_MAX_ENTRIES	1000	Most regions in the region cache of one server process (0 turns caching off)
REGION_CACHE_MAX_MB	64	Memory for the region cache of one server process
AUTOMATIONS	on	off stops this server from running automations
SCHEDULER_INTERVAL_S	30	How often the automation scheduler looks for due runs
BACKGROUND_PROCESSES	on	off stops this server from running background execution chains (another server runs them)
PROCESS_JOB_INTERVAL_S	10	How often a server looks for queued background chains (besides being woken by NOTIFY)
PDF_FONT, PDF_FONT_BOLD	(none)	TrueType fonts for report PDFs, for text beyond Western European (e.g. DejaVuSans.ttf)
ANTHROPIC_API_KEY	(none)	Default API key of AI services with the provider Claude that have no key of their own
OPENAI_API_KEY	(none)	Default API key of AI services with the provider OpenAI that have no key of their own
PGKILN_SECRET_KEY	(none)	Encrypts the secrets of web credentials and the API keys of AI services; at least 32 characters (e.g. openssl rand -base64 32). Keep it outside the database; changing it means entering the secrets again (chapter 19)
PGKILN_REST_ALLOWED_HOSTS	(none: no outgoing calls)	Hosts REST data sources and invoke_api may call: api.example.com, *.example.com, host:8443, * (any public host) (chapter 19)
PGKILN_REST_PRIVATE_HOSTS	(none)	Hosts that may resolve to private, loopback or link-local addresses (also allows them)
PGKILN_PUSH_HOSTS	the browsers' push services	The hosts push notifications may be posted to: fcm.googleapis.com,updates.push.services.mozilla.com,web.push.apple.com,*.notify.windows.com when unset (chapter 17)
PGKILN_PUSH_SUBJECT	PUBLIC_URL when it is https	The contact the push services see (mailto:ops@example.com or an https URL); Apple refuses notifications without one
PGKILN_PUSH_PRIVATE_HOSTS	(none)	For tests only: a push service on a private or loopback address, also over plain http
PGKILN_REST_MAX_BYTES	5000000	Largest web service response read (also after decompression)
MIGRATE_ON_START	false	true applies missing migrations when the server starts. Without it, a server whose database lacks migrations answers every request with 503 and names the missing files until they are applied (npm run db:migrate or pgkiln migrate; no restart needed)
LOG_LEVEL	info	fatal, error, warn, info, debug, trace

npm scripts

Script	What it does
npm run dev	Start with auto-restart on changes
npm start	Start (production)
npm run setup	Start the Docker database and apply the migrations
npm run db:up	Start the Docker database
npm run db:migrate	Apply pending migrations (db/migrations/*.sql)
npm run example:hr	Apply migrations, then install the HR example application (examples/hr/*.sql)
npm run db:reset	Delete the Docker database and set it up again
npm run typecheck	TypeScript type check
npm test	Unit and security tests (need the database; install the HR example first, as their fixture)
npm run test:e2e	Browser tests at phone, tablet and desktop widths (run npx playwright install chromium once)

Docker

The quickest way to run pgkiln: an image with the server and a compose file with PostgreSQL 17, in deploy/. Docker is all you need.

cd deploy
cp .env.example .env
# fill in POSTGRES_PASSWORD, RUNTIME_PASSWORD, PGKILN_SECRET_KEY (e.g. `openssl rand -hex 24` each)
# and PGKILN_ADMIN_PASSWORD (12+ characters)
docker compose up -d

Open http://127.0.0.1:3100/builder and sign in as admin with PGKILN_ADMIN_PASSWORD.

What happens at each start (scripts/docker-start.ts): the container checks the settings and lists everything missing at once (it doesn't start on empty or too short secrets), waits for the database, applies new migrations (with a lock, so several containers migrate once), and replaces the builder's admin / admin with PGKILN_ADMIN_PASSWORD (a password changed later in the builder is kept). pgkiln_runtime gets the password in RUNTIME_DATABASE_URL unless it already signs in with it; pgkiln_authenticator (PostgREST) gets PGKILN_AUTHENTICATOR_PASSWORD, or else a random one while it has its well-known default. Roles the start creates get their passwords straight away. PostgreSQL roles belong to the whole server, not to one database: other pgkiln databases on the same server share them, and with them these passwords. When the server doesn't check passwords for the container's connection (trust in pg_hba.conf), the start can't tell whether an older role has the password you configured. The same holds when the check fails for another reason, such as a network error. The start then leaves that password alone (other databases may use it), says so in the log, and you set it yourself. Without PGKILN_AUTHENTICATOR_PASSWORD, checking the authenticator's default costs one failed sign-in in the server log per start; setting it avoids that. GET /healthz answers ok when the database is reachable; the image's health check uses it.

POSTGRES_PASSWORD counts at the first start only: the bundled database keeps it in the volume pgdata. To change it later, change it in the database first (docker compose exec db psql -U pgkiln -c "\password pgkiln"), then in .env. A wrong password stops the container at once with a message saying so.

Settings go in deploy/.env: the ones in .env.example, and any other setting of the configuration reference.

Setting	Default	Meaning
COMPOSE_PROFILES	db	db: the bundled PostgreSQL (its data is in the volume pgdata). Remove it to use your own server; add https for Caddy
DATABASE_URL, RUNTIME_DATABASE_URL	the bundled database	Your own PostgreSQL (existing database); a server on the Docker host itself is host.docker.internal
PGKILN_PORT, PGKILN_BIND	3100, 127.0.0.1	Only this machine can connect by default; PGKILN_BIND=0.0.0.0 opens plain HTTP to the network
PGKILN_DOMAIN		With the https profile: Caddy gets a certificate for this name (its DNS must point at the machine, ports 80 and 443 open). Set PUBLIC_URL=https://…, COOKIE_SECURE=true and TRUST_PROXY=true with it. Installing apps on phones and push notifications need HTTPS
PGKILN_EXAMPLE		hr installs the HR example (its demo users have weak passwords: not on a public server)
PGKILN_IMAGE	pgkiln:local	Use a published image instead of building one from this checkout

Upgrading: built from this checkout: git pull, then docker compose up -d --build. With a published image (PGKILN_IMAGE): docker compose pull, then docker compose up -d (--build would build this checkout under the published name). The new container migrates the database before it starts serving. Back up first (docker compose exec db pg_dump -U pgkiln pgkiln > backup.sql). Keep PGKILN_SECRET_KEY: without it, stored secrets (web credentials, AI keys, push keys) can't be decrypted.

Upgrading

Every schema change ships as a new, numbered file in db/migrations/. The runner records applied files in public.pgkiln_migration and applies only new ones, each in its own transaction:

git pull
npm ci
npm run db:migrate
# restart the server

Back up the database before upgrading. Released migrations are never modified.

Each run that applies a file (or fails) is recorded in public.pgkiln_install_log; administrators see it, with the applied migrations and any the database still misses, under Builder → Workspace utilities → Installation.

Production deployment

A typical setup: pgkiln runs as a service behind a reverse proxy that terminates HTTPS.

Browser ──HTTPS──> nginx / Caddy / Traefik ──HTTP──> pgkiln (node) ──> PostgreSQL

1. Database
   • Use a dedicated owner role (it doesn't need to be a superuser once migrations have run, but it must own the meta schema and be allowed to create roles if developers create apps in the builder).
   • Set a strong password for pgkiln_runtime.
   • Enable regular backups (pg_dump or your provider's snapshots). Everything, including application definitions, lives in the database.
2. Environment

   NODE_ENV=production
   HOST=0.0.0.0
   PORT=3100
   DATABASE_URL=postgres://pgkiln_owner:...@db:5432/pgkiln
   RUNTIME_DATABASE_URL=postgres://pgkiln_runtime:...@db:5432/pgkiln
   COOKIE_SECURE=true
   TRUST_PROXY=true
   PUBLIC_URL=https://apps.example.com
   # only with a REST API (PostgREST):
   API_URL=https://api.example.com
   API_JWT_SECRET=<48+ random characters, same as PostgREST's jwt-secret>

3. Process manager. Run npm start under systemd, a container orchestrator or pm2. Example systemd unit:

   [Service]
   WorkingDirectory=/opt/pgkiln
   EnvironmentFile=/opt/pgkiln/.env
   ExecStart=/usr/bin/npm start
   Restart=always
   User=pgkiln

4. Reverse proxy (nginx example):

   location / {
     proxy_pass http://127.0.0.1:3100;
     proxy_set_header Host $host;
     proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
     proxy_set_header X-Forwarded-Proto $scheme;
   }

5. Restrict the builder. /builder is for developers. Consider allowing it only from your office network or VPN at the proxy (for example an nginx location /builder { allow …; deny all; }).
6. Change default passwords: admin in the builder (Developers page), and the demo users if the sample is installed. Don't install the example application in production: npm run db:migrate installs pgkiln only.
7. Walk through the checklist at the end of SECURITY.md.

Scaling

pgkiln keeps no state in memory between requests (sessions live in meta.session), so you can run several instances behind a load balancer. Each instance opens up to 2 × DB_POOL_SIZE database connections.

Backups and moving applications

• Everything is in PostgreSQL: application definitions (meta.*), sessions and your data. A normal pg_dump backs it all up.
• To move a single application between environments (development → test → production), use Builder → App → Export, which downloads JSON, then Builder → Import on the target, or in SQL:

  select meta.export_app('hr');                  -- returns jsonb
  select meta.import_app('<the json>', 'hr');    -- returns the new app id

  The export contains the application only, not your tables, functions, roles or users. Ship those as SQL migrations in your own project, and create users on the target.