Database Schema Reference

Agentcy uses three databases, each serving a distinct purpose:

Database	Purpose	Library
PostgreSQL	Relational data -- users, organizations, configurations, audit logs	sqlx 0.7
Neo4j	Knowledge graph -- nodes, relationships, vector embeddings	neo4rs 0.7
Redis	Caching, job queues, real-time streams	redis 0.25

PostgreSQL Tables

All tables use UUID primary keys (via gen_random_uuid()), TIMESTAMPTZ timestamps, and updated_at auto-update triggers.

organizations

Tenants in the multi-tenant system. Each organization has isolated data.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Organization identifier
`name`	VARCHAR(255)	—	Unique slug-style name
`display_name`	VARCHAR(255)	—	Human-readable name
`settings`	JSONB	`{}`	Organization settings (LLM config, features)
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

users

User accounts within an organization.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	User identifier
`organization_id`	UUID (FK)	—	Parent organization
`external_id`	VARCHAR(255)	—	External identity (e.g., OIDC `sub` claim)
`email`	VARCHAR(255)	—	Email address
`name`	VARCHAR(255)	—	Display name
`roles`	TEXT[]	`{}`	Role assignments (e.g., `["admin"]`)
`permissions`	TEXT[]	`{}`	Direct permission grants
`password_hash`	VARCHAR(255)	—	Bcrypt hash (local auth only)
`auth_provider`	VARCHAR(50)	`'local'`	Auth provider (`local` or `oidc`)
`email_verified`	BOOLEAN	`false`	Whether email has been verified
`last_login_at`	TIMESTAMPTZ	—	Last successful login
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

Unique constraint: (organization_id, external_id).

api_keys

API keys for programmatic access.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Key identifier
`organization_id`	UUID (FK)	—	Parent organization
`user_id`	UUID (FK)	—	Owner user
`name`	VARCHAR(255)	—	Descriptive name
`key_hash`	VARCHAR(512)	—	SHA-256 hash of the API key
`permissions`	TEXT[]	`{}`	Scoped permissions
`active`	BOOLEAN	`true`	Whether the key is active
`expires_at`	TIMESTAMPTZ	—	Optional expiration
`last_used_at`	TIMESTAMPTZ	—	Last usage timestamp
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp

source_configs

Data source connector configurations.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Source identifier
`organization_id`	UUID (FK)	—	Parent organization
`source_type`	VARCHAR(100)	—	Connector type (e.g., `github_pat`, `aws`, `sql`)
`name`	VARCHAR(255)	—	Display name
`config`	JSONB	`{}`	Connector-specific configuration (encrypted at rest)
`schedule`	VARCHAR(255)	—	Cron expression for automated syncs
`enabled`	BOOLEAN	`true`	Whether sync is enabled
`last_sync_at`	TIMESTAMPTZ	—	Last sync timestamp
`last_sync_status`	VARCHAR(50)	—	Last sync result (`success`, `error`)
`last_sync_error`	TEXT	—	Error message from last failed sync
`node_count`	BIGINT	`0`	Nodes created by this source
`relationship_count`	BIGINT	`0`	Relationships created by this source
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

ingestion_jobs

Background ingestion job tracking.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Job identifier
`organization_id`	UUID (FK)	—	Parent organization
`source_config_id`	UUID (FK)	—	Source that triggered the job
`source_type`	VARCHAR(100)	—	Source type (denormalized)
`status`	VARCHAR(50)	`'pending'`	Job status (`pending`, `running`, `completed`, `failed`, `cancelled`)
`config`	JSONB	`{}`	Job-specific configuration
`run_id`	VARCHAR(255)	—	Unique run identifier
`progress`	FLOAT	`0`	Completion progress (0.0 to 1.0)
`phase`	VARCHAR(50)	—	Current phase (e.g., `extracting`, `transforming`, `loading`)
`records_processed`	BIGINT	`0`	Total records processed
`nodes_created`	BIGINT	`0`	Graph nodes created
`relationships_created`	BIGINT	`0`	Graph relationships created
`nodes_cleaned`	BIGINT	`0`	Stale nodes removed
`error`	TEXT	—	Error message (if failed)
`worker_id`	VARCHAR(255)	—	Worker that executed the job
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`started_at`	TIMESTAMPTZ	—	Execution start time
`completed_at`	TIMESTAMPTZ	—	Execution end time
`duration_ms`	BIGINT	—	Total execution duration

conversations

Chat conversation sessions.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Conversation identifier
`organization_id`	UUID (FK)	—	Parent organization
`user_id`	UUID (FK)	—	Owner user
`title`	VARCHAR(500)	—	Conversation title
`model`	VARCHAR(100)	—	LLM model used
`message_count`	INT	`0`	Total messages in conversation
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last activity timestamp

