# PostgreSQL

Source: https://docs.interop.io/manager/databases/postgresql/index.html

## Overview

**io.Manager** supports connecting to PostgreSQL databases. The following sections outline the steps and requirements for configuring **io.Manager** to work with PostgreSQL databases and the options for initializing or migrating the database schema.

> ℹ️ *For a complete example of connecting **io.Manager** to a PostgreSQL database, see the [PostgreSQL Database](https://github.com/InteropIO/manager-examples/tree/main/db-postgresql) example on GitHub.*

## Supported Compatible Engines

In addition to PostgreSQL itself, **io.Manager** provides support for working with PostgreSQL wire-compatible engines. The same connection settings and configuration options apply as when using PostgreSQL.

| Engine | Supported Versions |
|--------|--------------------|
| [YugabyteDB](https://www.yugabyte.com/) | v2025.2 *Available since **io.Manager** Server 2.1.* |

## Connecting to PostgreSQL Databases

To enable **io.Manager** to connect to a PostgreSQL database, you must either set the necessary environment variables, or provide the required settings via the configuration object for initializing the **io.Manager** Server.

Depending on the [deployment](https://docs.interop.io/manager/deployment/index.md) approach you have chosen, you have the following options:

- If you are using the [basic deployment scenario](https://github.com/InteropIO/manager-examples/tree/main/manager-template/1-basic) from the [template repository](https://docs.interop.io/manager/deployment/index.md#template_repository) approach, you must set properly the necessary environment variables.

- If you are using the [NPM packages](https://docs.interop.io/manager/deployment/index.md#npm_packages) for deployment, or the [advanced deployment scenario](https://github.com/InteropIO/manager-examples/tree/main/manager-template/2-advanced) from the template repository approach, you must provide the necessary settings via the configuration object for initializing the **io.Manager** Server.

The following sections provide examples of both options.

### Environment Variables

To configure **io.Manager** to connect to a PostgreSQL database, you must register all of the following environment variables with the proper values. The `API_STORE_TYPE` environment variable must be set to `postgresql`. All other variables must be set with values according to your specific environment:

| Environment Variable | Description |
|----------------------|-------------|
| `API_STORE_TYPE` | **Required.** Type of the database. Must be set to `postgresql`. |
| `API_STORE_POSTGRESQL` | **Required.** PostgreSQL connection URL. |
| `API_STORE_POSTGRESQL_CREATE_DB` | If `true` (default), will automatically create and initialize the database and the tables necessary for **io.Manager**. Set to `false` if you want to do this separately. For more details on database schema creation and migration, see the [Database Schema](#database_schema) section. |
| `API_STORE_POSTGRESQL_DB_NAME` | **Required.** Database name for the PostgreSQL connection URL. |
| `API_STORE_POSTGRESQL_MIGRATION_RETRIES` | How many times store initialization - creating the database, creating the schema and running the migration scripts - is retried after a transient failure, such as another server instance initializing the same database concurrently. The error of the final attempt is propagated. Defaults to `3`. *Available since **io.Manager** 4.0.* |
| `API_STORE_POSTGRESQL_MIGRATION_RETRY_DELAY_MS` | The delay in milliseconds between store initialization retry attempts. Defaults to `1000`. *Available since **io.Manager** 4.0.* |
| `API_STORE_POSTGRESQL_NATIVE_PG_DRIVER` | If `true`, will use the [`pg-native`](https://www.npmjs.com/package/pg-native) implementation. Defaults to `false`. <br> ⚠️ *Note that `pg-native` isn't a dependency of the `@interopio/manager` package and must be installed separately.* |
| `API_STORE_POSTGRESQL_SCHEMA_NAME` | PostgreSQL schema name. Defaults to `public`. |
| `API_STORE_POSTGRESQL_TRANSACTION_ISOLATION` | The isolation level for the database transactions the server opens. Accepts `database-default`, `read-committed`, `repeatable-read`, or `serializable` as a value. `database-default` leaves the database's own default isolation level in effect, including a default customized with `default_transaction_isolation`. Defaults to `database-default`. *Available since **io.Manager** 4.0.* |
| `API_STORE_POSTGRESQL_TRANSACTION_RETRIES` | How many times a database transaction, or a standalone read, is re-run after a transient failure. Set to `0` to disable the retry. Defaults to `3`. *Available since **io.Manager** 4.0.* |
| `API_STORE_POSTGRESQL_TRANSACTION_RETRY_DELAY_MS` | The base delay in milliseconds between retry attempts. The actual delay is randomized and increases with each retry. Defaults to `100`. *Available since **io.Manager** 4.0.* |

Example settings:

```cmd
API_STORE_TYPE=postgresql
API_STORE_POSTGRESQL=postgresql://my_user:password@localhost:5432
API_STORE_POSTGRESQL_DB_NAME=my_db
API_STORE_POSTGRESQL_SCHEMA_NAME=public
API_STORE_POSTGRESQL_CREATE_DB=true
API_STORE_POSTGRESQL_NATIVE_PG_DRIVER=false
```

> ℹ️ *For details on all available environment variables for configuring the **io.Manager** Server, see the [Configuration > Server](https://docs.interop.io/manager/configuration/server/index.md#environment_variables) section.*

### Configuration Object

To configure **io.Manager** to connect to a PostgreSQL database, you must provide the necessary settings when initializing the **io.Manager** Server. Use the `store` property of the optional `Config` object and provide a `PostgreSQLStoreConfig` object as its value.

The following example demonstrates configuring the connection to a PostgreSQL database when initializing the **io.Manager** Server:

```javascript
import { start } from "@interopio/manager";

// Configuration for the io.Manager Server.
const config = {
    name: "my-server",
    port: 4242,
    token: {
        secret: "my-secret"
    },
    // Configuration for connecting to a PostgreSQL database.
    store: {
        type: "postgresql",
        connection: "postgresql://my_user:password@localhost:5432",
        dbName: "my_db",
        schemaName: "my_schema"
    }
};

// Initializing the io.Manager Server.
const server = await start(config);
```

The `store` object has the following properties:

| Property | Type | Description |
|----------|------|-------------|
| `connection` | `string` | **Required.** PostgreSQL connection URL. <br> ⚠️ *Note that this property is required only if the `hosts` property isn't populated.* |
| `createDatabaseAndTables` | `boolean` | If `true` (default), will automatically create and initialize the database and the tables necessary for **io.Manager**. Set to `false` if you want to do this separately. For more details on database schema creation and migration, see the [Database Schema](#database_schema) section. |
| `dbName` | `string` | Database name for the PostgreSQL connection URL. Defaults to `"test"`. |
| `hosts` | `object[]` | List of PostgreSQL hosts to which to connect. Hosts will be tried in the order they are provided. <br> ⚠️ *Note that if this property is populated, the connection-related properties of the `store` object will be ignored in favor of the host definitions, and automatic schema creation or migration won't be performed. The `transactionRetries`, `transactionRetryDelayMs`, and `transactionIsolation` properties still apply.* |
| `migrationRetries` | `number` | How many times store initialization - creating the database, creating the schema and running the migration scripts - is retried after a transient failure, such as another server instance initializing the same database concurrently. The error of the final attempt is propagated. Defaults to `3`. *Available since **io.Manager** 4.0.* |
| `migrationRetryDelayMs` | `number` | The delay in milliseconds between store initialization retry attempts. Defaults to `1000`. *Available since **io.Manager** 4.0.* |
| `native` | `boolean` | If `true`, will use the [`pg-native`](https://www.npmjs.com/package/pg-native) implementation. Defaults to `false`. <br> ⚠️ *Note that `pg-native` isn't a dependency of the `@interopio/manager` package and must be installed separately.* |
| `poolConfig` | `object` | [Knex.js](https://knexjs.org/) pool configuration. For more details, see the [official Knex.js documentation](https://knexjs.org/guide/#pool). |
| `schemaName` | `string` | PostgreSQL schema name. Defaults to `"public"`. |
| `transactionIsolation` | `"database-default"` \| `"read-committed"` \| `"repeatable-read"` \| `"serializable"` | The isolation level for the database transactions the server opens. `"database-default"` leaves the database's own default isolation level in effect, including a default customized with `default_transaction_isolation`. Defaults to `"database-default"`. *Available since **io.Manager** 4.0.* |
| `transactionRetries` | `number` | How many times a database transaction, or a standalone read, is re-run after a transient failure. Set to `0` to disable the retry. Defaults to `3`. *Available since **io.Manager** 4.0.* |
| `transactionRetryDelayMs` | `number` | The base delay in milliseconds between retry attempts. The actual delay is randomized and increases with each retry. Defaults to `100`. *Available since **io.Manager** 4.0.* |
| `type` | `"postgresql"` | **Required.** Type of the data store. Must be set to `"postgresql"` when using a PostgreSQL database. |

The `hosts` array accepts objects of type `PostgreSQLHostConfig`. Each object has the following properties:

| Property | Type | Description |
|----------|------|-------------|
| `connection` | `string` | **Required.** PostgreSQL connection URL. |
| `dbName` | `string` | Database name for the PostgreSQL connection URL. Defaults to `"test"`. |
| `failoverTimeout` | `number` | Interval in milliseconds to wait for a response from the PostgreSQL host before proceeding to the next one. Defaults to `2000`. |
| `isReadOnly` | `boolean` | If `true`, **io.Manager** won't attempt to execute write operations on this host. |
| `native` | `boolean` | If `true`, will use the [`pg-native`](https://www.npmjs.com/package/pg-native) implementation. Defaults to `false`. <br> ⚠️ *Note that `pg-native` isn't a dependency of the `@interopio/manager` package and must be installed separately.* |
| `poolConfig` | `object` | [Knex.js](https://knexjs.org/) pool configuration. For more details, see the [official Knex.js documentation](https://knexjs.org/guide/#pool). |
| `schemaName` | `string` | PostgreSQL schema name. Defaults to `"public"`. |

> ℹ️ *For details on all available properties for configuring the **io.Manager** Server, see the [Configuration > Server](https://docs.interop.io/manager/configuration/server/index.md#configuration_object) section.*

#### Automatic Failover

**io.Manager** supports automatic failover for PostgreSQL databases. To specify multiple PostgreSQL hosts to which to connect, use the `hosts` property of the `store` object in the configuration for initializing the **io.Manager** Server. The `hosts` property accepts as a value a list of objects each describing a PostgreSQL host.

When using multiple hosts, **io.Manager** will use the first host to which it can establish a connection. Hosts are tried in the order they are provided. To prevent **io.Manager** from attempting to execute write operations on a host, set the `isReadOnly` property of the host definition object to `true`. This will cause **io.Manager** to return an error response instead.

The following example demonstrates configuring **io.Manager** Server to use multiple PostgreSQL hosts:

```javascript
import { start } from "@interopio/manager";

// Configuration for the io.Manager Server.
const config = {
    name: "my-server",
    port: 4242,
    token: {
        secret: "my-secret"
    },
    // Configuration for connecting to a PostgreSQL database.
    store: {
        type: "postgresql",
        // Specifying multiple PostgreSQL hosts to which to connect.
        hosts: [
            {
                connection: "postgresql://my_user:password@localhost:5432",
                dbName: "my_db",
                schemaName: "my_schema"
            },
            {
                connection: "postgresql://my_user:password@localhost:5433",
                dbName: "my_db",
                schemaName: "my_schema",
                // io.Manager won't attempt to execute write operations on this host.
                isReadOnly: true
            }
        ]
    }
};

// Initializing the io.Manager Server.
const server = await start(config);
```

> ⚠️ *Note that if the `hosts` property is populated, the connection-related properties of the `store` object will be ignored in favor of the host definitions. The `transactionRetries`, `transactionRetryDelayMs`, and `transactionIsolation` properties still apply.*

> ⚠️ *Note that automatic schema creation or migration isn't supported when using a configuration for multiple PostgreSQL hosts.*

> ⚠️ *Note that the automatic failover incurs a small performance cost even when all hosts are up and running: each request runs one lightweight health check to select the host, which keeps both the failover and the recovery (switching back to the primary database instance when it's available) seamless and instant. For requests that modify data, the health check is folded into opening the database transaction, so no additional query is run.*

## Database Schema

The **io.Manager** schema for PostgreSQL databases can be created or migrated in two ways: automatically, when starting the **io.Manager** Server, or by using a schema creation script.

### Automatic Creation & Migration

**io.Manager** provides an automated functionality for creating or migrating the database schema. This functionality uses [Knex.js migrations](https://knexjs.org/guide/migrations.html) internally. The creation or migration of the database schema is executed on startup of the server, only if required.

> ⚠️ *Note that automatic schema creation or migration isn't supported when using a configuration for [multiple PostgreSQL hosts](#connecting_to_postgresql_databases-configuration_object-automatic_failover).*

To enable **io.Manager** to use this automated functionality, the user configured in **io.Manager** for connecting to the database must meet at least one of the following requirements:

- the user is a `SUPERUSER`;
- the user has the `CREATEDB` permission;

### Schema Creation Script

If you don't want to use the automated functionality of **io.Manager** for creating and migrating the database schema, you can use the SQL script provided in this section. Execute the script before initializing the **io.Manager** Server in order to create the necessary database and tables.

#### Prerequisites

Using this script means that you must disable the automated functionality of **io.Manager** for creating or migrating the database schema.

If you are using the [basic deployment scenario](https://github.com/InteropIO/manager-examples/tree/main/manager-template/1-basic) from the [template repository](https://docs.interop.io/manager/deployment/index.md#template_repository) approach, you must set the `API_STORE_POSTGRESQL_CREATE_DB` environment variable to `false`:

```cmd
API_STORE_POSTGRESQL_CREATE_DB=false
```

If you are using the [NPM packages](https://docs.interop.io/manager/deployment/index.md#npm_packages) for deployment, or the [advanced deployment scenario](https://github.com/InteropIO/manager-examples/tree/main/manager-template/2-advanced) from the template repository approach, you must set the `createDatabaseAndTables` property to `false` in the optional `Config` object for initializing the **io.Manager** Server:

```javascript
import { start } from "@interopio/manager";

const config = {
    name: "my-server",
    port: 4242,
    token: {
        secret: "my-secret"
    },
    store: {
        type: "postgresql",
        connection: "postgresql://my_user:password@localhost:5432",
        dbName: "my_db",
        schemaName: "my_schema",
        // Disable the automated functionality for creating the database.
        createDatabaseAndTables: false
    }
};

const server = await start(config);
```

#### SQL Script for Creating the Database

> ⚠️ *Note that `my_schema` and `my_user` in the script must already be created. Replace `my_schema` and `my_user` with the actual schema and user names where necessary.*

```sql
-- io.Manager schema initialization script for PostgreSQL databases.

SET search_path = my_schema;

-- A group of users. Groups are used to grant access to applications and layouts.
CREATE TABLE IF NOT EXISTS groups
(
    groups_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    name VARCHAR(255) NOT NULL, -- Name of the group. Unique.
    description TEXT NULL, -- Description of the group.
    "expandsTo" VARCHAR(255)[] NOT NULL DEFAULT '{}'::varchar[], -- Groups this group also grants membership of. Empty when it expands into no others. Array of strings.
    CONSTRAINT groups_name_unique UNIQUE (name)
);

-- A single row of change timestamps. An io.Connect platform polls these to learn whether it needs to re-fetch a kind of data.
CREATE TABLE IF NOT EXISTS last_updated
(
    last_updated_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    applications BIGINT NOT NULL, -- Time any application last changed. Epoch milliseconds (UTC).
    layouts BIGINT NOT NULL, -- Time any layout last changed. Epoch milliseconds (UTC).
    groups BIGINT NOT NULL, -- Unused. Epoch milliseconds (UTC).
    commands BIGINT NOT NULL, -- Time any command last changed. Epoch milliseconds (UTC).
    configs BIGINT NOT NULL, -- Time any system config last changed. Epoch milliseconds (UTC).
    others JSON NULL -- Further change timestamps that do not have a column of their own. JSON.
);

-- Layouts saved by an io.Connect platform.
CREATE TABLE IF NOT EXISTS layouts
(
    layouts_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the layout. Unique.
    type VARCHAR(255) NOT NULL, -- Kind of layout, for example 'Global' or 'Workspace'.
    name VARCHAR(255) NOT NULL, -- Name of the layout. Unique per type per owner.
    owner VARCHAR(255) NULL, -- User the layout is private to. The literal '*' marks a common layout, whose visibility is then decided by the public and accessList columns.
    public BOOLEAN NOT NULL, -- Whether every user can read the layout. Applies only to common layouts.
    disabled BOOLEAN NOT NULL, -- Whether the layout is withheld from all users.
    "accessList" VARCHAR(255)[] NULL, -- Groups allowed to read the layout when it is common and not public. Array of strings.
    definition JSON NOT NULL, -- The layout payload as saved by the io.Connect platform. JSON.
    "createdBy" VARCHAR(255) NOT NULL, -- User that created the layout.
    "createdOn" BIGINT NOT NULL, -- Creation time. Epoch milliseconds (UTC).
    "lastModifiedBy" VARCHAR(255) NULL, -- User that last modified the layout.
    "lastModifiedOn" BIGINT NULL, -- Time of the last modification. Epoch milliseconds (UTC).
    "default" BOOLEAN NULL, -- Whether the layout is restored when an io.Connect platform starts.
    migrated BOOLEAN NULL, -- Whether this layout has already been migrated to the layouts_advanced table.
    CONSTRAINT layouts_id_unique UNIQUE (id),
    CONSTRAINT layouts__name__type__owner__unique UNIQUE ("name", "type", "owner")
);

-- Resolving a layout owner: case-folded for when username_case_sensitive is false, exact for when it is true.
CREATE INDEX IF NOT EXISTS layouts__owner__lower ON layouts (lower("owner"));
CREATE INDEX IF NOT EXISTS layouts__owner ON layouts ("owner");

-- A machine an io.Connect platform has run on. Referenced by sessions.
CREATE TABLE IF NOT EXISTS machines
(
    machines_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the machine. Unique.
    "user" VARCHAR(255) NOT NULL, -- User the machine is associated with.
    os JSON NULL, -- Operating-system details reported by the machine. JSON.
    name VARCHAR(255) NULL, -- Host name of the machine.
    displays JSON NULL, -- Displays attached to the machine. JSON.
    browser JSON NULL, -- Browser details. Populated for io.Connect Browser sessions only. JSON.
    CONSTRAINT machines_id_unique UNIQUE (id)
);

-- Resolving a machine's user: case-folded for when username_case_sensitive is false, exact for when it is true.
CREATE INDEX IF NOT EXISTS machines__user__lower ON machines (lower("user"));
CREATE INDEX IF NOT EXISTS machines__user ON machines ("user");

-- Per-user, per-application preferences. One row per application and user.
CREATE TABLE IF NOT EXISTS prefs
(
    prefs_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the preference. Unique.
    app VARCHAR(255) NOT NULL, -- Application the preference belongs to.
    "user" VARCHAR(255) NOT NULL, -- User the preference belongs to.
    data JSON NOT NULL, -- The preference payload. Arbitrary data. JSON.
    "lastUpdate" BIGINT NOT NULL, -- Time of the last update. Epoch milliseconds (UTC).
    CONSTRAINT prefs_id_unique UNIQUE (id),
    CONSTRAINT prefs__app__user__unique UNIQUE ("app", "user")
);

-- Resolving a preference's user: case-folded for when username_case_sensitive is false, exact for when it is true.
CREATE INDEX IF NOT EXISTS prefs__user__lower__app ON prefs (lower("user"), app);
CREATE INDEX IF NOT EXISTS prefs__user ON prefs ("user");

-- A run of an io.Connect platform by one user on one machine.
CREATE TABLE IF NOT EXISTS sessions
(
    sessions_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the session. Unique.
    machine VARCHAR(255) NOT NULL, -- Machine the session ran on.
    start BIGINT NOT NULL, -- Time the session started. Epoch milliseconds (UTC).
    "user" VARCHAR(255) NOT NULL, -- User that opened the session.
    glue JSON NOT NULL, -- io.Connect platform details reported for the session. JSON.
    "lastDataFetch" BIGINT NULL, -- Time the client last requested data from the server. Used to purge inactive sessions. Epoch milliseconds (UTC).
    "end" BIGINT NULL, -- Time the session was closed. Epoch milliseconds (UTC).
    closed BOOLEAN NULL, -- Whether the session has been closed.
    "closeReason" VARCHAR(255) NULL, -- Why the session was closed. 'clean' when an administrator closed it.
    product VARCHAR(255) NOT NULL, -- The io.Connect product that opened the session.
    "productVersion" VARCHAR(255) NOT NULL, -- Version of that product. Determines which commands the session supports.
    CONSTRAINT sessions_id_unique UNIQUE (id)
);

-- A user known to io.Manager.
CREATE TABLE IF NOT EXISTS users
(
    users_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Identifier of the user. Unique.
    email VARCHAR(255) NULL, -- Email address of the user.
    password VARCHAR(255) NULL, -- A bcrypt hash of the user's password. Populated only when basic authentication is enabled.
    apps VARCHAR(255)[] NOT NULL, -- Applications granted directly to the user. Array of strings.
    groups VARCHAR(255)[] NOT NULL, -- Groups the user belongs to. Array of strings.
    "lastUpdated" BIGINT NULL, -- Time the user last changed. Epoch milliseconds (UTC).
    "firstName" VARCHAR(255) NULL, -- First name of the user.
    "lastName" VARCHAR(255) NULL, -- Last name of the user.
    layouts JSON ARRAY NULL, -- Layouts granted directly to the user. Array of JSON values.
    others JSONB NULL, -- Per-user timestamps that do not have a column of their own. JSON.
    CONSTRAINT users_id_unique UNIQUE (id)
);

-- Resolving a username: case-folded for when username_case_sensitive is false, exact for when it is true.
CREATE INDEX IF NOT EXISTS users__id__lower ON users (lower(id));

-- System configuration served to an io.Connect platform. The best-matching row for a platform version, group and user wins.
CREATE TABLE IF NOT EXISTS "glue42SystemConfig"
(
    "glue42SystemConfig_id" SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    identifier TEXT NOT NULL, -- The platform version, group and user this row applies to. Either of the group and user may be '*' to match any. JSON object, stored as text. Unique.
    configs JSON NOT NULL, -- The configuration files this row supplies, each keyed by file name. JSON.
    weight NUMERIC(8, 6) NULL, -- Precedence of this row when several rows match the same platform. Higher wins.
    CONSTRAINT glue42SystemConfig_identifier_unique UNIQUE (identifier)
);

-- A feedback report submitted by a user from an io.Connect platform.
CREATE TABLE IF NOT EXISTS feedback
(
    feedback_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the feedback report. Unique.
    date BIGINT NOT NULL, -- Time the report was filed. Epoch milliseconds (UTC).
    "user" VARCHAR(255) NOT NULL, -- User that filed the report.
    session VARCHAR(255) NOT NULL, -- Session the report was filed from.
    description TEXT NOT NULL, -- Free-text description entered by the user.
    attachment TEXT NOT NULL, -- File name of the attachment. The bytes are held in the blobs table.
    reviewed BOOLEAN NULL, -- Whether an administrator has reviewed the report.
    comment TEXT NULL, -- Review notes left by an administrator.
    CONSTRAINT feedback_id_unique UNIQUE (id)
);

-- A crash reported by an io.Connect platform.
CREATE TABLE IF NOT EXISTS crashes
(
    crashes_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the crash. Unique.
    date BIGINT NOT NULL, -- Time the report reached io.Manager. Epoch milliseconds (UTC).
    "user" VARCHAR(255) NULL, -- User of the session that produced the crash.
    info JSON NOT NULL, -- Crash details reported by the io.Connect platform. JSON.
    reviewed BOOLEAN NULL, -- Whether an administrator has reviewed the crash.
    comment TEXT NULL, -- Review notes left by an administrator.
    CONSTRAINT crashes_id_unique UNIQUE (id)
);

-- The commands one io.Connect product version supports, as reported by a session of that version.
CREATE TABLE IF NOT EXISTS commands_for_version
(
    commands_for_version_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    version VARCHAR(255) NOT NULL, -- Version of the io.Connect product.
    commands JSON NOT NULL, -- The supported commands and their parameters. JSON.
    product VARCHAR(255) NOT NULL, -- The io.Connect product. Unique together with the version.
    CONSTRAINT commands_for_version_product_version_unique UNIQUE (product, version)
);

-- A command sent to a running io.Connect platform, and its result.
CREATE TABLE IF NOT EXISTS commands
(
    commands_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the command. Unique.
    "user" VARCHAR(255) NOT NULL, -- User the command targets.
    status VARCHAR(255) NOT NULL, -- Progress of the command, for example 'Created' or 'Executed'.
    session VARCHAR(255) NOT NULL, -- Session the command targets.
    machine VARCHAR(255) NOT NULL, -- Machine the command targets.
    "createdBy" VARCHAR(255) NOT NULL, -- User that invoked the command.
    "createdAt" BIGINT NOT NULL, -- Time the command was invoked. Epoch milliseconds (UTC).
    command VARCHAR(255) NOT NULL, -- Kind of command, for example 'GetLogs' or 'SendFeedback'.
    "commandParams" JSON NULL, -- Parameters passed to the command. An empty object when the command takes none. JSON.
    "resultData" JSON NULL, -- The command result, or the file name when the result is a file held in the blobs table. JSON.
    "resultAt" BIGINT NULL, -- Time the command was executed on the target machine. Epoch milliseconds (UTC).
    "resultType" VARCHAR(255) NULL, -- Form of the result: 'JSON' or 'file'.
    "traceContext" JSON NULL, -- OpenTelemetry span context the command was invoked under. JSON.
    CONSTRAINT commands_id_unique UNIQUE (id)
);

-- An audit record of one operation performed through io.Manager.
CREATE TABLE IF NOT EXISTS audit
(
    audit_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the audit record. Unique.
    "user" VARCHAR(255) NULL, -- User that triggered the operation.
    parent VARCHAR(255) NULL, -- Audit record of the operation that caused this one, when it was not triggered directly.
    date BIGINT NOT NULL, -- Time of the operation. Epoch milliseconds (UTC).
    session VARCHAR(255) NULL, -- Session the operation was triggered from, when it came from an io.Connect platform.
    server VARCHAR(255) NULL, -- Unused.
    "entityType" VARCHAR(255) NOT NULL, -- Kind of entity the operation acted on, for example 'application' or 'layout'.
    "entityId" VARCHAR(255) NULL, -- Identifier of the entity the operation acted on.
    "entityDisplayName" VARCHAR(255) NULL, -- Display name of that entity.
    operation VARCHAR(255) NOT NULL, -- The operation performed, for example 'create' or 'delete'.
    "operationComment" VARCHAR(255) NULL, -- Note clarifying the operation, typically what it contributed to its parent operation.
    "newValue" JSON NULL, -- The entity after the operation. JSON.
    "oldValue" JSON NULL, -- The entity before the operation. JSON.
    request JSON NULL, -- The request that triggered the operation. JSON.
    response JSON NULL, -- Unused. JSON.
    CONSTRAINT audit_id_unique UNIQUE (id)
);

-- An application io.Manager serves to io.Connect platforms.
CREATE TABLE IF NOT EXISTS app
(
    app_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    name VARCHAR(255) NOT NULL, -- Name of the application. Unique.
    disabled BOOLEAN NOT NULL, -- Whether the application is withheld from all users.
    definition JSON NOT NULL, -- The application definition served to the io.Connect platform. JSON.
    public BOOLEAN NOT NULL, -- Whether every user can access the application.
    "accessList" VARCHAR(255)[] NULL, -- Groups allowed to access the application when it is not public. Array of strings.
    "createdBy" VARCHAR(255) NOT NULL, -- User that created the application.
    "createdOn" BIGINT NOT NULL, -- Creation time. Epoch milliseconds (UTC).
    "lastModifiedBy" VARCHAR(255) NULL, -- User that last modified the application.
    "lastModifiedOn" BIGINT NULL, -- Time of the last modification. Epoch milliseconds (UTC).
    CONSTRAINT app_name_unique UNIQUE (name)
);

-- Binary content held for another entity, such as a feedback attachment or a command result file.
CREATE TABLE IF NOT EXISTS blobs
(
    blobs_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    type VARCHAR(255) NOT NULL, -- Kind of entity the content belongs to, which together with the id locates it.
    id VARCHAR(255) NOT NULL, -- Identifier of the entity the content belongs to. Unique.
    "fileName" VARCHAR(255) NOT NULL, -- File name to serve the content under.
    data bytea NOT NULL, -- The content itself. Binary content.
    encoding VARCHAR(20) NULL, -- Encoding of the content: "raw" when it is the file's own bytes, "base64" when it is those bytes base64-encoded as text. Absent on content stored before the encoding was recorded.
    CONSTRAINT blobs_id_unique UNIQUE (id)
);

-- Layouts saved by an io.Connect platform, with per-entity access control.
CREATE TABLE IF NOT EXISTS layouts_advanced
(
    layouts_advanced_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    id VARCHAR(255) NOT NULL, -- Public identifier of the layout. Unique.
    type VARCHAR(255) NOT NULL, -- Kind of layout, for example 'Global' or 'Workspace'.
    name VARCHAR(255) NOT NULL, -- Name of the layout. Unique per type per owner while private, and unique per type across all owners once shared or public.
    disabled BOOLEAN NOT NULL, -- Whether the layout is withheld from all users.
    "accessLevel" VARCHAR(255) NOT NULL, -- Who can reach the layout: 'private', 'shared' or 'public'.
    "owner" VARCHAR(255) NOT NULL, -- User the layout belongs to.
    "accessInfo" JSON NULL, -- Users and groups granted access, with the level granted to each. Present only for shared layouts. JSON.
    definition JSON NOT NULL, -- The layout payload as saved by the io.Connect platform. JSON.
    "createdBy" VARCHAR(255) NOT NULL, -- User that created the layout.
    "createdOn" BIGINT NOT NULL, -- Creation time. Epoch milliseconds (UTC).
    "lastModifiedBy" VARCHAR(255) NULL, -- User that last modified the layout.
    "lastModifiedOn" BIGINT NULL, -- Time of the last modification. Epoch milliseconds (UTC).
    CONSTRAINT layouts_advanced_id_unique UNIQUE (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS layouts_advanced__name__type__owner__unique
    ON layouts_advanced (name, type, owner)
    WHERE "accessLevel" = 'private';

CREATE UNIQUE INDEX IF NOT EXISTS layouts_advanced__name__type__nonprivate__unique
    ON layouts_advanced (name, type)
    WHERE "accessLevel" IN ('shared', 'public');

-- Server-wide state shared by every io.Manager instance, one row per named entry.
CREATE TABLE IF NOT EXISTS system_state
(
    system_state_id SERIAL PRIMARY KEY, -- Primary key. Auto-assigned by the database and not used to identify the row outside it.
    name VARCHAR(255) NOT NULL, -- Name of the entry. Unique.
    value JSONB NOT NULL, -- The entry value. JSON.
    version INT NOT NULL, -- Revision of the entry, raised on every write so concurrent instances cannot overwrite each other.
    CONSTRAINT system_state_name_unique UNIQUE (name)
);

-- Object documentation. Every table and column carries a description readable from the
-- database itself.

-- groups
COMMENT ON TABLE "groups" IS 'A group of users. Groups are used to grant access to applications and layouts.';
COMMENT ON COLUMN "groups"."groups_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "groups"."name" IS 'Name of the group. Unique.';
COMMENT ON COLUMN "groups"."description" IS 'Description of the group.';
COMMENT ON COLUMN "groups"."expandsTo" IS 'Groups this group also grants membership of. Empty when it expands into no others. Array of strings.';

-- last_updated
COMMENT ON TABLE "last_updated" IS 'A single row of change timestamps. An io.Connect platform polls these to learn whether it needs to re-fetch a kind of data.';
COMMENT ON COLUMN "last_updated"."last_updated_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "last_updated"."applications" IS 'Time any application last changed. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "last_updated"."layouts" IS 'Time any layout last changed. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "last_updated"."groups" IS 'Unused. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "last_updated"."commands" IS 'Time any command last changed. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "last_updated"."configs" IS 'Time any system config last changed. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "last_updated"."others" IS 'Further change timestamps that do not have a column of their own. JSON.';

-- layouts
COMMENT ON TABLE "layouts" IS 'Layouts saved by an io.Connect platform.';
COMMENT ON COLUMN "layouts"."layouts_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "layouts"."id" IS 'Public identifier of the layout. Unique.';
COMMENT ON COLUMN "layouts"."type" IS 'Kind of layout, for example ''Global'' or ''Workspace''.';
COMMENT ON COLUMN "layouts"."name" IS 'Name of the layout. Unique per type per owner.';
COMMENT ON COLUMN "layouts"."owner" IS 'User the layout is private to. The literal ''*'' marks a common layout, whose visibility is then decided by the public and accessList columns.';
COMMENT ON COLUMN "layouts"."public" IS 'Whether every user can read the layout. Applies only to common layouts.';
COMMENT ON COLUMN "layouts"."disabled" IS 'Whether the layout is withheld from all users.';
COMMENT ON COLUMN "layouts"."accessList" IS 'Groups allowed to read the layout when it is common and not public. Array of strings.';
COMMENT ON COLUMN "layouts"."definition" IS 'The layout payload as saved by the io.Connect platform. JSON.';
COMMENT ON COLUMN "layouts"."createdBy" IS 'User that created the layout.';
COMMENT ON COLUMN "layouts"."createdOn" IS 'Creation time. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "layouts"."lastModifiedBy" IS 'User that last modified the layout.';
COMMENT ON COLUMN "layouts"."lastModifiedOn" IS 'Time of the last modification. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "layouts"."default" IS 'Whether the layout is restored when an io.Connect platform starts.';
COMMENT ON COLUMN "layouts"."migrated" IS 'Whether this layout has already been migrated to the layouts_advanced table.';

-- machines
COMMENT ON TABLE "machines" IS 'A machine an io.Connect platform has run on. Referenced by sessions.';
COMMENT ON COLUMN "machines"."machines_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "machines"."id" IS 'Public identifier of the machine. Unique.';
COMMENT ON COLUMN "machines"."user" IS 'User the machine is associated with.';
COMMENT ON COLUMN "machines"."os" IS 'Operating-system details reported by the machine. JSON.';
COMMENT ON COLUMN "machines"."name" IS 'Host name of the machine.';
COMMENT ON COLUMN "machines"."displays" IS 'Displays attached to the machine. JSON.';
COMMENT ON COLUMN "machines"."browser" IS 'Browser details. Populated for io.Connect Browser sessions only. JSON.';

-- prefs
COMMENT ON TABLE "prefs" IS 'Per-user, per-application preferences. One row per application and user.';
COMMENT ON COLUMN "prefs"."prefs_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "prefs"."id" IS 'Public identifier of the preference. Unique.';
COMMENT ON COLUMN "prefs"."app" IS 'Application the preference belongs to.';
COMMENT ON COLUMN "prefs"."user" IS 'User the preference belongs to.';
COMMENT ON COLUMN "prefs"."data" IS 'The preference payload. Arbitrary data. JSON.';
COMMENT ON COLUMN "prefs"."lastUpdate" IS 'Time of the last update. Epoch milliseconds (UTC).';

-- sessions
COMMENT ON TABLE "sessions" IS 'A run of an io.Connect platform by one user on one machine.';
COMMENT ON COLUMN "sessions"."sessions_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "sessions"."id" IS 'Public identifier of the session. Unique.';
COMMENT ON COLUMN "sessions"."machine" IS 'Machine the session ran on.';
COMMENT ON COLUMN "sessions"."start" IS 'Time the session started. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "sessions"."user" IS 'User that opened the session.';
COMMENT ON COLUMN "sessions"."glue" IS 'io.Connect platform details reported for the session. JSON.';
COMMENT ON COLUMN "sessions"."lastDataFetch" IS 'Time the client last requested data from the server. Used to purge inactive sessions. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "sessions"."end" IS 'Time the session was closed. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "sessions"."closed" IS 'Whether the session has been closed.';
COMMENT ON COLUMN "sessions"."closeReason" IS 'Why the session was closed. ''clean'' when an administrator closed it.';
COMMENT ON COLUMN "sessions"."product" IS 'The io.Connect product that opened the session.';
COMMENT ON COLUMN "sessions"."productVersion" IS 'Version of that product. Determines which commands the session supports.';

-- users
COMMENT ON TABLE "users" IS 'A user known to io.Manager.';
COMMENT ON COLUMN "users"."users_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "users"."id" IS 'Identifier of the user. Unique.';
COMMENT ON COLUMN "users"."email" IS 'Email address of the user.';
COMMENT ON COLUMN "users"."password" IS 'A bcrypt hash of the user''s password. Populated only when basic authentication is enabled.';
COMMENT ON COLUMN "users"."apps" IS 'Applications granted directly to the user. Array of strings.';
COMMENT ON COLUMN "users"."groups" IS 'Groups the user belongs to. Array of strings.';
COMMENT ON COLUMN "users"."lastUpdated" IS 'Time the user last changed. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "users"."firstName" IS 'First name of the user.';
COMMENT ON COLUMN "users"."lastName" IS 'Last name of the user.';
COMMENT ON COLUMN "users"."layouts" IS 'Layouts granted directly to the user. Array of JSON values.';
COMMENT ON COLUMN "users"."others" IS 'Per-user timestamps that do not have a column of their own. JSON.';

-- glue42SystemConfig
COMMENT ON TABLE "glue42SystemConfig" IS 'System configuration served to an io.Connect platform. The best-matching row for a platform version, group and user wins.';
COMMENT ON COLUMN "glue42SystemConfig"."glue42SystemConfig_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "glue42SystemConfig"."identifier" IS 'The platform version, group and user this row applies to. Either of the group and user may be ''*'' to match any. JSON object, stored as text. Unique.';
COMMENT ON COLUMN "glue42SystemConfig"."configs" IS 'The configuration files this row supplies, each keyed by file name. JSON.';
COMMENT ON COLUMN "glue42SystemConfig"."weight" IS 'Precedence of this row when several rows match the same platform. Higher wins.';

-- feedback
COMMENT ON TABLE "feedback" IS 'A feedback report submitted by a user from an io.Connect platform.';
COMMENT ON COLUMN "feedback"."feedback_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "feedback"."id" IS 'Public identifier of the feedback report. Unique.';
COMMENT ON COLUMN "feedback"."date" IS 'Time the report was filed. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "feedback"."user" IS 'User that filed the report.';
COMMENT ON COLUMN "feedback"."session" IS 'Session the report was filed from.';
COMMENT ON COLUMN "feedback"."description" IS 'Free-text description entered by the user.';
COMMENT ON COLUMN "feedback"."attachment" IS 'File name of the attachment. The bytes are held in the blobs table.';
COMMENT ON COLUMN "feedback"."reviewed" IS 'Whether an administrator has reviewed the report.';
COMMENT ON COLUMN "feedback"."comment" IS 'Review notes left by an administrator.';

-- crashes
COMMENT ON TABLE "crashes" IS 'A crash reported by an io.Connect platform.';
COMMENT ON COLUMN "crashes"."crashes_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "crashes"."id" IS 'Public identifier of the crash. Unique.';
COMMENT ON COLUMN "crashes"."date" IS 'Time the report reached io.Manager. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "crashes"."user" IS 'User of the session that produced the crash.';
COMMENT ON COLUMN "crashes"."info" IS 'Crash details reported by the io.Connect platform. JSON.';
COMMENT ON COLUMN "crashes"."reviewed" IS 'Whether an administrator has reviewed the crash.';
COMMENT ON COLUMN "crashes"."comment" IS 'Review notes left by an administrator.';

-- commands_for_version
COMMENT ON TABLE "commands_for_version" IS 'The commands one io.Connect product version supports, as reported by a session of that version.';
COMMENT ON COLUMN "commands_for_version"."commands_for_version_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "commands_for_version"."version" IS 'Version of the io.Connect product.';
COMMENT ON COLUMN "commands_for_version"."commands" IS 'The supported commands and their parameters. JSON.';
COMMENT ON COLUMN "commands_for_version"."product" IS 'The io.Connect product. Unique together with the version.';

-- commands
COMMENT ON TABLE "commands" IS 'A command sent to a running io.Connect platform, and its result.';
COMMENT ON COLUMN "commands"."commands_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "commands"."id" IS 'Public identifier of the command. Unique.';
COMMENT ON COLUMN "commands"."user" IS 'User the command targets.';
COMMENT ON COLUMN "commands"."status" IS 'Progress of the command, for example ''Created'' or ''Executed''.';
COMMENT ON COLUMN "commands"."session" IS 'Session the command targets.';
COMMENT ON COLUMN "commands"."machine" IS 'Machine the command targets.';
COMMENT ON COLUMN "commands"."createdBy" IS 'User that invoked the command.';
COMMENT ON COLUMN "commands"."createdAt" IS 'Time the command was invoked. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "commands"."command" IS 'Kind of command, for example ''GetLogs'' or ''SendFeedback''.';
COMMENT ON COLUMN "commands"."commandParams" IS 'Parameters passed to the command. An empty object when the command takes none. JSON.';
COMMENT ON COLUMN "commands"."resultData" IS 'The command result, or the file name when the result is a file held in the blobs table. JSON.';
COMMENT ON COLUMN "commands"."resultAt" IS 'Time the command was executed on the target machine. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "commands"."resultType" IS 'Form of the result: ''JSON'' or ''file''.';
COMMENT ON COLUMN "commands"."traceContext" IS 'OpenTelemetry span context the command was invoked under. JSON.';

-- audit
COMMENT ON TABLE "audit" IS 'An audit record of one operation performed through io.Manager.';
COMMENT ON COLUMN "audit"."audit_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "audit"."id" IS 'Public identifier of the audit record. Unique.';
COMMENT ON COLUMN "audit"."user" IS 'User that triggered the operation.';
COMMENT ON COLUMN "audit"."parent" IS 'Audit record of the operation that caused this one, when it was not triggered directly.';
COMMENT ON COLUMN "audit"."date" IS 'Time of the operation. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "audit"."session" IS 'Session the operation was triggered from, when it came from an io.Connect platform.';
COMMENT ON COLUMN "audit"."server" IS 'Unused.';
COMMENT ON COLUMN "audit"."entityType" IS 'Kind of entity the operation acted on, for example ''application'' or ''layout''.';
COMMENT ON COLUMN "audit"."entityId" IS 'Identifier of the entity the operation acted on.';
COMMENT ON COLUMN "audit"."entityDisplayName" IS 'Display name of that entity.';
COMMENT ON COLUMN "audit"."operation" IS 'The operation performed, for example ''create'' or ''delete''.';
COMMENT ON COLUMN "audit"."operationComment" IS 'Note clarifying the operation, typically what it contributed to its parent operation.';
COMMENT ON COLUMN "audit"."newValue" IS 'The entity after the operation. JSON.';
COMMENT ON COLUMN "audit"."oldValue" IS 'The entity before the operation. JSON.';
COMMENT ON COLUMN "audit"."request" IS 'The request that triggered the operation. JSON.';
COMMENT ON COLUMN "audit"."response" IS 'Unused. JSON.';

-- app
COMMENT ON TABLE "app" IS 'An application io.Manager serves to io.Connect platforms.';
COMMENT ON COLUMN "app"."app_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "app"."name" IS 'Name of the application. Unique.';
COMMENT ON COLUMN "app"."disabled" IS 'Whether the application is withheld from all users.';
COMMENT ON COLUMN "app"."definition" IS 'The application definition served to the io.Connect platform. JSON.';
COMMENT ON COLUMN "app"."public" IS 'Whether every user can access the application.';
COMMENT ON COLUMN "app"."accessList" IS 'Groups allowed to access the application when it is not public. Array of strings.';
COMMENT ON COLUMN "app"."createdBy" IS 'User that created the application.';
COMMENT ON COLUMN "app"."createdOn" IS 'Creation time. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "app"."lastModifiedBy" IS 'User that last modified the application.';
COMMENT ON COLUMN "app"."lastModifiedOn" IS 'Time of the last modification. Epoch milliseconds (UTC).';

-- blobs
COMMENT ON TABLE "blobs" IS 'Binary content held for another entity, such as a feedback attachment or a command result file.';
COMMENT ON COLUMN "blobs"."blobs_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "blobs"."type" IS 'Kind of entity the content belongs to, which together with the id locates it.';
COMMENT ON COLUMN "blobs"."id" IS 'Identifier of the entity the content belongs to. Unique.';
COMMENT ON COLUMN "blobs"."fileName" IS 'File name to serve the content under.';
COMMENT ON COLUMN "blobs"."data" IS 'The content itself. Binary content.';
COMMENT ON COLUMN "blobs"."encoding" IS 'Encoding of the content: "raw" when it is the file''s own bytes, "base64" when it is those bytes base64-encoded as text. Absent on content stored before the encoding was recorded.';

-- layouts_advanced
COMMENT ON TABLE "layouts_advanced" IS 'Layouts saved by an io.Connect platform, with per-entity access control.';
COMMENT ON COLUMN "layouts_advanced"."layouts_advanced_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "layouts_advanced"."id" IS 'Public identifier of the layout. Unique.';
COMMENT ON COLUMN "layouts_advanced"."type" IS 'Kind of layout, for example ''Global'' or ''Workspace''.';
COMMENT ON COLUMN "layouts_advanced"."name" IS 'Name of the layout. Unique per type per owner while private, and unique per type across all owners once shared or public.';
COMMENT ON COLUMN "layouts_advanced"."disabled" IS 'Whether the layout is withheld from all users.';
COMMENT ON COLUMN "layouts_advanced"."accessLevel" IS 'Who can reach the layout: ''private'', ''shared'' or ''public''.';
COMMENT ON COLUMN "layouts_advanced"."owner" IS 'User the layout belongs to.';
COMMENT ON COLUMN "layouts_advanced"."accessInfo" IS 'Users and groups granted access, with the level granted to each. Present only for shared layouts. JSON.';
COMMENT ON COLUMN "layouts_advanced"."definition" IS 'The layout payload as saved by the io.Connect platform. JSON.';
COMMENT ON COLUMN "layouts_advanced"."createdBy" IS 'User that created the layout.';
COMMENT ON COLUMN "layouts_advanced"."createdOn" IS 'Creation time. Epoch milliseconds (UTC).';
COMMENT ON COLUMN "layouts_advanced"."lastModifiedBy" IS 'User that last modified the layout.';
COMMENT ON COLUMN "layouts_advanced"."lastModifiedOn" IS 'Time of the last modification. Epoch milliseconds (UTC).';

-- system_state
COMMENT ON TABLE "system_state" IS 'Server-wide state shared by every io.Manager instance, one row per named entry.';
COMMENT ON COLUMN "system_state"."system_state_id" IS 'Primary key. Auto-assigned by the database and not used to identify the row outside it.';
COMMENT ON COLUMN "system_state"."name" IS 'Name of the entry. Unique.';
COMMENT ON COLUMN "system_state"."value" IS 'The entry value. JSON.';
COMMENT ON COLUMN "system_state"."version" IS 'Revision of the entry, raised on every write so concurrent instances cannot overwrite each other.';

-- Minimum required user permissions for the schema and the schema objects.
GRANT USAGE ON SCHEMA my_schema TO my_user;
GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_schema TO my_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA my_schema TO my_user;
```