Example: application database

A complete SQL connector for Acme Ledger, a fictional accounting application that keeps its users and their roles in a Postgres database. It syncs users and roles, creates, updates, disables, enables and deletes users, and adds and removes role assignments as the writable Grant kind has_role.

Paste the definition into the Connector Builder's Raw JSON tab of a new SQL connector, or write it as definition.json through the API. It derives with Owlie's SQL engine and passes its publish checks. The SQL track explains every field.

The tables

CREATE TABLE ledger_users (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email varchar(254) NOT NULL UNIQUE,
  full_name varchar(200),
  status varchar(20) NOT NULL DEFAULT 'active',
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE ledger_roles (
  code varchar(50) PRIMARY KEY,
  label varchar(200) NOT NULL
);

CREATE TABLE ledger_user_roles (
  user_id integer NOT NULL REFERENCES ledger_users (id),
  role_code varchar(50) NOT NULL REFERENCES ledger_roles (code),
  PRIMARY KEY (user_id, role_code)
);

Acme Ledger never deletes a user row: it sets status to deleted. A suspended user has status suspended and cannot sign in. The application updates updated_at whenever a user changes.

Decisions

• Two entities. user is the account, identified by its primary key id. role is the target of role assignments, identified by code. Both keys never change.
• Users sync from the table, roles from a SELECT. ledger_users is read as a table, so installs can filter it. Roles are read through a selectStatement that names the two columns the connector uses.
• The filter is install-editable. Its seed, status <> 'deleted', leaves deleted users out. A user Owlie cannot read looks deleted to Owlie, so the soft delete below and this filter work together. An install that removes the filter would see deleted users as active accounts.
• The page size is install-editable, so an install on a slow database can read smaller pages.
• The connection test reads ledger_users, which proves the connecting user can read the table, not only that it can sign in.
• Incremental sync on updated_at. An incremental run reads only the rows the filter lets through, so a user deleted since the last run is not in it. The next full Sync finds the row gone and records the account as deleted.
• Writes. create returns the new id with RETURNING. update binds email and full_name, which makes them the writable columns; status changes only through disable, enable and delete. disable and enable leave deleted users alone, so they change no row, and fail, for a deleted user. delete is a soft delete, as the application does it. Every statement casts :id, which binds as text, to integer.
• Suspended users still read as active. A SQL connector never reports a disabled account, so after disable Owlie sees the user as active until the integration's admin adds the Sync rule status == 'active' (Account state).
• Installs switch the account Sync job to Resource mode. Ledger users are accounts of people who already exist in Owlie, but a SQL connector's account Sync job starts in Identity mode, which would create an identity per row. The connector's README tells admins to set the job's Sync mode to Resource (Sync mode).
• Role assignments are writable. add uses ON CONFLICT DO NOTHING, so adding an assignment that exists changes no row and reads as already present. remove deletes exactly the assignment's row, so a remove that changes no row means the assignment was not there.

The definition

{
  "engine": 1,
  "dialect": "postgres",
  "description": "Acme Ledger users and roles, read from and written to its own database.",
  "icon": "@acmeledger.example",
  "config": {
    "properties": {
      "userFilter": {
        "type": "string",
        "title": "Which users sync",
        "description": "A condition on ledger_users, without WHERE. The default leaves out deleted users.",
        "default": "status <> 'deleted'"
      },
      "pageSize": {
        "type": "integer",
        "title": "Page size",
        "minimum": 1,
        "maximum": 5000,
        "default": 500
      }
    }
  },
  "pageSize": { "$oxl": "config.pageSize" },
  "validationQuery": "SELECT 1 FROM ledger_users LIMIT 1",
  "entities": {
    "user": {
      "name": "User",
      "is_account": true,
      "identifier": "id",
      "columns": {
        "id": { "type": "string" },
        "email": { "type": "string", "title": "Email" },
        "full_name": { "type": "string", "title": "Name" },
        "status": { "type": "string", "title": "Status" },
        "updated_at": { "type": "datetime", "title": "Updated" }
      },
      "sync": {
        "table": "ledger_users",
        "where": { "$oxl": "config.userFilter" },
        "orderKey": "id",
        "updatedAt": "updated_at"
      },
      "statements": {
        "create": "INSERT INTO ledger_users (email, full_name, status, updated_at) VALUES (:email, :full_name, 'active', now()) RETURNING id",
        "update": "UPDATE ledger_users SET email = :email, full_name = :full_name, updated_at = now() WHERE id = CAST(:id AS integer)",
        "disable": "UPDATE ledger_users SET status = 'suspended', updated_at = now() WHERE id = CAST(:id AS integer) AND status <> 'deleted'",
        "enable": "UPDATE ledger_users SET status = 'active', updated_at = now() WHERE id = CAST(:id AS integer) AND status <> 'deleted'",
        "delete": "UPDATE ledger_users SET status = 'deleted', updated_at = now() WHERE id = CAST(:id AS integer)"
      }
    },
    "role": {
      "name": "Role",
      "identifier": "code",
      "columns": {
        "code": { "type": "string", "title": "Code" },
        "label": { "type": "string", "title": "Label" }
      },
      "sync": {
        "selectStatement": "SELECT code, label FROM ledger_roles",
        "orderKey": "code"
      }
    }
  },
  "grant_kinds": {
    "has_role": {
      "source": "user",
      "target": "role",
      "select": "SELECT user_id, role_code FROM ledger_user_roles",
      "sourceColumn": "user_id",
      "targetColumn": "role_code",
      "add": "INSERT INTO ledger_user_roles (user_id, role_code) VALUES (CAST(:source AS integer), :target) ON CONFLICT DO NOTHING",
      "remove": "DELETE FROM ledger_user_roles WHERE user_id = CAST(:source AS integer) AND role_code = :target"
    }
  }
}

What an install fills in

Field	Value for Acme Ledger production
host	db.acmeledger.example
port	Left empty for Postgres's 5432
database	ledger
user	owlie_connector
password	The name of the gateway secret that holds the password

userFilter and pageSize start at their seeds, and defaultSchema stays empty: the tables live in the connecting user's default schema, where the statements' unqualified ledger_users resolves. Were they in another schema, setting defaultSchema to it would point every statement there. The database user needs SELECT on the three tables, INSERT and UPDATE on ledger_users, and INSERT and DELETE on ledger_user_roles. The gateway's operator lists db.acmeledger.example:5432 in the connector's egressAllowlist.

email is NOT NULL, and the connector gives a new user no values of its own, so the first create fails until the integration's admin maps the new user's columns on the resource's Provisioning tab under Baseline attributes: email from identity.email and full_name from identity.display_name (What a new account gets).

What Owlie derives

• Entities user (the account, identified by id) and role (identified by code). In the user schema, email and full_name are writable and every other column is read-only.
• The Grant kind has_role, from user to role, discovered through each role, writable.
• provides of sync and provision, and the handlers user.fetchOne, user.fetchAll, user.fetchAllSince, user.create, user.update, user.delete, user.enable, user.disable, role.fetchOne, role.fetchAll, role.fetchRelationships, grant.has_role.add and grant.has_role.remove, plus connection.validateConfig, connection.testConnection and connection.validateStatements.

Validate every statement before the first test: on the Statements tab, select Validate every statement (Validate statements).