Receive external database writes

Let software on a customer's PC or server insert events into your App's own PostgreSQL table. Your App requests credentials and registers source IPs through the PHP SDK; Nexia manages the database role and network rules. This capability accepts incoming writes to Nexia. It does not connect Nexia to an arbitrary external database or provide a general SQL account.

The sequence is request a writer → register its source IP → wait for applied access → reveal credentials once → test an INSERT. Users do not need AWS access.

Prepare your App

Use PHP SDK 0.9.1 or later and a compatible Core host with external writer network access (0.8.14 or later). Declare those minimums in your App's compatibility metadata. A package version alone does not prove availability: the selected environment must have its gateway enabled and its App runtime must contain the matching SDK. When network() reports enabled: false, show that external access is unavailable and ask the platform operator to enable it; do not offer a private DB endpoint as an external connection.

Follow Data Model and Migrations to create an App-owned ingestion table and Add a protected API to declare read and administration permissions. Use authenticated App routes in the selected tenant. The SDK derives the App, tenant, actor and database allocation from that context; it is not a public browser API or an unauthenticated script interface.

The excerpts below assume a Workshop App with:

• An existing wsp_external_events table: generated id, payload JSONB, and received_at defaulting to the database's current timestamp. No other required column may lack a default.
• Declared permissions workshop.external_events.manage and workshop.external_events.read, assigned to the appropriate users.
• An App-owned integration record that stores the returned opaque writer handle for this tenant. Authorize that record before loading its handle in later requests.

External SQL bypasses your App's controllers, model events and API validation. Enforce ingestion constraints in the database and validate/process raw events through your App before using them as trusted business data. Never grant external writers columns that let them assign internal approvals, ownership or processing state.

Request the writer

In the App's authenticated integration service, use a server-defined target. Do not accept a table, column list or permission name from the client. This PHP excerpt belongs inside that service, after the App-owned integration record has been saved:

use Nexia\Laravel\Database\Contracts\ExternalDatabaseWriterProvisioner;
use Nexia\Laravel\Database\ExternalDatabaseWriterDefinition;

$writers = app(ExternalDatabaseWriterProvisioner::class);
$definition = new ExternalDatabaseWriterDefinition(
    key: 'external_events',
    table: 'wsp_external_events',
    insertColumns: ['payload'],
    permission: 'workshop.external_events.manage',
    readPermission: 'workshop.external_events.read',
);
$provisioning = $writers->request($definition);
$handle = $provisioning->handle;12345678910111213

Persist $handle in that integration record. Do not persist a role name or password as App state. Provisioning runs asynchronously. Keep the integration record and Core request as separate steps: a Core effect cannot be rolled back by an App database transaction. Repeating request() with the same definition reuses a pending/provisioning/ready writer; changing an existing writer's target or permissions requires review.

permission governs requests, rotation, reveal and IP changes; readPermission governs status and IP reads. Keep the corresponding checks on your routes as well. Knowing a handle does not authorize another tenant, App or user.

Register the sending machine's IP

Read the policy from an authorized App endpoint. In these excerpts, $handle comes from the integration record, not an unchecked request parameter:

use Nexia\Laravel\Database\Contracts\ExternalDatabaseWriterNetworkAccess;

$network = app(ExternalDatabaseWriterNetworkAccess::class);
$policy = $network->network($handle);1234

The browser's IP may differ from the sender's IP. A laptop behind a company router normally uses the router's public egress IP; a remote integration server needs that server's egress IP. Wi-Fi, VPN or network changes may require registration again. Do not silently authorize every browser visit.

Your mutation endpoint validates the submitted complete list and the revision from the displayed policy, then calls:

// $addresses: validated list<string> from the form.
// $expectedRevision: validated non-negative integer from the displayed policy.
$saved = $network->setNetwork($handle, $addresses, $expectedRevision);123

This replaces the whole list, not just one entry. Preserve addresses the user has not removed. Accept individual public IPv4 addresses only: no CIDR ranges, private/reserved addresses, IPv6 or 0.0.0.0/0. The current limit is five IPs per writer and fifty distinct IPs per environment; shared IPs count once in the environment's network rules. AWS and host firewall changes are automatic after registration.

