Skip to content

database-schema.md

Latest commit

 

History

History
251 lines (199 loc) · 10.3 KB

database-schema.md

File metadata and controls

251 lines (199 loc) · 10.3 KB

Database Schema Reference

Status: active

Authoritative reference for the Powernode PostgreSQL schema: model namespaces, key tables, conventions.

Table of Contents

Overview

The Powernode backend uses PostgreSQL (with pgvector for embeddings) and UUIDv7 primary keys across all tables. Models live under server/app/models/ in 13 namespace directories that reflect feature domains (account, ai, chat, database, data_management, devops, file_management, knowledge_base, mcp, monitoring, review, shared, supply_chain), alongside the top-level core models. This reference catalogues each namespace, summarises the tables per area, and documents the schema conventions that controllers, migrations, and seeds must follow.

Model Namespaces

Top-Level Models

Core platform models not in a namespace.

Model Description
User Platform users with authentication and permissions
Account Multi-tenant account (one per organisation)
Role Permission grouping (system.admin, account.manager, etc.)
Permission Individual permission entry — see permissions.md
RolePermission Role-to-permission join table
Plan Subscription plan with features/limits (extension-gated)
Subscription Account subscription, AASM state machine with 8 states (extension-gated)
Invoice Billing invoices with line items (extension-gated)
Payment Payment records (extension-gated)
Invitation User invitations with email workflow
AuditLog Comprehensive activity tracking
Notification User notifications
ApiKey / ApiKeyUsage API key management and usage tracking
AdminSetting System configuration key-value store
BlacklistedToken / JwtBlacklist Token revocation
PasswordHistory Password reuse prevention
Page CMS content pages
OauthApplication OAuth2 provider applications
ImpersonationSession Admin impersonation tracking
McpServer / McpTool / McpSession / McpToolExecution MCP protocol infrastructure
CommunityAgent / CommunityAgentRating / CommunityAgentReport Agent marketplace
ExternalAgent / FederationPartner A2A external agents
ReportRequest Async report generation
EmailDelivery Email delivery tracking
BackgroundJob Job status tracking

Ai:: Namespace

The largest namespace — covers the entire AI platform.

Area Examples
Agents Agent, AgentExecution, AgentExecutionStep, AgentCapability, AgentConfiguration
Teams AgentTeam, AgentTeamMember, TeamExecution, TeamChannel, TeamMessage
Missions Mission, MissionApproval
Ralph RalphLoop, RalphTask, RalphIteration, RalphLearning
Providers Provider, ProviderModel, ModelRoutingRule, ProviderHealthCheck
Data Sources DataSource, DataSourceCredential
Knowledge KnowledgeGraphNode, KnowledgeGraphEdge, CompoundLearning, SharedKnowledge
Memory MemoryEntry, MemoryPool, ContextEntry, ContextGroup, AgentShortTermMemory
Skills Skill, SkillExecution, AgentSkill, SkillMutation, SkillChallenge
Tools Tool, ToolExecution, ToolCategory
Code Factory CodeFactoryRun, CodeFactoryContract, CodeFactoryTask, RiskContract, ReviewState
Autonomy AgentTrustScore, DelegationPolicy, AgentPrivilegePolicy, BehavioralFingerprint, KillSwitchEvent, AgentGoal, AgentObservation, AgentProposal, AgentEscalation, InterventionPolicy, AgentFeedback
Conversations Conversation, Message, Attachment
Monitoring CostTracking, UsageMetric, BudgetAlert
Templates SystemPromptTemplate
Trust & Safety TrustScore, Guardrail, GuardrailEvaluation
Worktree Worktree, WorktreeSession, WorktreeEvent

Devops:: Namespace

Area Examples
Pipelines Pipeline, PipelineRun, PipelineStep, PipelineStepExecution
Git GitProvider, GitRepository, GitRunner, GitPipelineJob
Docker DockerHost, DockerContainer, SwarmService, SwarmStack
Deployments Deployment, DeploymentTarget, DeploymentEnvironment
Templates IntegrationTemplate, ContainerTemplate

KnowledgeBase:: Namespace

Model Description
Article KB articles with Markdown content
Category Article categorisation
Tag Article tagging
ArticleTag Article-to-tag join
Comment Article comments
Attachment Article file attachments
ArticleView View tracking
Workflow Article workflow states

Chat:: Namespace

Model Description
Conversation Chat conversation container
Message Individual messages
Attachment Message attachments
Session Active chat sessions
Participant Conversation participants

