Database Architecture

The data layer in sfp server provides durable state storage, identity management, and real-time change propagation. The platform requires a Postgres-compatible database for persistent state (configuration, history, audit records, and identity) plus a mechanism to stream state changes to clients without polling.

Data Layer Components

The reference deployment uses Supabase, which bundles PostgreSQL with authentication services, row-level security, and a real-time engine. However, the architecture does not strictly require Supabase; any Postgres deployment that provides equivalent capabilities (auth primitives, RLS, and change subscriptions) can serve as the backing store.

The auth system provides token and session primitives used by the API layer. PostgREST exposes a REST interface over the database tables, while row-level security policies enforce authorization boundaries within the data store itself. The real-time engine tracks table changes and pushes updates to subscribed clients.

Instance Isolation

Each organization receives its own dedicated database instance. This architectural decision provides complete data separation at the infrastructure level rather than relying solely on application-level tenant filtering.

This isolation ensures that each organization's data remains completely separate, performance and scaling can be managed independently, security boundaries are enforced at the infrastructure level, and organizations maintain control over their data governance and retention policies.

Real-time State Management

The platform treats state as a first-class runtime concern. Operational state changes (task progress, environment allocation, deployment status) are persisted to the database and simultaneously propagated to clients as they occur.

Workers write state updates to the database during task execution. The real-time engine detects these changes and pushes notifications to the API layer, which forwards them to subscribed WebSocket clients. This keeps the API, workers, and clients consistent without requiring clients to poll for updates.

The subscription model supports filtering by resource type and identifier, allowing clients to receive updates only for the specific tasks or resources they are interested in. This reduces network traffic and client-side processing compared to broadcasting all changes to all connected clients.

State Categories

The system manages three categories of state with different storage and access patterns.

Operational state encompasses long-term records including deployment histories, configuration data, and audit logs. This state is stored in PostgreSQL with full transactional guarantees and is accessed through the PostgREST API with row-level security enforcement.

Resource state tracks current allocations such as active environments, running tasks, and provisioned resources. This state is maintained in Redis for high-performance access and is synchronized to PostgreSQL for durability. Redis provides the low-latency reads required for real-time coordination, while PostgreSQL ensures state survives infrastructure restarts.

Task state covers the progress and status of active operations. Current task status is maintained in Redis to enable real-time updates through WebSocket connections. Upon task completion, the final state and execution history are persisted to PostgreSQL for audit and historical analysis.

Self-hosting Considerations

Self-hosted deployments run the same logical architecture but differ in how the backing services are operated. The key requirement is that the data layer (if need to be seperated) is reachable from the instance and configured with appropriate tenant isolation.

Organizations operating self-hosted deployments typically run their own Supabase stack or an equivalent set of services. They apply their own backup schedules, retention policies, and observability standards according to their operational requirements. The application layer connects to the data layer through standard PostgreSQL and HTTP protocols, allowing flexibility in how the underlying infrastructure is provisioned and managed.