A stale revision returns HTTP 409. Reload the current policy and let the user reconcile the change; do not silently fetch a fresh revision and overwrite someone else's list. IP validation or capacity failures return HTTP 422.

Wait, then reveal once

Poll status($handle) and network($handle) with a bounded interval, for example every five seconds while this screen is open. Keep “applying” distinct from “ready” and provide refresh/recovery when waiting takes too long. A successful registration response proves that the list was saved, not that a socket connection works.

The following is an excerpt from an authorized reveal action, not a polling handler. $writers and $network are the SDK services above:

$provisioning = $writers->status($handle);
$policy = $network->network($handle);

abort_unless(
    $policy['enabled'] && $policy['addresses'] !== [] && $policy['applied']
    && $provisioning->status === 'ready' && $provisioning->connection !== null,
    409,
);
$credential = $writers->reveal($handle);

return response()->json([
    'host' => $credential->host,
    'port' => $credential->port,
    'database' => $credential->database,
    'schema' => $credential->schema,
    'username' => $credential->username,
    'password' => $credential->password,
    'ssl_mode' => $credential->sslMode,
])->header('Cache-Control', 'no-store');12345678910111213141516171819

The actor who requested the current credential must reveal it and still have administration permission. The password can be revealed once, within ten minutes of successful role provisioning. Do not reveal automatically during status polling. Do not log it, put it in browser storage/query caches, or save it in App tables. Present it transiently for transfer into the external client's credential store. If a reveal response is lost, consumed or expired, rotate through an explicit user action instead of replaying reveal.

Use the returned host, port, database, schema, username and SSL mode. Never construct them from tenant IDs or hardcode a domain or AWS RDS hostname. Current managed endpoints are db-connect.dev.nexia.to for development and db-connect.nexia.to for production; their stable names allow the platform to change the backend without editing client configuration. They are examples of platform configuration, not constants for App code.

Verify from the external client

Use a PostgreSQL driver that supports SCRAM-SHA-256 and TLS hostname/certificate verification. Configure verify-full as returned by the gateway and the appropriate trusted CA for that driver. Match the driver's architecture to the external program, including 32-bit ODBC where required. Do not weaken TLS when connection tests fail.

From the machine whose public IP you registered, configure the returned connection fields and insert into the example table:

INSERT INTO wsp_external_events (payload)
VALUES ('{"event":"docs-check"}'::jsonb);12

The writer's search path is set to its returned schema. If the client requires a qualified table name, use that schema. Avoid RETURNING and clients that require table reads: this role grants INSERT on the declared columns and necessary sequence access, not general SELECT, UPDATE or DELETE. Verify the inserted row through your authorized App screen, not by granting the external writer read access.

In a practice tenant, also confirm that an unauthorized actor cannot register or reveal credentials; an unregistered source IP cannot connect; other tables and protected columns reject writes; and removing the last IP eventually terminates access. A TCP connection test alone does not verify authentication or INSERT privileges. Actual vendor/ODBC acceptance must be checked with that program; package tests do not establish it.

Change or revoke access

Call setNetwork() with the complete remaining list and its last-read revision. An empty list revokes this writer's gateway access after reconciliation. This does not delete its data or database role. A shared source IP may remain allowed at the network layer for another writer; the gateway still enforces the individual database/role/IP policy.

IP changes do not require a new password. To rotate a credential, explicitly call request($definition, rotate: true) with the same server-defined target, wait for ready/applied again, reveal once and update the external client. Rotation changes the password, so the client must be updated even though its hostname is stable.

Revocation is asynchronous: allow the reconciliation interval (normally fifteen seconds after the preceding pass) plus execution time. IP or credential changes terminate affected gateway sessions; the initial implementation may also disconnect other writers using the same database. Clients should reconnect with bounded retries. Do not promise instantaneous revocation or uninterrupted rotation.

Recover from failures

The current design owners are the public SDK external-writer contracts and Core's external-writer provisioning/gateway implementation. App developers consume these contracts; server setup, AWS permissions and gateway deployment remain platform responsibilities.