# FlowFabric API

FastAPI backend implementing the modules specified in `FlowFabric_SRS.docx`
Section 4, over the PostgreSQL schema in [`db/schema.sql`](../db/schema.sql).

## Setup

```bash
py -3.14 -m venv .venv          # or any recent CPython; see note below on 3.11 vs 3.14
./.venv/Scripts/pip install -r requirements.txt
cp .env.example .env            # adjust DATABASE_URL / OLLAMA_BASE_URL if needed
```

The database itself (role, database, `db/schema.sql`) is expected to already
exist — see the repo root for how it was created. `.env`'s default
`DATABASE_URL` points at `flowfabric`/`flowfabric` on `localhost:5432`.

### Schema changes

`db/schema.sql` builds a new database; the files in `db/migrations` bring an
existing one forward, and `schema_migrations` records which it has:

```bash
./.venv/Scripts/python.exe -m app.db_migrate status    # applied / pending / changed since applied
./.venv/Scripts/python.exe -m app.db_migrate up        # apply what's pending, in name order
./.venv/Scripts/python.exe -m app.db_migrate check     # exit 1 while anything is pending (deploy gate)
./.venv/Scripts/python.exe -m app.db_migrate baseline  # a database built from schema.sql: record all as applied
```

The API logs a warning at startup while anything is pending.

### Tenant isolation in the database

Postgres keeps tenants apart through row-level security
([`db/schema.sql`](../db/schema.sql)). Each request sets its tenant,
and the policies are **forced**, so they also bind the role that owns the
tables. A query that forgets `WHERE tenant_id = …` sees only its own
tenant's rows, and work done with no tenant set fails outright.

Exceptions:
- **`master_records` and `form_submissions`** have policies but aren't
  forced. Forcing them would stop Postgres from using their JSONB indexes.
  They are only read through `app/crud/record_sql.py`, which always scopes
  by tenant and source.
- **`jobs` and `sso_states`** have no policy, by design.

`tests/test_tenant_isolation.py` fails if a table with a `tenant_id` is
added without a forced policy.

**The API must connect as an ordinary role**, not as a superuser or a
`BYPASSRLS` role, because those skip row-level security entirely.
`python scripts/create_app_role.py` creates that role and its database
from `DATABASE_URL`, using `PROVISIONING_SUPERUSER`. Docker Compose and CI
already set this up. The test suite's own setup sessions connect as the
superuser, while the API under test uses its own role. A workspace
database provisioned from `schema.sql` is baselined automatically. The role
the API connects as needs `CREATE` on the `entity_data` schema to store
datasources and forms as tables.

### Sign-in security and single sign-on

Administrators set a workspace's sign-in policy under **Administration →
Security**.

- **Two-factor sign-in.** Uses codes from an authenticator app (TOTP).
  It can be optional, required for Studio users, or required for
  everyone. Each person manages their own under **Account → Security**,
  and gets ten single-use recovery codes. If the policy requires two-factor
  and someone hasn't set it up, they're walked through setup at sign-in.
  An administrator can reset someone's two-factor from **Users**.
- **Lockout.** An account locks after a number of failed passwords or
  codes in a row, for a set time. An administrator can unlock it early
  from **Users**.
- **Session length.** A workspace can set how long a session lasts,
  overriding `JWT_EXPIRY_MINUTES`.
- **Single sign-on (OpenID Connect).**
  - **Supported providers:** Microsoft Entra ID, Okta, Google Workspace,
    Keycloak, Auth0, or any other OpenID Connect provider.
  - **Provider setup:** register the redirect URI shown on the Security
    page with the provider. Behind a proxy, set `PUBLIC_API_URL` (e.g.
    `https://app.example.com/api`) so that URI is the public one.
  - **Accounts:** a person is matched by email the first time they sign
    in, and linked from then on. Accounts can optionally be created on
    first sign-in, with a role you choose.
  - **Requiring SSO:** Studio users can be made to sign in only through
    SSO. This can only be switched on from a session that itself signed in
    through SSO.
  - **Self-hosted providers:** a provider on the server's own network
    (such as a self-hosted Keycloak) needs
    `DATA_QUERY_ALLOW_PRIVATE_HOSTS=true`.
  - **Not supported:** SAML.

### Licensing

A tenant can't be used until it has a license
([`app/crud/licensing.py`](app/crud/licensing.py)). Until then nobody can
sign in, and the sign-in page offers to apply a license file. The Super
Admin licenses a tenant in one of two ways:
- **directly**, on the tenant's **License** tab or when creating it;
- **by license file**, which the tenant's administrator applies (see
  below).

A license sets how many active Studio and Portal users the tenant may
have, and the dates it's valid from and through. A blank field means no
limit, so saving with every field blank licenses the tenant with no
limits. **Remove license** takes it away again.
`scripts/provision_tenant.py --licensed` (with `--max-users` and
`--license-ends-on`) licenses a tenant it creates.