FileManagement:: Namespace

Model Description
FileUpload Uploaded file records
StorageBackend Storage provider configuration
FileVersion File versioning
FileShare Shared file access
VirusScanResult Antivirus scan results
FileQuota Storage quota tracking
FileAuditLog File access audit trail

Account:: Namespace

Model Description
Delegation Cross-account access delegation
Setting Account-specific settings
Feature Account feature flags

DataManagement:: Namespace

Model Description
RetentionPolicy Data retention rules
SanitizationRule PII sanitisation
DataExport GDPR data export records

Database:: Namespace

Model Description
Connection External database connections
QueryHistory Query execution history

Monitoring:: Namespace

Model Description
HealthCheck Service health check records
ServiceStatus Service availability status

Shared:: Namespace

Model Description
FeatureGate Extension feature gating

Review:: Namespace

App-marketplace review and moderation models.

Model Description
Response Review/response records
HelpfulnessVote Helpfulness voting on responses
ModerationAction Moderation decisions
MediaAttachment Review media attachments
Notification Review notifications
NotificationDelivery Notification delivery tracking
NotificationPreference Per-user notification preferences

SupplyChain:: Namespace

SBOM, attestation, and vulnerability models (active when the supply-chain extension is loaded).

Area Examples
SBOM Sbom, SbomComponent, SbomDiff, SbomVulnerability
Scanning VulnerabilityScan, ScanTemplate, ScanExecution, ScanInstance, VulnerabilityFeed
Provenance & Signing BuildProvenance, Attestation, Attribution, SigningKey, VerificationLog
Images & Policy ContainerImage, ImagePolicy, License, LicensePolicy, LicenseDetection, LicenseViolation
Vendor & Risk Vendor, VendorMonitoringEvent, CveMonitor, RiskAssessment, RemediationPlan, Report
Questionnaires QuestionnaireTemplate, QuestionnaireResponse

Mcp:: Namespace

MCP protocol principal records. (The MCP server/tool/session/execution models listed under Top-Level Models above are not namespaced; only Principal lives under Mcp::.)

Model Description
Principal MCP authentication principal (token-bearing caller identity)

Key Relationships

flowchart LR
  Account --> User
  User --> Role
  Role --> Permission
  Account --> Subscription
  Subscription --> Plan
  Account --> AiAgent[Ai::Agent]
  AiAgent --> AiAgentExecution[Ai::AgentExecution]
  Account --> AiMission[Ai::Mission]
  AiMission --> AiMissionApproval[Ai::MissionApproval]
  Account --> AiRalphLoop[Ai::RalphLoop]
  AiRalphLoop --> AiRalphTask[Ai::RalphTask]
  Account --> AiAgentTeam[Ai::AgentTeam]
  AiAgentTeam --> AiAgentTeamMember[Ai::AgentTeamMember]
  Account --> AiKnowledgeGraphNode[Ai::KnowledgeGraphNode]
  AiKnowledgeGraphNode --> AiKnowledgeGraphEdge[Ai::KnowledgeGraphEdge]
  Account --> DevopsPipeline[Devops::Pipeline]
  DevopsPipeline --> DevopsPipelineRun[Devops::PipelineRun]
  Account --> Invoice
  Invoice --> Payment
Loading

Database Conventions

Convention Detail
Primary keys UUIDv7 (chronologically sortable) — generate via UUID7.generate
Foreign keys t.references with type :uuid (index included automatically — never add_index separately)
Namespaced FK prefix Ai:: → ai_, Devops:: → devops_, BaaS:: → baas_
Timestamps created_at, updated_at on all tables
Soft delete discarded_at on applicable models (Discard gem)
JSON columns Lambda defaults — attribute :config, :json, default: -> { {} }
Vectors pgvector with HNSW indexes for embedding columns

Association requirements:

  • Always pair class_name: with foreign_key: — e.g. belongs_to :provider, class_name: "Ai::Provider", foreign_key: "ai_provider_id".
  • All namespaced models MUST use :: separator in class_name: — e.g. class_name: "Ai::AgentTeam", never "AiAgentTeam".

Migration patterns:

  • t.references automatically creates an index; never add add_index for reference columns. Customise via the declaration itself: t.references :account, index: { unique: true }.

After modifying seeds, run cd server && rails db:seed and verify completion.

Related docs

Materials previously at

  • docs/backend/DATABASE_SCHEMA_REFERENCE.md

Last verified: 2026-06-03