> 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

JSONAir uses a MySQL-compatible database to store two things: **API keys** (used to authenticate agents) and **configurations** (the data agents retrieve). 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'`).

***

## 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 two tables: `keys` and `configurations`.

***

## 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                                               |

***

## 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.

***

## 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 |