- **Seats.** Only *active* accounts count. Creating or re-activating one
  past the limit is refused, including by the Super Admin and through
  single sign-on. Portal registrations start out inactive, so they're
  always accepted; approving one needs a free seat. Lowering a limit below
  what's in use leaves those accounts working.
- **Dates.** The end date counts through the whole day, in UTC. Outside
  the dates, no one in the tenant can sign in, sessions already open end
  on their next request (within a minute on other API processes), Portal
  registration is closed, and the tenant's scheduled jobs wait. The Super
  Admin can still manage the tenant.
- **Who sees it.** The tenant's administrators see the license and the
  seats in use on the Users page. A Studio banner warns them 30 days
  before the end date and when every seat is in use. Each change is
  recorded in the tenant's audit log.
- **License files.** On the License tab, **Issue license file** downloads
  an encrypted `.lic` file with the terms in the form, without changing
  the tenant's license. The tenant's administrator applies it under
  **Users → License → Apply license file**, after seeing what it changes.
  If the license has already ended, nobody can sign in, so the sign-in
  page offers **Apply a license file** instead. Each file is encrypted and
  authenticated with a key derived from `WORKSPACE_ENCRYPTION_KEY`
  ([`app/crud/license_files.py`](app/crud/license_files.py)), so it can't
  be read, edited or forged. Each file is also:
  - only for its own tenant;
  - usable once;
  - unusable once a newer file has been applied;
  - revocable by the Super Admin until it's applied.

  Changing `WORKSPACE_ENCRYPTION_KEY` makes earlier files unreadable.
- **Renewal requests.** A tenant's administrator can request a renewal or
  more seats from the Studio banner or the Users page, with the seats and
  end date wanted and a message
  ([`app/crud/license_requests.py`](app/crud/license_requests.py)). Only
  one request can be open at a time. Requests wait under **License
  requests** in the Super Admin's sidebar, and on the tenant's License
  tab. The Super Admin either:
  - **issues a license** for the request, pre-filled with what was asked
    for — the requester is notified and applies it with **Apply now**;
  - **declines** it with a reason, which the requester sees.

  A tenant whose license has already ended can't sign in to make a
  request, which is why administrators are warned 30 days ahead.

### Background jobs

Notification deliveries (webhooks, emails), field-index and storage-table
builds, and scheduled runs go through a job queue in Postgres
([`app/core/jobs.py`](app/core/jobs.py)). A job is written in the same
transaction as the change that needs it, retried with backoff when it
fails, and handed to another worker if its worker dies. Administrators see
and retry failed jobs under Scheduled Jobs → Background jobs.

Each API process runs a worker and the scheduler. To run them separately,
set `RUN_JOBS=false` on the API and start as many workers as needed:

```bash
./.venv/Scripts/python.exe -m app.worker
```

`JOB_QUEUE` names the queue a process uses (default `default`). The test
suite uses its own queue, so a dev server on the same database never picks
up its jobs.

### Logs, metrics and tracing

- **Request IDs.** Every response carries `X-Request-ID`: the caller's own
  ID if it sent one, otherwise a new one. Every log line written during
  the request includes it, along with the tenant and user. A job's log
  lines include its job ID and kind. An unexpected error returns a 500
  whose message names the reference, and the web app shows that
  reference to the user.
