Choose the application database

The application database holds phaf's transactions, message history, tracked headers, application logs and audit trail. phaf supports SQLite (the default) and Microsoft SQL Server. Users, settings, workspaces and the encryption key always stay in the data folder, whichever database you choose.

How phaf chooses the database

• The database is set in the appDb section of C:\phaf\app\config.json. Without that section, phaf uses SQLite.
• On every start phaf creates any missing tables itself. You only provide an empty database.
• The console shows the database in use, tests it and can migrate data between databases in Settings › Advanced › Database. It does not save the connection: you always change it in config.json and restart the phaf service.
• If the database is unavailable — server stopped, wrong password — phaf still starts and you can sign in, but it does not process or receive files until the database is back. A banner at the top of every page says why, and phaf retries every 30 seconds.

SQLite (default)

Nothing to install. phaf creates the database file C:\phaf\data\db\phaf.db on the first start. To use another file name or folder, add:

{
  "appDirectory": "C:/phaf/data",
  "host": "127.0.0.1",
  "port": 8181,
  "appDb": { "provider": "sqlite", "file": "db/phaf.db" }
}

A relative file is relative to the data folder.

SQL Server

Supported: SQL Server 2016 or later, including Express, on the same server or another one. phaf connects over TCP with a SQL Server login or a Windows account.

1. Prepare SQL Server

• TCP/IP enabled — in SQL Server Configuration Manager › SQL Server Network Configuration › Protocols. SQL Server Express has it disabled by default. Restart the SQL Server service after enabling it.
• Port open — TCP 1433 by default. A named instance uses a dynamic port unless you fix one; it then also needs the SQL Server Browser service (UDP 1434).
• Authentication — to use a SQL Server login, the instance must allow "SQL Server and Windows Authentication mode".

2. Create the database and the login

Ask your DBA to run this script, with a strong password of your own. phaf needs to read and write its tables and to create them (db_ddladmin) — only in its own database.

CREATE DATABASE phaf;
GO
CREATE LOGIN phaf_app WITH PASSWORD = '<a strong password>', CHECK_POLICY = ON;
GO
USE phaf;
CREATE USER phaf_app FOR LOGIN phaf_app;
ALTER ROLE db_datareader ADD MEMBER phaf_app;
ALTER ROLE db_datawriter ADD MEMBER phaf_app;
ALTER ROLE db_ddladmin  ADD MEMBER phaf_app;
GO

To keep phaf's tables apart from other objects, give them their own schema — phaf creates it if it does not exist, or you can create it first:

USE phaf;
CREATE SCHEMA phaf AUTHORIZATION dbo;

3. Encrypt the password

Never leave the database password in clear text in config.json. phaf encrypts it with the key of this installation:

1. Sign in to phaf as administrator (it still uses SQLite).
2. Go to Settings › Advanced and click Encrypt a password.
3. Type the password of phaf_app. phaf shows a value that starts with ENC:v1:. Copy it.
4. Keep the value for the next step.

The encrypted value only works on this installation, because it depends on its phaf.master_key.

4. Add the appDb section

{
  "appDirectory": "C:/phaf/data",
  "host": "127.0.0.1",
  "port": 8181,
  "appDb": {
    "provider": "sqlserver",
    "server": "sql01.your-company.local",
    "port": 1433,
    "database": "phaf",
    "schema": "dbo",
    "user": "phaf_app",
    "password": "ENC:v1:…paste the encrypted value…",
    "encrypt": true,
    "trustServerCertificate": false
  }
}

Connection encryption

Windows account instead of a SQL login

To connect with a domain account, add authScheme and domain, and use that account's name and encrypted password. The account needs the same database roles as above.

"appDb": {
  "provider": "sqlserver",
  "server": "sql01.your-company.local",
  "database": "phaf",
  "authScheme": "ntlm",
  "domain": "YOURDOMAIN",
  "user": "svc-phaf",
  "password": "ENC:v1:…",
  "encrypt": true,
  "trustServerCertificate": false
}

5. Restart and check

Restart-Service (Get-Service | Where-Object DisplayName -like 'phaf*').Name

Then open Settings › Advanced › Database and click Test: phaf shows the SQL Server version and the number of rows in each table.

Moving existing data to SQL Server

If phaf already ran on SQLite and you want to keep its history, use the migration assistant instead of starting empty. It copies the six application tables and never changes the current database.

1. Settings › Advanced › Database › Migrate to another database.
2. Safe Mode — phaf stops sending, processing and receiving, and waits for running work to finish.
3. Destination — enter the SQL Server details and click Test connection. This creates the tables in the destination.
4. Migration — phaf copies the tables page by page, with progress for each one.
5. Done — phaf shows the appDb block, with the password already encrypted. Paste it into config.json and restart the phaf service.

To cancel, click Leave Safe Mode: phaf goes back to work on the current database.

Environment variables (optional)

Instead of config.json, the database can come from system environment variables, which take priority: PHAF_APPDB_PROVIDER, PHAF_APPDB_SERVER, PHAF_APPDB_PORT, PHAF_APPDB_DATABASE, PHAF_APPDB_SCHEMA, PHAF_APPDB_USER, PHAF_APPDB_PASSWORD, PHAF_APPDB_AUTHSCHEME, PHAF_APPDB_DOMAIN and PHAF_APPDB_FILE. If you use them, put the ENC:v1: value in the password variable, not the clear password.

MySQL and MariaDB

phaf cannot use MySQL or MariaDB as its application database in this version. Use SQLite or SQL Server. If MySQL is a requirement for you, tell us through a feature request — we will include it in the roadmap and update this page when it is available.