Database Schema (Supabase)
Relevant source files
The following files were used as context for generating this wiki page:
The Supabase database schema serves as the primary persistence layer for the NeXifyAI platform's autonomous operations, governance, and multi-tenant isolation. It integrates PostgreSQL tables, Row Level Security (RLS) policies, and specialized modules for task orchestration (Oracle), secret management, and billing tracking. The schema enables a "Brain-First" architecture where agents query and update operational memory to maintain system-wide consistency.
Sources: SETUP.md:123-128, services/api/agents/brain_agents/supabase-schema-architect.md:6-12
Core Task Orchestration (Oracle)
The oracle_tasks table acts as the central ledger for the autonomous execution engine. It tracks the full lifecycle of tasks from recognition to validation, including metadata for loop reasoning and evidence collection.
Task Status Lifecycle
Tasks progress through a granular state machine to ensure auditability and quality control:
- Initial:
erkannt(recognized) ->eingeplant(scheduled) - Execution:
gestartet(started) ->in_bearbeitung(in progress) - Wait States:
wartet_auf_input,wartet_auf_freigabe,in_loop - Terminal:
erfolgreich_validiert,fehlgeschlagen,blockiert,eskaliert
Sources: services/api/services/oracle_engine.py:21-47, SETUP.md:139-144
Schema Definition: oracle_tasks
| Column | Type | Description |
|---|---|---|
id | UUID | Primary key for the task. |
type | VARCHAR | Task category (e.g., infrastructure, security). |
status | VARCHAR(50) | Current lifecycle state. |
priority | INT | Execution priority level. |
evidence | JSONB | Data collected during task execution/validation. |
status_history | JSONB | Audit trail of every state transition. |
loop_count | INT | Number of times a task re-entered execution loops. |
verification_score | NUMERIC | Quality score assigned by the verifier agent. |
Sources: SETUP.md:130-137, services/api/services/oracle_engine.py:108-164
Secrets Vault and Governance
The platform implements a centralized credential management system using the secrets_vault and project_secrets tables. This structure ensures that sensitive API keys, certificates, and tokens are isolated by environment and tenant.
Secret Management Structure
The diagram shows the relationship between core service definitions and encrypted credentials.
Sources: supabase/migrations/015_secrets_vault.sql:10-74
Data Models
secrets_vault: Stores encrypted credentials for platform-wide services. Includes rotation periods and environment flags (production,staging,development).project_secrets: Provides tenant-specific isolation for secrets required by individual customer projects.rules_registry: Defines autonomous agent behavior rules (category:always,never,when).
Sources: supabase/migrations/015_secrets_vault.sql:35-46, supabase/migrations/014_orchestrator_v3_tables.sql:10-29
Billing and Cost Tracking
NeXifyAI tracks net costs, markups, and gross totals for billable AI operations via the cost_tracking table.
| Field | Type | Constraint | Description |
|---|---|---|---|
project_id | UUID | FK -> customer_projects | The tenant associated with the cost. |
cost_net | NUMERIC | NOT NULL | Internal cost paid by the platform. |
cost_markup | NUMERIC | NOT NULL | Calculated markup (default 20%). |
cost_gross | NUMERIC | NOT NULL | Final cost billed to the customer. |
usage_unit | TEXT | Units such as tokens, requests, or gb. |
Sources: supabase/migrations/015_secrets_vault.sql:87-101
Multi-Tenant Security (RLS)
The schema enforces tenant isolation through Row Level Security (RLS). All tables include a tenant_id or project_id to filter access based on the authenticated user's session.
Access Control Logic
The flowchart describes how RLS policies differentiate between administrative staff and tenant users.
Sources: services/api/agents/brain_agents/supabase-schema-architect.md:32-36, supabase/migrations/015_secrets_vault.sql:127-152
Orchestrator V3 and Task Graphs
The schema supports complex multi-step agent pipelines using a task graph architecture.
team_registry: Maps agents to specific capabilities likeanalyze,build, orcompliance.task_graph: Defines sequences of steps (sequential,parallel,dag) that agents must follow to complete complex workflows.agent_metrics: Records performance data, includinglatency,success_rate, andbrain_hits.
Sources: supabase/migrations/014_orchestrator_v3_tables.sql:42-88
Summary
The Supabase database schema provides the foundation for NeXifyAI's autonomous operations. By combining the Oracle task engine, secure secret vaulting, and granular billing tracking within a multi-tenant RLS framework, the system maintains high levels of auditability and security while enabling agents to operate with shared operational memory.
Sources: services/api/agents/brain_agents/supabase-schema-architect.md:1-5, services/api/services/oracle_engine.py:5-15