multi-tenant-postgres

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.

Ir para a instalação

Repositório: raphaelmansuy/k8s-agent-stack
Última atividade na origem: 19 de dezembro de 2025 às 04:22
Idioma detectado do SKILL.md: inglês
Estrelas: 2
Forks: 0

Opções de instalação

Por padrão, está selecionado o prompt que primeiro revisa a origem. Você pode mudar para um comando direto ou baixar uma cópia local.

Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.

Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.

npx skills add https://github.com/raphaelmansuy/k8s-agent-stack --skill multi-tenant-postgres

O comando permanece em uma só linha. Role horizontalmente para revisá-lo antes de copiar.

Prefere uma cópia local? Baixe os arquivos disponíveis atualmente no SkillsMP.

Revise os arquivos de origem

Leia o SKILL.md e os arquivos complementares exibidos pelo SkillsMP antes de decidir se vai instalar.

SKILL.md

Instruções da origem · Visualização somente leitura

Voltar à pesquisa Voltar à instalação

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.

Multi-Tenancy Architecture

┌─────────────────────────────────────────────────────────────────┐ │ Multi-Tenant Data Architecture │ ├─────────────────────────────────────────────────────────────────┤ │ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ Application Layer │ │ │ │ • Set project_id context on each request │ │ │ │ • Connection pool with PgBouncer │ │ │ └─────────────────────────────────────────────────────────┘ │ │ │ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ PostgreSQL │ │ │ │ ┌─────────────────────────────────────────────────┐ │ │ │ │ │ Row-Level Security (RLS) │ │ │ │ │ │ • All tables have project_id column │ │ │ │ │ │ • Policies enforce project_id = current_setting │ │ │ │ │ └─────────────────────────────────────────────────┘ │ │ │ │ │ │ │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │ │ │ │Project A│ │Project B│ │Project C│ │ ... │ │ │ │ │ │ (rows) │ │ (rows) │ │ (rows) │ │ │ │ │ │ │ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │ │ │ └─────────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘

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.

Multi-Tenancy Architecture

-- migrations/001_initial_schema.sql -- Enable extensions CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION IF NOT EXISTS "pgcrypto"; CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- Organizations CREATE TABLE organizations ( id TEXT PRIMARY KEY DEFAULT 'org_' || gen_random_uuid()::text, name TEXT NOT NULL, slug TEXT UNIQUE NOT NULL, plan TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')), settings JSONB DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- Projects (tenant boundary) CREATE TABLE projects ( id TEXT PRIMARY KEY DEFAULT 'prj_' || gen_random_uuid()::text, organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE, name TEXT NOT NULL, slug TEXT NOT NULL, settings JSONB DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (organization_id, slug) ); CREATE INDEX idx_projects_org ON projects(organization_id); -- Agents CREATE TABLE agents ( id TEXT PRIMARY KEY DEFAULT 'agt_' || gen_random_uuid()::text, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, name TEXT NOT NULL, description TEXT, framework TEXT NOT NULL CHECK (framework IN ('google-adk', 'langchain', 'crewai', 'custom')), status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'deploying', 'running', 'failed', 'stopped')), config JSONB NOT NULL DEFAULT '{}', metadata JSONB DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT 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 KEY DEFAULT 'rev_' || gen_random_uuid()::text, agent_id TEXT NOT NULL REFERENCES agents(id) ON DELETE CASCADE, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, revision_number INT NOT NULL, image TEXT NOT NULL, config JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (agent_id, revision_number) ); CREATE INDEX idx_revisions_agent ON agent_revisions(agent_id); -- Chat Sessions CREATE TABLE chat_sessions ( id TEXT PRIMARY KEY DEFAULT 'ses_' || gen_random_uuid()::text, agent_id TEXT NOT NULL REFERENCES agents(id) ON DELETE CASCADE, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, user_id TEXT, metadata JSONB DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_sessions_agent ON chat_sessions(agent_id); CREATE INDEX idx_sessions_project ON chat_sessions(project_id); -- Chat Messages CREATE TABLE chat_messages ( id TEXT PRIMARY KEY DEFAULT 'msg_' || gen_random_uuid()::text, session_id TEXT NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system', 'tool')), content TEXT NOT NULL, tool_calls JSONB, usage JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_messages_session ON chat_messages(session_id); CREATE INDEX idx_messages_created ON chat_messages(created_at DESC); -- API Keys CREATE TABLE api_keys ( id TEXT PRIMARY KEY DEFAULT 'key_' || gen_random_uuid()::text, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, name TEXT NOT NULL, key_hash TEXT NOT NULL, key_prefix TEXT NOT NULL, -- First 8 chars for identification scopes TEXT[] NOT NULL DEFAULT '{}', last_used_at TIMESTAMPTZ, expires_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_api_keys_project ON api_keys(project_id); CREATE INDEX idx_api_keys_hash ON api_keys(key_hash);

// internal/infrastructure/database/agent_repository.go package database import ( "context" "github.com/raphaelmansuy/agentstack/internal/domain/agent" ) type AgentRepository struct { db *TenantDB } func NewAgentRepository(db *TenantDB) *AgentRepository { return &AgentRepository{db: db} } func (r *AgentRepository) Create(ctx context.Context, projectID string, a *agent.Agent) error { conn, err := r.db.WithTenant(ctx, projectID) if err != nil { return err } defer conn.Release() query := ` INSERT INTO agents (id, project_id, name, description, framework, status, config) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING created_at, updated_at ` return conn.QueryRow(ctx, query, a.ID, projectID, a.Name, a.Description, a.Framework, a.Status, a.Config, ).Scan(&a.CreatedAt, &a.UpdatedAt) } func (r *AgentRepository) GetByID(ctx context.Context, projectID, agentID string) (*agent.Agent, error) { conn, err := r.db.WithTenant(ctx, projectID) if err != nil { return nil, err } defer conn.Release() query := ` SELECT id, project_id, name, description, framework, status, config, created_at, updated_at FROM agents WHERE id = $1 ` // Note: RLS automatically filters by project_id var a agent.Agent err = conn.QueryRow(ctx, query, agentID).Scan( &a.ID, &a.ProjectID, &a.Name, &a.Description, &a.Framework, &a.Status, &a.Config, &a.CreatedAt, &a.UpdatedAt, ) if err != nil { return nil, err } return &a, nil } func (r *AgentRepository) List(ctx context.Context, projectID, cursor string, limit int) ([]*agent.Agent, string, error) { conn, err := r.db.WithTenant(ctx, projectID) if err != nil { return nil, "", err } defer conn.Release() query := ` SELECT id, project_id, name, description, framework, status, config, created_at, updated_at FROM agents WHERE ($1 = '' OR id > $1) ORDER BY id LIMIT $2 ` // Note: RLS automatically filters by project_id rows, err := conn.Query(ctx, query, cursor, limit+1) if err != nil { return nil, "", err } defer rows.Close() var agents []*agent.Agent for rows.Next() { var a agent.Agent err := rows.Scan( &a.ID, &a.ProjectID, &a.Name, &a.Description, &a.Framework, &a.Status, &a.Config, &a.CreatedAt, &a.UpdatedAt, ) if err != nil { return nil, "", err } agents = append(agents, &a) } var nextCursor string if len(agents) > limit { nextCursor = agents[limit].ID agents = agents[:limit] } return agents, nextCursor, nil }

multi-tenant-postgres

Opções de instalação

Revise os arquivos de origem