messages

Individual chat messages within conversations.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Message identifier
`conversation_id`	UUID (FK)	—	Parent conversation
`role`	VARCHAR(20)	—	Message role: `system`, `user`, `assistant`, `tool`
`content`	TEXT	—	Message content
`tool_calls`	JSONB	—	Tool call definitions (for assistant messages)
`tool_call_id`	VARCHAR(255)	—	Tool call ID (for tool result messages)
`token_count`	INT	—	Token count for billing/tracking
`model`	VARCHAR(100)	—	Model that generated the message
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp

agent_configs

Autonomous agent configuration.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Agent identifier
`organization_id`	UUID (FK)	—	Parent organization
`agent_type`	VARCHAR(100)	—	Agent type (e.g., `schema_analyzer`, `dependency_mapper`)
`name`	VARCHAR(255)	—	Display name
`config`	JSONB	`{}`	Agent-specific configuration
`schedule`	JSONB	`{"type": "oneshot"}`	Schedule configuration
`enabled`	BOOLEAN	`true`	Whether the agent is active
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

agent_runs

Agent execution history.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Run identifier
`agent_config_id`	UUID (FK)	—	Parent agent
`organization_id`	UUID	—	Organization (denormalized)
`status`	VARCHAR(50)	`'running'`	Run status
`nodes_created`	BIGINT	`0`	Nodes created during run
`nodes_updated`	BIGINT	`0`	Nodes updated during run
`relationships_created`	BIGINT	`0`	Relationships created
`insights`	JSONB	`[]`	Generated insights
`error`	TEXT	—	Error (if failed)
`started_at`	TIMESTAMPTZ	`NOW()`	Start time
`completed_at`	TIMESTAMPTZ	—	Completion time

sub_agents

OpenFang sub-agent registrations.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Sub-agent identifier
`organization_id`	UUID (FK)	—	Parent organization
`openfang_agent_id`	VARCHAR(255)	—	Remote agent ID in the orchestration engine
`name`	VARCHAR(255)	—	Display name
`description`	TEXT	—	Description
`template`	VARCHAR(100)	—	Template used to create the agent
`config`	JSONB	`{}`	Agent configuration
`gateway_id`	UUID (FK)	—	Orchestration gateway
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

sub_agent_workflows

Workflow definitions for the orchestration engine.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Workflow identifier
`organization_id`	UUID (FK)	—	Parent organization
`openfang_workflow_id`	VARCHAR(255)	—	Remote workflow ID
`name`	VARCHAR(255)	—	Workflow name
`description`	TEXT	—	Description
`steps`	JSONB	`[]`	Workflow step definitions
`gateway_id`	UUID (FK)	—	Orchestration gateway
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

sub_agent_triggers

Event triggers that start agents or workflows.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Trigger identifier
`organization_id`	UUID (FK)	—	Parent organization
`openfang_trigger_id`	VARCHAR(255)	—	Remote trigger ID
`agent_id`	UUID (FK)	—	Target agent (nullable)
`pattern`	JSONB	—	Trigger pattern (schedule, webhook, lifecycle)
`prompt_template`	TEXT	—	Prompt template for the triggered action
`enabled`	BOOLEAN	`true`	Whether the trigger is active
`gateway_id`	UUID (FK)	—	Orchestration gateway
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp

orchestration_gateways

Orchestration engine connections.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Gateway identifier
`organization_id`	UUID (FK)	—	Parent organization
`name`	VARCHAR(255)	—	Display name
`engine_type`	VARCHAR(50)	`'openfang'`	Engine type: `openfang`, `langchain`, `crewai`, `custom`
`base_url`	TEXT	—	Engine base URL
`api_key`	TEXT	—	Authentication key
`context`	JSONB	`{}`	Arbitrary configuration
`is_default`	BOOLEAN	`false`	Default gateway for the organization
`is_active`	BOOLEAN	`true`	Whether the gateway is active
`last_health_check`	TIMESTAMPTZ	—	Last health check time
`last_health_status`	VARCHAR(50)	—	Health status: `ok`, `error`, `unreachable`
`worker_token_id`	UUID (FK)	—	Linked worker token
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

Unique index: one default gateway per organization.

worker_tokens