- **Logs.** `LOG_FORMAT=json` writes one JSON object per line (the Docker
  image's default); `text` is the default for local development. Set the
  level with `LOG_LEVEL`. Each request gets one access line with its
  method, route, status and duration.
- **Health checks.** `/health` reports whether the process is alive.
  `/ready` reports whether it can serve: it checks the database and
  lists any pending migrations, and returns 503 when the database can't
  be reached.
- **Metrics.** `/metrics` exposes Prometheus metrics: requests by route
  and status, request latency, job runs by kind and outcome, jobs by
  queue and status, and how long the oldest due job has waited. Set
  `METRICS_TOKEN` and scrape with `Authorization: Bearer <token>`. In
  production `/metrics` stays off until a token is set.
  [`deploy/prometheus/alerts.yml`](../deploy/prometheus/alerts.yml) has
  starting-point alert rules.
- **Tracing.** Set `OTEL_EXPORTER_OTLP_ENDPOINT` (e.g.
  `http://otel-collector:4318`) and each request becomes an OpenTelemetry
  trace, with a span for every SQL statement and outbound HTTP call. JSON
  log lines then also carry the `trace_id`.

### Docker and CI

`docker compose up --build` at the repository root runs Postgres, the API
and the web app (nginx, proxying `/api` to the API) on
http://localhost:8080; the top of [`docker-compose.yml`](../docker-compose.yml)
lists the `.env` values it needs. The API image runs with
`ENVIRONMENT=production`, which refuses to start with the built-in dev
`JWT_SECRET` or `WORKSPACE_ENCRYPTION_KEY`, and runs `app.db_migrate deploy`
before starting (`MIGRATE_ON_START=false` to leave that to a release step).

[`.gitlab-ci.yml`](../.gitlab-ci.yml) runs the backend suite against a
fresh Postgres built from `schema.sql` (re-running every migration over it
first), the frontend type check, tests and build, and on the default branch
pushes both images to the project's container registry.

## Provisioning your first tenant

There is no `POST /tenants` — see the docstring in `app/routers/tenants.py`
for why. Bootstrap a tenant (and its 5 baseline roles + first Administrator
user, per FR-RBA-001) with:

```bash
./.venv/Scripts/python.exe scripts/provision_tenant.py \
    --tenant-name "Acme Corp" --admin-name "Alice Admin" --admin-email alice@acme-corp.com
```

Note the printed `tenant_id` and `admin_user_id` — every other request needs
them (see Authentication below).

## Running

```bash
./.venv/Scripts/uvicorn.exe app.main:app --reload --port 8000
```

Interactive docs: `http://localhost:8000/docs`.

## Testing the API — Postman collection

[`postman/FlowFabric.postman_collection.json`](postman/FlowFabric.postman_collection.json)
covers all 95 requests across every module, pre-wired with collection
variables and id-capture scripts so it runs end-to-end top-to-bottom against
a freshly provisioned tenant (verified via `newman`, Postman's CLI runner,
during development — 88/95 requests return 2xx on a clean run; the other 7
are documented, expected non-2xx: schema-dependent example bodies and two
deliberate "can't delete a published resource" guards). See
[`postman/README.md`](postman/README.md) for import instructions and how to
regenerate it after an API change.

## Authentication (read this before treating this as production-ready)

`app/core/security.py` is a **stub** standing in for the Keycloak
integration `FlowFabric_SRS.docx` (8.4) specifies. Every request must carry:

- `X-Tenant-Id` (required) — which tenant's data to operate on
- `X-User-Id` (optional, required by a few endpoints — e.g. starting an AI
  Assistant session) — attributed as the actor for audit-log entries,
  `created_by`, etc.
- `X-Vendor-Id` (optional) — set for Public Portal / vendor-scoped requests

Nothing here verifies these headers actually belong to the caller — Postgres
Row-Level Security (`db/schema.sql`) enforces that a request can only ever
touch the tenant/vendor named in its headers, but it does **not** enforce
that the headers are honest. Replace `get_auth_context` with real
Keycloak-issued JWT verification before exposing this outside a trusted
network.

## What's implemented vs. deliberately out of scope

Implemented: full CRUD across all 14 SRS modules; tenant/vendor Row-Level
Security wired through every request; versioned forms/workflows with
publish/new-version lifecycles; workflow instance start/advance with a
step-history audit trail; master-to-master `reference` fields (a Master
field can link live to another Master's records, single- or multi-select,
with the linked record id(s) validated against that Master's actual rows
on every write — see `app/crud/validation.py`'s `_check_reference` and
`app/routers/masters.py`'s delete guard for masters referenced by another
Master's schema); master bulk import (CSV/XLSX) with row-level
validation; form conditional-visibility simulation (FR-FRM-007); dashboard
role-based accessibility; report export to `.xlsx`; connector reachability
testing; an AI Assistant chat endpoint that calls a real self-hosted Ollama
instance (FR-AIA-006) and degrades gracefully per FR-AIA-008 if it's
unreachable; an audit log written internally by sensitive operations.

Not implemented (called out inline in the relevant router/comment, not
faked): PDF report export (FR-RPT-005 — the SRS's own recommended approach
needs a frontend report view to render via headless browser, which doesn't
exist yet); Temporal-backed durable workflow execution (8.4.1 — instances
here are plain rows, not backed by a durable execution engine); real
Keycloak auth; scheduled report emails (FR-RPT-006); WebSocket real-time
dashboard push (NFR-PERF-002); record-level referential integrity for
`reference` fields — deleting a *Master* another Master's schema links to
is blocked, but deleting one specific *record* that other records still
point at (e.g. one Country row while States reference it) is not, since
that requires scanning every other Master's records for a match rather
than just their schemas.

## Tests

`scripts/provision_tenant.py`'s output feeds an ad hoc smoke test that
exercised every router end-to-end against a live Postgres instance during
development (masters/bulk-import, workflow publish→instance→advance, form
publish→submission, menu role auto-sync, dashboard accessibility, report
export, connector test, AI Assistant error path, last-Administrator guard,
FR-MST-007 delete guard). It isn't checked in as a permanent test suite —
consider adding pytest + pytest-asyncio + a transactional-rollback fixture
around a real Postgres (or testcontainers) as a follow-up.