database

8 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Set PostgreSQL app.current_tenant session variable before queries

Before executing queries on RLS-protected tables, set the PostgreSQL session variable using session.execute("SELECT set_config('app.current_tenant', :tenant_id, true)") with the current tenant's ID.

Database isolation strategy selection by security tier

Choose isolation strategy based on requirements: Separate Databases (highest isolation, for regulated industries/enterprise); Separate Schemas (high isolation, balance manageability); Shared Tables with Row-Level Security (medium isolation, cost-effective for high tenant count); Hybrid (variable isolation, different tiers for different customers).

Enable Row-Level Security on PostgreSQL tenant tables

Execute ALTER TABLE table_name ENABLE ROW LEVEL SECURITY on all tenant-related tables. Create policies using CREATE POLICY policy_name ON table_name FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid). Execute ALTER TABLE table_name FORCE ROW LEVEL SECURITY to enforce RLS for table owners.

Implement TenantAwareSession to auto-filter queries by tenant

Create a TenantAwareSession class extending SQLAlchemy Session that stores _tenant_id. Use SQLAlchemy event listeners: add a before_compile listener to automatically filter all queries by tenant_id column when it exists on the model; add a before_insert listener to automatically set tenant_id on new objects from current_tenant context.

Use TenantMixin to ensure all models have tenant_id column

Create a TenantMixin class with a @declared_attr method that adds a tenant_id Column(String(36), nullable=False, index=True) to every model. All data models must inherit from TenantMixin to ensure tenant_id is always present and indexed.

Implement TenantScopedRepository for data access layer isolation

Create a generic TenantScopedRepository[T] class that enforces tenant isolation on all database operations. Methods must include: get_by_id (filters by both resource_id and tenant_id), list_all (filters by tenant_id with limit/offset), create (auto-sets tenant_id, rejects attempts to create for different tenant), delete (filters by both resource_id and tenant_id). All operations must include the tenant_id check in the WHERE clause.

Do not allow queries without tenant filters in shared database

Never execute queries like SELECT * FROM table without WHERE tenant_id filter, even for admin operations. Implement explicit override mechanism if admin access to all tenants is required.

Do not store tenant data without tenant_id column

Every table containing tenant-specific data must have a tenant_id column (indexed, non-nullable). Tables without tenant_id are security risks.

Give your agent this brain