Skip to content

database.md

Latest commit

 

History

History
141 lines (108 loc) · 7.18 KB

database.md

File metadata and controls

141 lines (108 loc) · 7.18 KB
Raw

Database Schema

FlowOS persists domain state through repository adapters. This document covers the SQL schema, how it maps to the domain layer, and the migration workflow.

Repository adapters

Every repository has two implementations selected by the composition root:

  • In-memory (flowforge.infrastructure.repositories) — used in the development environment; the API runs without any database server.
  • SQLAlchemy (flowforge.infrastructure.persistence) — used whenever FLOWOS_ENV is anything other than development; backed by PostgreSQL in production and a local SQLite file for SQL-backed local runs.

Rows are dumb storage. Every value stored in a column is JSON-safe or a primitive, and mapping to/from the immutable domain objects happens in the repository adapters — never in the domain layer. Datetimes are stored in UTC; SQLite does not persist the timezone offset, so repositories normalize naive values back to aware ones (flowforge.infrastructure.db.ensure_utc).

Tables

The initial migration (bee63bd5c72b — "initial schema") creates five tables.

workflows

Stores workflow definitions. The graph (nodes + edges) is serialized into the graph JSON column; there is no relational node/edge normalization.

Column Type Nullable Notes
seq integer PK no Auto-increment surrogate key
id uuid UNIQUE no Stable workflow id (domain WorkflowId)
name varchar(255) no
version integer no Monotonic revision
description text yes
webhook_secret text yes Shared secret for the public webhook; never serialized in API responses
graph json no Serialized nodes and edges
created_at datetime(tz) no UTC

executions

Execution history. The summary columns are the store of record for history and read APIs; the checkpoint column additionally holds the latest serialized ExecutionState frontier for restart-safe resume (captured after each completed batch while the execution is running).

Column Type Nullable Notes
execution_id uuid PK no ExecutionId
workflow_id uuid INDEX no Filter by workflow
status varchar(32) no pending | running | paused | succeeded | failed | cancelled
started_at datetime(tz) no UTC
finished_at datetime(tz) yes Set on terminal statuses
error json yes {kind, message, node_id, attempt}
checkpoint json yes Versioned ExecutionState envelope for resume; set while running

users

Column Type Nullable Notes
id uuid PK no UserId
email varchar(320) UNIQUE INDEX no Normalized (lowercase/trimmed)
password_hash text no Argon2id encoded hash (legacy PBKDF2 keeps verifying)
roles json no ["viewer", "operator", "admin"]
is_active boolean no Deactivated users are denied every permission

credentials

Column Type Nullable Notes
credential_id uuid PK no CredentialId
name varchar(255) no
credential_type varchar(32) no api_key | bearer | basic
payload json no Fernet-encrypted envelope at rest in the SQL adapter
created_at datetime(tz) no UTC
updated_at datetime(tz) no UTC

The SQL adapter encrypts the payload with FernetSecretCipher keyed by FLOWOS_SECRET_ENCRYPTION_KEY before writing, and decrypts on read (failing closed on tampering or key mismatch). The in-memory adapter stores payloads directly. The API masks values on every response either way.

audit_events

An append-only security audit trail.

Column Type Nullable Notes
audit_id uuid PK no AuditEventId
actor_id uuid INDEX yes Anonymous actions (login failures, webhooks) have none
action varchar(64) no e.g. login, credential.create, webhook.trigger, access.denied, rate.limited
resource_type varchar(64) no e.g. workflow, credential, auth, permission
resource_id varchar(255) yes
outcome varchar(32) no success | denied | failure
detail json no Structured context (never secret values)
created_at datetime(tz) no UTC

Relationship map

users ─── 1─* audit_events (actor_id, nullable for anonymous)
workflows ───1─* executions (workflow_id)
workflows 1─0..1 webhook_secret (column, no separate table)
credentials ── (payload encrypted JSON)

There are no foreign-key constraints in the initial migration; associations are by UUID convention (workflows/executions, users/audit_events). This keeps the tables portable and the rows decoupled from the domain aggregates.

Migrations

Migrations live in backend/migrations and use Alembic:

  • env.py uses the async SQLAlchemy engine, so one environment serves local SQLite (aiosqlite) and production PostgreSQL (asyncpg). The database URL is resolved through flowforge.infrastructure.db.database_url — a single source of truth shared with the application.
  • versions/bee63bd5c72b_initial_schema.py is the current head.

Run migrations against a configured database:

uv run --package flowforge alembic upgrade head

Generate a new migration after changing flowforge.infrastructure.models:

uv run --package flowforge alembic revision --autogenerate -m "describe change"

Working without the database

In the development environment no database is required at all: create_app wires the in-memory repositories, and the API runs entirely in memory (note: restarts lose data). Set FLOWOS_ENV to any other value plus FLOWOS_DATABASE_URL / FLOWOS_REDIS_URL / FLOWOS_SECRET_ENCRYPTION_KEY to use the SQL + Redis stack (see deployment.md).