Pre-created tokens for worker registration.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Token identifier
`organization_id`	UUID (FK)	—	Parent organization
`name`	VARCHAR(255)	—	Token name
`token_hash`	VARCHAR(255)	—	SHA-256 hash (unique)
`labels`	JSONB	`{}`	Worker labels
`capabilities`	TEXT[]	`["execute", "ingest"]`	Allowed capabilities
`max_concurrent_tasks`	INTEGER	`4`	Concurrency limit
`created_by`	VARCHAR(255)	—	Creating user
`last_used_at`	TIMESTAMPTZ	—	Last usage
`revoked_at`	TIMESTAMPTZ	—	Revocation timestamp
`worker_id`	UUID	—	Registered worker ID
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp

role_definitions

RBAC role definitions for the zero-trust system.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Role identifier
`organization_id`	UUID (FK)	—	Parent organization
`name`	VARCHAR(100)	—	Role name (unique per org)
`description`	TEXT	—	Human-readable description
`permissions`	TEXT[]	`{}`	Granted permissions
`is_builtin`	BOOLEAN	`false`	Whether this is a system-defined role
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

Built-in roles: admin, editor, viewer, operator.

policy_sources

OPA policy sources -- git repositories or inline policy containers.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Source identifier
`organization_id`	UUID (FK)	—	Parent organization
`name`	VARCHAR(255)	—	Source name
`source_type`	VARCHAR(50)	—	`git_repo` or `inline`
`git_url`	TEXT	—	Git repository URL
`git_branch`	VARCHAR(255)	`'main'`	Branch to sync
`git_path`	VARCHAR(500)	`'/'`	Path within repo
`git_ssh_key_encrypted`	TEXT	—	Encrypted SSH key for private repos
`last_synced_at`	TIMESTAMPTZ	—	Last sync timestamp
`sync_status`	VARCHAR(50)	`'never'`	Sync status
`sync_error`	TEXT	—	Last sync error
`enabled`	BOOLEAN	`true`	Whether the source is active
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

policies

Individual OPA/Rego policy rules.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Policy identifier
`organization_id`	UUID (FK)	—	Parent organization
`source_id`	UUID (FK)	—	Parent policy source (nullable for inline)
`name`	VARCHAR(255)	—	Policy name
`description`	TEXT	—	Description
`package_path`	VARCHAR(500)	—	Rego package path (e.g., `agentcy.connectors.sql`)
`rego_code`	TEXT	—	Rego source code
`enabled`	BOOLEAN	`true`	Whether the policy is active
`file_path`	VARCHAR(500)	—	Original file path (for git sources)
`created_at`	TIMESTAMPTZ	`NOW()`	Creation timestamp
`updated_at`	TIMESTAMPTZ	`NOW()`	Last update timestamp

policy_audit_log

Audit trail for all policy evaluations.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Audit entry identifier
`organization_id`	UUID	—	Organization
`user_id`	VARCHAR(255)	—	User who triggered the action
`action`	VARCHAR(255)	—	Action attempted
`resource_type`	VARCHAR(100)	—	Resource type
`resource_id`	VARCHAR(255)	—	Resource identifier
`decision`	VARCHAR(20)	—	`allow` or `deny`
`matched_policy`	VARCHAR(255)	—	Policy that matched (if denied)
`reason`	TEXT	—	Human-readable reason
`input_snapshot`	JSONB	—	Full policy input for forensics
`created_at`	TIMESTAMPTZ	`NOW()`	Timestamp

audit_log

General system audit log.

Column	Type	Default	Description
`id`	UUID (PK)	`gen_random_uuid()`	Entry identifier
`organization_id`	UUID	—	Organization
`user_id`	UUID	—	User who performed the action
`action`	VARCHAR(100)	—	Action performed
`resource_type`	VARCHAR(100)	—	Target resource type
`resource_id`	VARCHAR(255)	—	Target resource ID
`details`	JSONB	`{}`	Additional details
`ip_address`	VARCHAR(45)	—	Client IP address
`created_at`	TIMESTAMPTZ	`NOW()`	Timestamp

Neo4j Graph Model

The knowledge graph stores ingested data from all connectors. Node labels and relationship types depend on the connected sources.

Common Node Labels

