> For the complete documentation index, see [llms.txt](https://docs.k9.io/key9-identity/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.k9.io/key9-identity/jsonair/2-install/2.4-database-setup.md).

# 2.4 Database Setup

How to create and configure the JSONAir database.

JSONAir uses a MySQL-compatible database to store three things: **API keys** (used to authenticate agents), **configurations** (the data agents retrieve), and, if you run the optional write-only API, **write keys** (used to authenticate services that change configurations). This page walks through creating the database, importing the schema, and adding your first API key.

***

## 1. Create the Database and User

Log into your MySQL or MariaDB server as an administrator and run the following, substituting your own values for the username and password:

```sql
CREATE DATABASE jsonair CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE USER 'jsonair'@'%' IDENTIFIED BY 'your-strong-password';

GRANT SELECT, INSERT, UPDATE, DELETE ON jsonair.* TO 'jsonair'@'%';

FLUSH PRIVILEGES;
```

> **Tip:** Restrict the `'jsonair'@'%'` host to the specific IP address of your JSONAir server in production (e.g., `'jsonair'@'10.0.0.5'`).

### Least privilege (recommended for production)

The grant above is the simplest setup. Because each JSONAir program does a different job, each can use its own database user with only the access it needs. The read API (`jsonair`) never writes configuration data, so its user can be limited to:

```sql
CREATE USER 'jsonair_read'@'10.0.0.5' IDENTIFIED BY 'a-long-password';

GRANT SELECT ON jsonair.keys TO 'jsonair_read'@'10.0.0.5';
GRANT SELECT ON jsonair.configurations TO 'jsonair_read'@'10.0.0.5';
GRANT UPDATE (last_login) ON jsonair.keys TO 'jsonair_read'@'10.0.0.5';
```

`jsonair-admin` needs `SELECT, INSERT, UPDATE, DELETE` on `configurations` and `SELECT` on `keys`. `jsonair-write` has its own, narrower grants; see [2.9 The Write API](https://github.com/k9io/jsonair/tree/main/docs/2.9-write-api.md).

***

## 2. Import the Schema

The repository includes a ready-to-use schema file at `sql/jsonair.sql`. Import it into the database you just created:

```bash
mysql -u jsonair -p jsonair < sql/jsonair.sql
```

This creates three tables: `keys`, `configurations` and `write_keys`.

> **Warning:** `sql/jsonair.sql` is written for a **new** database. It drops and recreates `keys` and `configurations`, which **deletes their data**. To upgrade an existing database, see [Upgrading an Existing Database](#7-upgrading-an-existing-database) below.

***

## 3. Table Overview

### `keys`

Stores API keys used by agents to authenticate with the JSONAir server.

| Column       | Type         | Description                              |
| ------------ | ------------ | ---------------------------------------- |
| `id`         | int          | Auto-increment primary key               |
| `uuid`       | varchar(36)  | Unique identifier for this key (UUID v4) |
| `name`       | varchar(64)  | Human-readable label for the key         |
| `token`      | varchar(255) | HMAC-SHA256 hash of the plain-text PAT   |
| `created`    | timestamp    | When the key was created                 |
| `last_login` | timestamp    | Last successful authentication           |

### `configurations`

Stores the configuration data that agents retrieve.

| Column        | Type         | Description                                                                    |
| ------------- | ------------ | ------------------------------------------------------------------------------ |
| `id`          | int          | Auto-increment primary key                                                     |
| `uuid`        | varchar(36)  | The key UUID this configuration belongs to                                     |
| `type`        | varchar(128) | Configuration type (e.g. `suricata`, `nginx`)                                  |
| `name`        | varchar(127) | Configuration name (e.g. `prod.yaml`)                                          |
| `reload`      | varchar(255) | Reload trigger key/value                                                       |
| `debug`       | varchar(128) | Debug level or flag                                                            |
| `config_data` | mediumtext   | The configuration data, AES-256-GCM encrypted and Base64-encoded (up to 16 MB) |
| `created`     | timestamp    | When the record was created                                                    |
| `updated`     | timestamp    | When the record was last updated                                               |

The combination of `uuid`, `type` and `name` is **unique** (the `idx_uuid_type_name` key). There can only ever be one configuration for a given key, type and name. Trying to insert a second one fails with a duplicate-entry error (MySQL error 1062).

### `write_keys`

Stores the keys used by the optional write-only API (`jsonair-write`). These are completely separate from `keys`: a read key is never accepted by the write API, and a write key is never accepted by the read API. See [2.9 The Write API](https://github.com/k9io/jsonair/tree/main/docs/2.9-write-api.md).

| Column          | Type         | Description                                                             |
| --------------- | ------------ | ----------------------------------------------------------------------- |
| `id`            | int          | Auto-increment primary key                                              |
| `uuid`          | varchar(36)  | The `uuid` of the configurations this key may write                     |
| `name`          | varchar(64)  | Human-readable label for the key                                        |
| `token`         | varchar(255) | HMAC-SHA256 hash of the plain-text PAT, using `WRITE_TOKEN_HMAC_SECRET` |
| `allowed_types` | varchar(512) | Comma separated list of `type` values this key may write (`*` for any)  |
| `allowed_names` | varchar(512) | Comma separated list of `name` values this key may write (`*` for any)  |
| `created`       | timestamp    | When the key was created                                                |
| `last_login`    | timestamp    | Last successful authentication                                          |

***

## 4. Adding an API Key

JSONAir never stores plain-text tokens. The `token` column holds an **HMAC-SHA256 hash** of the plain-text PAT, computed using the server's `TOKEN_HMAC_SECRET`. This means even if the database is compromised, the actual tokens cannot be recovered.

### Step 1 — Choose a plain-text PAT

This is the token value that will be placed in the agent's `JSONAIR_PAT` environment variable. It should be a long, random string. For example:

```bash
openssl rand -hex 32
```

### Step 2 — Hash the token

Using the same `TOKEN_HMAC_SECRET` that your JSONAir server is configured with, compute the HMAC-SHA256 hash:

```bash
echo -n "YOURTOKEN" | openssl dgst -sha256 -hmac "YOUR_TOKEN_HMAC_SECRET"
```

Example output:

```
SHA2-256(stdin)= a3f1c2d4e5b6...
```

Take only the hex string after the `=` — that is the value you insert into the `token` column.

### Step 3 — Generate a UUID

```bash
uuidgen | tr '[:upper:]' '[:lower:]'
```

### Step 4 — Insert the key

```sql
INSERT INTO `keys` (`uuid`, `name`, `token`, `created`, `last_login`)
VALUES (
  'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
  'My Agent Key',
  'a3f1c2d4e5b6...',
  NOW(),
  NOW()
);
```

***

## 5. Adding a Configuration

Configuration data is **encrypted at rest** using AES-256-GCM. Before inserting a row, you must Base64-encode the raw configuration and then encrypt it using the `jsonair-encrypt` tool. The server will transparently decrypt it when an agent requests it.

See [2.7 Encrypting Configuration Data](/key9-identity/jsonair/2-install/2.7-encrypting-config-data.md) for full detail on the encryption tool and key management.

### Step 1 — Base64-encode your configuration

```bash
base64 -i /path/to/your/config.yaml
```

Or inline for a small value:

```bash
echo -n '{"key":"value"}' | base64
```

### Step 2 — Encrypt the Base64 output

Pipe the Base64 string into `jsonair-encrypt` with your `CONFIG_ENCRYPT_SECRET` set:

```bash
echo -n "base64encodedcontenthere" | CONFIG_ENCRYPT_SECRET=your-secret ./jsonair-encrypt
```

The output is the encrypted value ready for the database.

### Step 3 — Insert the configuration

```sql
INSERT INTO `configurations` (`uuid`, `type`, `name`, `reload`, `debug`, `config_data`, `created`, `updated`)
VALUES (
  'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
  'myapp',
  'prod.yaml',
  'RELOADKEY',
  'INFO',
  '<output from jsonair-encrypt>',
  NOW(),
  NOW()
);
```

The `uuid` must match the `uuid` of the API key that should be allowed to retrieve this configuration.

> **Other ways to add configurations:** `jsonair-admin` ([2.8](/key9-identity/jsonair/2-install/2.8-admin-web-interface.md)) does the base64 and encryption steps for you from a web form, and `jsonair-write` ([2.9](https://github.com/k9io/jsonair/tree/main/docs/2.9-write-api.md)) does the same for other services over an API. You only need to run `jsonair-encrypt` and `INSERT` by hand if you are not using either of them.

***

## 6. Environment Variables

Once the database is ready, configure the JSONAir server with the following environment variables:

| Variable                | Description                                                                             |
| ----------------------- | --------------------------------------------------------------------------------------- |
| `MYSQL_USERNAME`        | Database user                                                                           |
| `MYSQL_PASSWORD`        | Database password                                                                       |
| `MYSQL_DATABASE`        | Database name                                                                           |
| `MYSQL_HOST`            | Database hostname or IP                                                                 |
| `MYSQL_PORT`            | Database port (typically `3306`)                                                        |
| `MYSQL_TLS`             | Set to `true` to enable TLS for the DB connection                                       |
| `MYSQL_TLS_SKIP_VERIFY` | Set to `true` to disable certificate verification (not recommended for production)      |
| `TOKEN_HMAC_SECRET`     | The secret used to HMAC-SHA256 hash PATs — must match what was used when inserting keys |

`MYSQL_PORT` is used to build the connection address, so a non-default port works as expected. (Older versions ignored `MYSQL_PORT` in `jsonair`; a port written directly into `MYSQL_HOST`, such as `db.internal:3307`, is still honored and takes precedence.)

***

## 7. Upgrading an Existing Database

Do **not** re-import `sql/jsonair.sql` into a database that already holds data. Apply only what you need:

**Add the unique key on `configurations`** (required by `jsonair-write`, and it prevents duplicate configurations). First look for existing duplicates:

```sql
SELECT `uuid`,`type`,`name`,COUNT(*) AS n FROM `configurations`
  GROUP BY `uuid`,`type`,`name` HAVING n > 1;
```

Remove or rename any rows it returns (the read API returns whichever duplicate MySQL finds first), then:

```sql
ALTER TABLE `configurations`
  ADD UNIQUE KEY `idx_uuid_type_name` (`uuid`,`type`,`name`);
```

**Add the `write_keys` table** (only if you will run `jsonair-write`): run the `CREATE TABLE IF NOT EXISTS` write\_keys\`\` statement from `sql/jsonair.sql`. It does not touch existing tables.