Implement multi-tenant PostgreSQL database layer with row-level security. Use for database schema design, tenant isolation, migrations, connection pooling, and data access patterns. Triggers on "database schema", "PostgreSQL", "multi-tenant", "row-level security", "RLS", "database migration", "sqlc", or when implementing the data layer for AgentStack.
Implement multi-tenant PostgreSQL database layer with row-level security. Use for database schema design, tenant isolation, migrations, connection pooling, and data access patterns. Triggers on "database schema", "PostgreSQL", "multi-tenant", "row-level security", "RLS", "database migration", "sqlc", or when implementing the data layer for AgentStack.
Multi-Tenant PostgreSQL
Overview
Implement a multi-tenant PostgreSQL database with row-level security (RLS), providing complete tenant isolation while maintaining a single database instance for operational simplicity.
-- migrations/001_initial_schema.sql-- Enable extensionsCREATE EXTENSION IF NOTEXISTS "uuid-ossp";
CREATE EXTENSION IF NOTEXISTS "pgcrypto";
CREATE EXTENSION IF NOTEXISTS "pg_trgm";
-- OrganizationsCREATE TABLE organizations (
id TEXT PRIMARY KEYDEFAULT'org_'|| gen_random_uuid()::text,
name TEXT NOT NULL,
slug TEXT UNIQUENOT NULL,
plan TEXT NOT NULLDEFAULT'free'CHECK (plan IN ('free', 'pro', 'enterprise')),
settings JSONB DEFAULT'{}',
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
-- Projects (tenant boundary)CREATE TABLE projects (
id TEXT PRIMARY KEYDEFAULT'prj_'|| gen_random_uuid()::text,
organization_id TEXT NOT NULLREFERENCES organizations(id) ONDELETE CASCADE,
name TEXT NOT NULL,
slug TEXT NOT NULL,
settings JSONB DEFAULT'{}',
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
UNIQUE (organization_id, slug)
);
CREATE INDEX idx_projects_org ON projects(organization_id);
-- AgentsCREATE TABLE agents (
id TEXT PRIMARY KEYDEFAULT'agt_'|| gen_random_uuid()::text,
project_id TEXT NOT NULLREFERENCES projects(id) ONDELETE CASCADE,
name TEXT NOT NULL,
description TEXT,
framework TEXT NOT NULLCHECK (framework IN ('google-adk', 'langchain', 'crewai', 'custom')),
status TEXT NOT NULLDEFAULT'draft'CHECK (status IN ('draft', 'deploying', 'running', 'failed', 'stopped')),
config JSONB NOT NULLDEFAULT'{}',
metadata JSONB DEFAULT'{}',
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
UNIQUE (project_id, name)
);
CREATE INDEX idx_agents_project ON agents(project_id);
CREATE INDEX idx_agents_status ON agents(status);
-- Agent Revisions (immutable)CREATE TABLE agent_revisions (
id TEXT PRIMARY KEYDEFAULT'rev_'|| gen_random_uuid()::text,
agent_id TEXT NOT NULLREFERENCES agents(id) ONDELETE CASCADE,
project_id TEXT NOT NULLREFERENCES projects(id) ONDELETE CASCADE,
revision_number INTNOT NULL,
image TEXT NOT NULL,
config JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
UNIQUE (agent_id, revision_number)
);
CREATE INDEX idx_revisions_agent ON agent_revisions(agent_id);
-- Chat SessionsCREATE TABLE chat_sessions (
id TEXT PRIMARY KEYDEFAULT'ses_'|| gen_random_uuid()::text,
agent_id TEXT NOT NULLREFERENCES agents(id) ONDELETE CASCADE,
project_id TEXT NOT NULLREFERENCES projects(id) ONDELETE CASCADE,
user_id TEXT,
metadata JSONB DEFAULT'{}',
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
CREATE INDEX idx_sessions_agent ON chat_sessions(agent_id);
CREATE INDEX idx_sessions_project ON chat_sessions(project_id);
-- Chat MessagesCREATE TABLE chat_messages (
id TEXT PRIMARY KEYDEFAULT'msg_'|| gen_random_uuid()::text,
session_id TEXT NOT NULLREFERENCES chat_sessions(id) ONDELETE CASCADE,
project_id TEXT NOT NULLREFERENCES projects(id) ONDELETE CASCADE,
role TEXT NOT NULLCHECK (role IN ('user', 'assistant', 'system', 'tool')),
content TEXT NOT NULL,
tool_calls JSONB,
usage JSONB,
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
CREATE INDEX idx_messages_session ON chat_messages(session_id);
CREATE INDEX idx_messages_created ON chat_messages(created_at DESC);
-- API KeysCREATE TABLE api_keys (
id TEXT PRIMARY KEYDEFAULT'key_'|| gen_random_uuid()::text,
project_id TEXT NOT NULLREFERENCES projects(id) ONDELETE CASCADE,
name TEXT NOT NULL,
key_hash TEXT NOT NULL,
key_prefix TEXT NOT NULL, -- First 8 chars for identification
scopes TEXT[] NOT NULLDEFAULT'{}',
last_used_at TIMESTAMPTZ,
expires_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
CREATE INDEX idx_api_keys_project ON api_keys(project_id);
CREATE INDEX idx_api_keys_hash ON api_keys(key_hash);
Row-Level Security
-- migrations/002_rls_policies.sql-- Enable RLS on all tenant tablesALTER TABLE agents ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_revisions ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY;
-- Create application roleCREATE ROLE app_user;
-- RLS Policies for agentsCREATE POLICY agents_tenant_isolation ON agents
FORALLTO app_user
USING (project_id = current_setting('app.current_project_id', true))
WITHCHECK (project_id = current_setting('app.current_project_id', true));
-- RLS Policies for revisionsCREATE POLICY revisions_tenant_isolation ON agent_revisions
FORALLTO app_user
USING (project_id = current_setting('app.current_project_id', true))
WITHCHECK (project_id = current_setting('app.current_project_id', true));
-- RLS Policies for sessionsCREATE POLICY sessions_tenant_isolation ON chat_sessions
FORALLTO app_user
USING (project_id = current_setting('app.current_project_id', true))
WITHCHECK (project_id = current_setting('app.current_project_id', true));
-- RLS Policies for messagesCREATE POLICY messages_tenant_isolation ON chat_messages
FORALLTO app_user
USING (project_id = current_setting('app.current_project_id', true))
WITHCHECK (project_id = current_setting('app.current_project_id', true));
-- RLS Policies for API keysCREATE POLICY api_keys_tenant_isolation ON api_keys
FORALLTO app_user
USING (project_id = current_setting('app.current_project_id', true))
WITHCHECK (project_id = current_setting('app.current_project_id', true));
Go Database Layer
Connection Pool
// internal/infrastructure/database/pool.gopackage database
import (
"context""fmt""time""github.com/jackc/pgx/v5/pgxpool"
)
type Config struct {
Host string
Port int
Database string
User string
Password string
MaxConns int
MinConns int
MaxConnLifetime time.Duration
MaxConnIdleTime time.Duration
}
funcNewPool(ctx context.Context, cfg Config) (*pgxpool.Pool, error) {
connString := fmt.Sprintf(
"postgres://%s:%s@%s:%d/%s?sslmode=require",
cfg.User, cfg.Password, cfg.Host, cfg.Port, cfg.Database,
)
poolConfig, err := pgxpool.ParseConfig(connString)
if err != nil {
returnnil, fmt.Errorf("parse config: %w", err)
}
poolConfig.MaxConns = int32(cfg.MaxConns)
poolConfig.MinConns = int32(cfg.MinConns)
poolConfig.MaxConnLifetime = cfg.MaxConnLifetime
poolConfig.MaxConnIdleTime = cfg.MaxConnIdleTime
pool, err := pgxpool.NewWithConfig(ctx, poolConfig)
if err != nil {
returnnil, fmt.Errorf("create pool: %w", err)
}
return pool, nil
}
