Export

The export script bundles everything needed to restore an Alchemify instance into a single .tar.gz archive: a full database dump, uploaded files, and a README with setup instructions.

Prerequisites

PostgreSQL dba access — the script connects as dba (or the user you specify with -U)
An owner account — only users with the owner role can run an export
export-db.sh — called internally to produce the SQL dump

Usage

./scripts/export.sh --email owner@example.com
./scripts/export.sh --email owner@example.com -d mydb -o myexport.tar.gz

The script prompts for your password interactively (hidden input). The password never appears in shell history or the process list.

Options

Flag	Description	Default
`--email EMAIL`	Owner email address (required)	—
`-o, --output FILE`	Output archive path	`alchemify-export-<date>.tar.gz`
`-d, --database NAME`	Database name	`alchemify`
`-U, --user NAME`	PostgreSQL user	`dba`
`-H, --host HOST`	PostgreSQL host	`localhost`
`-p, --port PORT`	PostgreSQL port	`5432`

Archive Contents

alchemify-export/
├── README.md          # Setup and restore instructions
├── sql/
│   └── dump.sql       # Full database dump
└── files/
    └── (uploaded files)

sql/dump.sql — produced by export-db.sh. Contains roles, schema, data, and a freshly generated authenticator password. Chat history and magic link tokens are excluded.
files/ — all files from the server’s upload directory (UPLOAD_DIR). If the directory is missing or empty, this folder will be empty.
README.md — generated instructions including the authenticator password and environment configuration guidance.

Restore Walkthrough

After extracting the archive (tar xzf alchemify-export-*.tar.gz):

1. Create a database and restore the dump

createdb myapp
psql -v ON_ERROR_STOP=1 -U <superuser> myapp -f alchemify-export/sql/dump.sql

2. Configure environment

Create apps/server/.env using the authenticator password from the README:

DATABASE_URL=postgres://authenticator:<password>@localhost:5432/myapp
JWT_SECRET=<your-jwt-secret>
PORT=3000

3. Copy uploaded files

Copy the contents of alchemify-export/files/ to the server’s upload directory (matching UPLOAD_DIR in your .env, default: ./uploads).

4. Install and start

pnpm install
pnpm dev

Security

The archive contains database credentials (the authenticator password). Handle it securely:

Don’t share it over unencrypted channels
Delete it after restoring if you no longer need it
The owner’s password is never written to the archive — it’s only used for authentication during the export process

Upload Directory Resolution

The script resolves the upload directory in this order:

$UPLOAD_DIR environment variable (if set)
UPLOAD_DIR= line from apps/server/.env (if file exists)
Default: ./uploads (relative to repo root)

Relative paths are resolved against the repository root.

What’s Included vs Excluded

The export scripts are selective about what goes into the archive. The goal is a clean, portable snapshot — no ephemeral tokens, no chat history.

Included

Category	Details
Roles & grants	All 7 roles (`dba`, `authenticator`, `owner`, `admin`, `staff`, `member`, `anon`) with correct attributes and grants. Created idempotently so restore works on a fresh database.
Schema	All tables, indexes, triggers, functions, sequences, and RLS policies in both `public` and `sys` schemas.
Business data	Every row in every user-created table.
User accounts	`sys.users` — all user records and role assignments.
Pages	`sys.pages` — page layouts, navigation settings, publish status.
Files table	`sys.files` — file metadata records.
Metadata tables	`sys.table_metadata`, `sys.column_metadata`, `sys.app_metadata` — all configuration the frontend and AI use.
Request log	`sys.request_log` — query audit trail.
Uploaded files	All files from `UPLOAD_DIR`, bundled in the `files/` directory of the archive.
Authenticator password	Freshly generated 48-character random hex string per export.

Excluded

Category	What happens	Why
`sys.conversations`	Entire table excluded (`-T` flag)	Chat history is session-specific to the builder and not part of the app’s data.
`sys.messages`	Entire table excluded (`-T` flag)	Same as conversations — builder chat artifacts.
`public.conversations` / `public.messages` views	Excluded if they are views (detected dynamically)	These are convenience views over the chat tables. Real business tables with these names are preserved.
`sys.magic_links` data	Table schema kept, rows purged (`--exclude-table-data`)	Tokens are single-use and time-sensitive — stale tokens in an export are a security risk with no upside.

Docker Deployment

The Docker stack runs the exported app as three containers orchestrated by docker-compose.yml.

Architecture

                        ┌──────────────────────┐
                        │       nginx           │
                        │       :80             │
                        └──┬────────────┬───────┘
                           │            │
                  /api/*   │            │  /*
                           ▼            ▼
                  ┌──────────────┐   static files
                  │  Express app │   (SPA fallback)
                  │    :3000     │
                  └──────┬───────┘
                         │
                         ▼
                  ┌──────────────┐
                  │  PostgreSQL  │
                  │    :5432     │
                  └──────────────┘

nginx serves the frontend SPA and reverse-proxies API requests:

/api/* → app:3000/* (prefix stripped)
/api/events → app:3000/events (SSE — buffering disabled, 24h timeout)
/assets/* → static files (1-year immutable cache)
/* → SPA fallback (/index.html)

Express app is the thin SQL proxy, built as a multi-stage Docker image (no source code or dev dependencies in the final image).

PostgreSQL 16 runs the init-db.sh script on first startup to apply schema.sql, sample-app.sql, and optionally chat-schema.sql.

Environment Variables

Variable	Service	Description	Default
`DATABASE_URL`	app	PostgreSQL connection string (authenticator role)	—
`JWT_SECRET`	app	Token signing key	—
`PORT`	app / nginx	App listens on this port; nginx uses `${PORT:-80}`	`3000` / `80`
`UPLOAD_DIR`	app	File upload directory	`/app/uploads`
`INCLUDE_CHAT`	postgres	Apply chat schema on init (`true`/`false`)	`true`

Starting the Stack

docker compose up -d

On first run, PostgreSQL applies the schema via docker/init-db.sh. Subsequent starts reuse the persisted data volume.

Updating an Exported App

To update an existing deployment to a newer Alchemify version:

Back up the database — pg_dump before making any changes
Pull the latest code — git pull
Rebuild containers — docker compose build
Re-apply schema — psql -U dba alchemify -f apps/server/schema.sql
Restart — docker compose up -d

Always back up first. Schema changes are additive (new columns, new tables) but a backup protects against the unexpected.

Post-Restore Verification

After restoring an export, verify these basics:

Login — sign in with an existing user account
Query — open a table and confirm rows appear
File access — view or download an uploaded file
Permissions — confirm staff users see only what their role allows