Label	Source	Description
`Repository`	GitHub	Git repository
`PullRequest`	GitHub	Pull request
`Issue`	GitHub	Issue
`GitHubUser`	GitHub	GitHub user account
`Branch`	GitHub	Git branch
`Commit`	GitHub	Git commit
`EC2Instance`	AWS	EC2 virtual machine
`S3Bucket`	AWS	S3 storage bucket
`SecurityGroup`	AWS	EC2 security group
`IAMRole`	AWS	IAM role
`VPC`	AWS	Virtual private cloud
`Lambda`	AWS	Lambda function
`RDSInstance`	AWS	RDS database instance
`GCEInstance`	GCP	Compute Engine instance
`GKECluster`	GCP	Kubernetes Engine cluster
`CloudSQLInstance`	GCP	Cloud SQL instance
`Pod`	Kubernetes	Kubernetes pod
`Service`	Kubernetes	Kubernetes service
`Deployment`	Kubernetes	Kubernetes deployment
`Namespace`	Kubernetes	Kubernetes namespace
`ConfigMap`	Kubernetes	Kubernetes config map
`Table`	SQL	Database table
`Column`	SQL	Table column
`ForeignKey`	SQL	Foreign key constraint
`Index`	SQL	Table index
`Collection`	MongoDB	MongoDB collection
`Field`	MongoDB	Document field
`APIEndpoint`	OpenAPI	REST API endpoint
`VercelProject`	Vercel	Vercel project
`SupabaseProject`	Supabase	Supabase project

Common Relationship Types

Relationship	Description
`OWNED_BY`	Ownership (repo by org, bucket by account)
`PR_OF`	Pull request belongs to repository
`ISSUE_OF`	Issue belongs to repository
`BRANCH_OF`	Branch belongs to repository
`COMMITTED_TO`	Commit on a branch
`AUTHORED_BY`	Authorship (PR, commit, issue)
`MEMBER_OF`	User membership in organization
`IN_VPC`	Resource resides in a VPC
`IN_SECURITY_GROUP`	EC2 instance in a security group
`IN_NAMESPACE`	Kubernetes resource in a namespace
`PART_OF`	Pod part of deployment
`EXPOSES`	Service exposes a port
`CONNECTS_TO`	Network connection between resources
`COLUMN_OF`	Column belongs to table
`REFERENCES`	Foreign key references another table
`INDEX_OF`	Index on a table
`FIELD_OF`	Field belongs to collection
`IN_SCHEMA`	Table/view in a database schema
`IN_DATABASE`	Schema in a database
`DEPENDS_ON`	Dependency relationship
`USES`	Resource uses another resource (e.g., Lambda uses IAM role)

Node Properties

All nodes include these common properties:

Property	Type	Description
`_id`	String	Internal node identifier
`_source_id`	String	Source connector that created this node
`_source_type`	String	Source type (e.g., `aws`, `github_pat`)
`_organization_id`	String	Owning organization
`_ingested_at`	DateTime	When the node was created/updated
`_labels`	List[String]	Neo4j labels

Vector Indexes

When RAG is initialized, a vector index is created for semantic search:

Index Name	Dimensions	Similarity	Description
`node_embeddings`	1536 (OpenAI) or 1024 (Anthropic)	Cosine	Vector embeddings of node descriptions

Initialize the index:

bash

curl -X POST http://localhost:18080/api/v1/rag/index/initialize \
  -H "Authorization: Bearer $TOKEN"

Redis Key Patterns

Redis is used for caching, job coordination, and real-time event streaming.

Key Patterns

Pattern	Type	TTL	Description
`job:{job_id}`	Hash	24h	Job status and metadata
`job:{job_id}:progress`	String	24h	Job progress percentage
`source:{source_id}:lock`	String	5m	Sync lock to prevent concurrent syncs
`source:{source_id}:status`	Hash	—	Current source sync status
`worker:{worker_id}`	Hash	—	Worker registration and heartbeat data
`worker:{worker_id}:tasks`	List	—	Pending tasks for a worker
`session:{session_id}`	Hash	1h	Chat session state
`cache:graph:stats:{org_id}`	String	5m	Cached graph statistics
`cache:catalog:{org_id}`	String	10m	Cached source catalog

Streams

Stream	Description
`events:ingestion`	Ingestion progress events for all sources
`events:connector:{source_id}`	Connector-specific events (tool execution, sync progress)
`events:agent:{agent_id}`	Agent execution events

Pub/Sub Channels

Channel	Description
`sync:{source_id}`	Sync completion notifications
`approval:{conversation_id}`	Tool approval request/response signals

Database Schema Reference ​

PostgreSQL Tables ​

organizations ​

users ​

api_keys ​

source_configs ​

ingestion_jobs ​

conversations ​

messages ​

agent_configs ​

agent_runs ​

sub_agents ​

sub_agent_workflows ​

sub_agent_triggers ​

orchestration_gateways ​

worker_tokens ​

role_definitions ​

policy_sources ​

policies ​

policy_audit_log ​

audit_log ​

Neo4j Graph Model ​

Common Node Labels ​

Common Relationship Types ​

Node Properties ​

Vector Indexes ​

Redis Key Patterns ​

Key Patterns ​

Streams ​

Pub/Sub Channels ​