Multi-Tenant Task Management Database

A PostgreSQL-only implementation of a multi-tenant task management system. The project focuses entirely on schema design, row-level security (RLS), and audit logging — there's no application layer, just the database doing the enforcement work.

About The Project

This is a database-first exploration of multi-tenancy: instead of scoping tenants at the application layer, every table and policy lives in PostgreSQL itself. The core schema — users, teams, team memberships, tasks, and an audit log — is in place and seeded with demo data. The main unfinished work is the write path for non-owner roles and performance tuning for larger datasets.

Key Features

• Multi-tenant schema with auth.users, core.teams, core.team_members, core.tasks, and audit.audit_logs
• Row-level security enabled across user, team-membership, and task tables
• Forced RLS on core.tasks so even table owners can't bypass policies
• Seeded demo dataset (4 users, 3 teams, 5 memberships, 9 tasks, 1 audit log row)
• Append-only audit logging trigger on task writes
• Dockerized local PostgreSQL 16 setup with schema-only and full data dumps

Development Progress

StatusIn progress — core schema done, write path and performance work remaining

The multi-tenant schema, RLS setup, seeded demo data, and forced owner enforcement on tasks are complete. Task and team-member RLS policies are partially implemented, the audit trigger needs permission and delete-handling fixes, and views, helper query functions, performance indexes, and a large-scale benchmark dataset are not yet implemented.

Technology Stack

• PostgreSQL 16
• Docker / Docker Compose

Schema Summary

• auth.users — user identity and role table
• core.teams — teams
• core.team_members — user-to-team membership
• core.tasks — shared tasks scoped by creator, assignee, and team
• audit.audit_logs — append-only audit table for task writes
• analytics — schema created in the dump but currently unused

Security Model

The intended access model is user-scoped and team-scoped, enforced entirely through PostgreSQL RLS rather than application logic.

• core.tasks has SELECT, INSERT, and UPDATE policies, with forced row-level security and no DELETE policy yet
• core.team_members has RLS enabled but no policy currently checked in
• auth.users has RLS enabled

One concrete inconsistency surfaced during testing: the SELECT and INSERT policies on tasks read a different session variable than the UPDATE policy and the audit trigger, which breaks the write path for non-owner roles until both are set explicitly.

Verified Behavior

Testing as a non-owner role against the seeded dataset showed that task visibility currently behaves more like creator-or-assignee isolation than full team-based isolation, since the team-membership branch of the task policy is effectively blocked by the missing core.team_members policy. Insert and update attempts as a non-owner role surface real, reproducible errors — a missing sequence grant, a missing session variable, and a permissions gap inside the audit trigger — each pointing to a specific fix rather than a vague "doesn't work."

Problems Faced

1. Inconsistent Session Variables : Task policies and the audit trigger don't all read from the same session-setting name, which silently breaks the write path until both variables are set for a session.

2. Sequence and Schema Grants : A non-owner role can have INSERT privilege on the tasks table itself while still lacking privileges on the underlying sequence and the audit schema, producing permission errors deeper in the write path than expected.

3. Audit Trigger Delete Handling : The audit trigger fires on delete but currently references the wrong row variable, so delete events aren't logged correctly.

4. RLS Performance Under Policy Evaluation : Indexing the base table doesn't remove the cost of evaluating RLS predicates — especially policy subqueries that touch other RLS-protected tables, which can keep queries slow or incomplete even with good indexes on core.tasks itself.

Key Learnings

1. RLS Is a Cross-Cutting Concern : Row-level security policies, grants, sequences, and triggers all have to agree on the same assumptions — a single inconsistent session-variable name can quietly break an entire write path.

2. Forced RLS Changes the Failure Mode : Using FORCE ROW LEVEL SECURITY means even privileged roles hit policy checks, which surfaces enforcement gaps early instead of letting them hide behind owner bypasses.

3. Performance Work Has To Follow Correctness : Benchmarking and indexing only make sense once the write path actually works for the roles being tested — there's little point optimizing queries that don't yet behave correctly.

What's Next

• Fix the write path by aligning grants, trigger permissions, and session-setting names
• Add and verify a core.team_members policy so team-based visibility actually works
• Decide whether the audit trigger should run as SECURITY DEFINER, and fix delete handling to use the correct row reference
• Seed a large (50k+ row) dataset and capture baseline EXPLAIN ANALYZE output
• Add only the indexes that demonstrably improve real query plans