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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.k9.io/key9-identity/jsonair/2-install/2.4-database-setup.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.