Databases and storage

This page is for administrators. It covers the Databases page, where outside databases are connected and checked, and the Storage page, where you choose which database holds each kind of record. Today the only kind that can be routed is the audit log.

Database connections

A database connection is a saved way to sign in to an outside database server. The gateway creates only its own tables in the database you name; the database itself must exist already. Because a connection carries a password, everything here is admin only.

The Databases page

Open Connections, then Databases (the entry appears only for administrators). The page lists the connections with their state; selecting one opens a panel beside the list.

Adding a connection

1. Click New connection.
2. Under Connection, give it a Name (up to 60 characters, unique among connections) and an optional Description (up to 500 characters).
3. Under Server, pick the Engine and enter the Host, Port and Database.
4. Under Sign-in, enter the User and Password. Use a user of the gateway's own, allowed to create tables in that database and to read and write them. On an edit, a blank password keeps the one already saved.
5. Under Advanced, optional Extra settings are connection string settings separated by semicolons (up to 1000 characters). They win over the fields above. The gateway checks that the engine's driver understands them before saving.
6. Leave On switched on and click Add. The gateway starts checking the connection at once.

Engine	Default port
PostgreSQL	5432
Microsoft SQL Server	1433
MySQL or MariaDB	3306

The host is a server name or address without spaces or ;. The port is 1 to 65535; left empty, it is the engine's default.

Testing

Test the connection signs in with the settings as they are in the form, saved or not, and reports the result: the state, the server's version and the sign-in latency in milliseconds, or the driver's error. On a saved connection a blank password tests with the saved one.

Health check

Every 30 seconds the gateway signs in to each enabled connection and asks the server its version, so the page shows whether a connection works before anything depends on it. Saving a connection triggers a check right away. The connect timeout is 10 seconds.

State	Meaning
connecting	Saved, not checked yet
connected	The last check signed in; latencyMs and serverVersion are set
faulted	The last check failed; error says why
disabled	On is off. The gateway leaves it alone, and records meant for it wait on the gateway

A change of state is written to the gateway log. The check itself is not counted in the query rates below.

Deleting

Delete removes the connection. It answers 409 while Storage still routes a kind of record to it; pick another location first.

Passwords

Passwords are encrypted with ASP.NET Data Protection before they are stored and are never returned by the API, which only reports hasPassword. The keys live in DATA_DIR/keys; see Backup and restore.

Storage

Open Gateway, then System, then Storage. The page lists each kind of record and where it is kept: On this gateway (the gateway's own SQLite database) or one of the database connections. Choosing a connection and saving changes the route at once.

Kind	Label	Today
audit	Audit log	The only routable kind

Store and forward for the audit log

Every write is recorded in the gateway's own database first, in the same moment as the write, so an outside database that is slow or down never holds up or loses a write. When the audit log is routed to a connection, a forwarder copies finished entries there:

• It runs every 5 seconds, or sooner when woken, in batches of 500, in order.
• The first batch creates the table scadex_tag_write_audit if it is missing. Columns: gateway_id, local_id, at, principal_id, principal_name, operation, tag_id, tag_path, module_id, value, correlation_id, outcome, error, completed_at.
• Each row carries this gateway's installation id, the entry's local id and its time. Before inserting, the forwarder reads which (local_id, at) pairs the table already holds for this gateway in the batch's id range and skips them, so a retry after a lost answer never writes an entry twice. Several gateways can share one table.
• An entry still pending is held back until it is 10 minutes old (the gateway stopped mid-write and it will never finish), then copied as it is.
• A copied row is marked with the time it was forwarded. Local rows are never pruned: the gateway's own copy always holds every entry.
• Changing the route clears the forwarded marks, so a new location receives everything the gateway holds. The old location keeps what it had.
• A connection that is switched off makes the forwarder wait with the error "… is turned off, so entries wait on the gateway until it is back on."

The Storage page shows the forwarding status for the audit log: entries waiting, when the last batch was copied, and the last error with its time.

Reading a routed audit log

The Audit page reads from wherever the log is stored. From an outside database, entries not copied yet are added from the gateway, so a write shows the moment it is made. If the outside database cannot be read, the gateway's own copy is shown and the response header X-Scadex-Audit-Warning says why; X-Scadex-Audit-Store names the store that answered (gateway or the connection's name).

Connection tags

The state of every connection is published under @gateway.connection, refreshed every 2 seconds from memory (nothing here reaches a server). Database connections sit under @gateway.connection.database:

Tag	Type	Meaning
@gateway.connection.database.count	integer	Saved connections
@gateway.connection.database.connected	integer	Connections whose last check succeeded
…database.<name>.name	text	The connection's name
…database.<name>.state	text	connecting, connected, faulted or disabled
…database.<name>.connected	boolean	state == connected
…database.<name>.enabled	boolean	On
…database.<name>.address	text	host:port/database
…database.<name>.engine	text	The engine's label
…database.<name>.latency	integer, ms	Sign-in latency of the last check
…database.<name>.queriesPerSecond	integer, /s	Queries in the last whole second
…database.<name>.queriesPerMinute	integer, /min	Queries in the last 60 seconds
…database.<name>.version	text	Server version from the last check
…database.<name>.checked	text	When it was last checked, ISO-8601 UTC
…database.<name>.error	text	The last error, or empty
…database.<name>.since	text	When the state last changed

<name> is the connection's name with dots and spaces replaced by _; a name that collides with another, or with count or connected, gets a _2 suffix. Query rates count real work (forwarding, reading the audit log, scripts); the half-minute check is not counted.

Boolean and numeric leaves (connected, enabled, latency, the query rates) are real tags with history, so they can be trended and alarmed on. Text leaves are live only. Driver connections (OPC UA, MQTT) appear the same way under @gateway.connection.opcua and @gateway.connection.mqtt.

Limits

• Three engines; no SQLite, Oracle or others.
• Only the audit log can be routed. History stays with the historian module.
• A connection's password lives behind DATA_DIR/keys; without those keys it cannot be decrypted.
• Nothing has been proven against a production database server yet; the drivers are the standard .NET providers.

API

All routes need the admin role. The full reference is in Admin API.

Route	Purpose
GET /api/v1/admin/databases/engines	id, label, defaultPort per engine
GET /api/v1/admin/databases	Connections with status, queries and usedFor
POST /api/v1/admin/databases	Create {name, engine, host, port?, database, username, password?, options?, enabled?, description?}
PUT /api/v1/admin/databases/{id}	Replace; blank password keeps the saved one
DELETE /api/v1/admin/databases/{id}	409 while Storage uses it
POST /api/v1/admin/databases/test	Test unsaved settings; with id, a blank password means the saved one
POST /api/v1/admin/databases/{id}/check	Check a saved connection now (202)
GET /api/v1/admin/storage	Each kind with connectionId, connectionName and, for audit, forwarding
PUT /api/v1/admin/storage/{kind}	{connectionId}; empty means on this gateway