Roost

A Firebird and InterBase console that runs in a browser, with an audit trail and an undo.

Firebird's tooling is desktop — FlameRobin, IBExpert, IBSurgeon. That is fine until the database lives inside a Kubernetes cluster, or you are not at your own machine, or more than one person needs to see what someone else changed. This is the same job, served over HTTP.

Why "Roost". It is where the firebird lives, and where you go to look in on it.

Nothing about any particular estate is compiled in. Point it at a server and it shows the tables the credential you gave it can see.

What it does

• Browse tables, views, columns, indexes and primary keys
• Page through rows with a real WHERE and ORDER BY
• Run read-only SQL, and see Firebird's query plan
• Edit, insert and delete rows — if the connection is configured to allow it
• Every change is recorded with the row as it was, and can be put back

The rule that shapes everything: rows are addressed by primary key

In a document store, "this exact document" is always expressible — _id always exists. In SQL it is not. An UPDATE with a WHERE clause is a statement about a set of rows, and on a table with no primary key there is no clause that provably matches the one row someone clicked on. It might match one. It might match four hundred. rowcount tells you afterwards, long after it matters.

So Roost:

• discovers the primary key from RDB$RELATION_CONSTRAINTS and addresses every edit and delete by the full key, never by a clause the UI composed;
• refuses to edit rows on a table with no primary key, and says why;
• refuses a partial composite key — half a key identifies a set, not a row;
• rolls back and reports if an update or delete would somehow affect more than one row.

That is a smaller tool than one with an Edit button on every table. It is also one that cannot quietly rewrite a day of takings.

Safety, since it can write to production

1. Connections are read-only unless the config says otherwise.
2. Writable connections require an explicit confirmation per change.
3. The row is read and recorded before it is changed.
4. If the audit record cannot be written, the caller is told the change failed. The backend refuses to start at all if the audit directory is not writable.

The audit log is append-only JSON Lines on disk, not in the database being audited — a trail stored inside the system it audits disappears at exactly the moment it matters.

Reads run in an explicitly read-only transaction, so the database refuses a write rather than this code trying to parse SQL and guess. Parsing SQL to decide what is safe always misses a case; the engine never does.

Money must not become a float

NUMERIC(18,2) is money in every point-of-sale database Firebird tends to live in. Values come back as strings, not floats — float would quietly round them, and nobody notices until a reconciliation fails.

Sign-in

Any number of identity providers, plus a break-glass credential. Every provider speaks OIDC and is discovered, never assumed: Roost fetches {issuer}/.well-known/openid-configuration and reads the jwks_uri from it.

Verified against Keycloak and WeldForge; the same shape covers Auth0, Entra, Okta and Google.

auth:
  providers:
    - id: keycloak
      issuer: https://auth.example.com/realms/internal
      client_id: roost
      groups_claim: groups        # Keycloak's name for it

    - id: weldforge
      issuer: https://weldforge.example.com/t/acme   # tenant in the path
      client_id: roost
      groups_claim: roles         # WeldForge emits `roles`, not `groups`

There is no standard claim for group membership, so it is per-provider configuration rather than a constant.

The break-glass credential carries no groups, so it reaches only connections with no allowed_groups restriction — an emergency login must not inherit the most privileged access in the config. Starting with neither configured is refused rather than quietly serving an open console.

Configuration

connections:
  - id: production
    name: Production
    # host/port:path, or host/port:alias when the server uses databases.conf.
    dsn: firebird/3050:/var/lib/firebird/data/app.fdb
    user: SYSDBA
    password: ${FIREBIRD_PASSWORD}
    charset: UTF8                 # NOT NONE — see below
    writable: true
    confirm_writes: true
    allowed_groups: [db-admins]   # optional
    tables: [SALES, CUSTOMERS]    # optional; empty means all

audit:
  path: /data/audit.log

limits:
  max_rows: 200
  max_delete: 100

Use UTF8, not NONE. With NONE the server returns bytes in whatever character set the field was written in and the driver cannot tell you which, so text arrives mojibaked and it looks like the data is corrupt.

A missing ${VAR} fails at boot rather than at connect time — otherwise a half-built connection surfaces as an authentication failure hours later against the wrong database.

Running it

docker compose up --build

Built with

tina4-python 3.13	routing, templating, sessions
Tina4JS	the frontend: signals and web components, no build step
firebird-driver	the modern driver; needs libfbclient at runtime

firebird-driver, not fdb. The latter is in maintenance; the former is what Firebird 3+ and 5 expect. It needs the Firebird client library present at runtime — without it the import succeeds and connecting fails, which reads like a network problem.

Tests

cd backend && python -m pytest tests/ -q

They cover refusals, key handling and the audit contract rather than Firebird itself — a test that needs a live server is a test that does not get run.

Licence

Apache 2.0 — see LICENSE.

Not affiliated with or endorsed by the Firebird Project, the FirebirdSQL Foundation, or Embarcadero. "Firebird" and "InterBase" are their respective trademarks; this is an independent tool.