All systems operational

Database & Schema Overview

Data lives in a Supabase PostgreSQL database, organized into organization-scoped tables and evolved through numbered SQL migrations, with row-level security enforcing tenant isolation.

#Purpose

Give developers a map of the data layer, the entity relationships, and how the schema evolves.

#Architecture

The schema is defined by a sequence of numbered SQL migrations. Core entities include organizations and users; CRM (leads, opportunities, clients); delivery (projects, project_tasks, time_entries, timesheets, deliverables); finance (client_invoices, expenses); people (HR directory, reviews, leave, training); support (tickets); content (cms_sections, cms_items); plus cross-cutting systems: agents, approvals, notifications, activity_logs, api_keys, api_request_logs, and webhook_endpoints/deliveries.

Almost every table carries an org_id and is protected by RLS so that a query only ever returns the caller's organization's rows.

1
organizations
Tenant root.
2
users
Belong to an org.
3
domain tables
leads, projects, invoices…
4
cross-cutting
agents, approvals, logs, webhooks.

#How it works

1

Migrations define schema

Numbered SQL files create and evolve tables, types, indexes, and RLS policies.
2

org_id scopes rows

Domain rows reference their organization.
3

RLS filters access

Policies use the caller's role and organization to permit or deny each row.
4

Relationships link entities

Foreign keys connect, for example, tasks to projects and invoices to clients.

#Reference

#Representative core entities

AreaTables (examples)
Tenancyorganizations, users
CRMleads, opportunities, clients
Deliveryprojects, project_tasks, time_entries, timesheets, deliverables
Financeclient_invoices, expenses
AIagents
Platformapprovals, notifications, activity_logs, api_keys, api_request_logs, webhook_endpoints, webhook_deliveries

#Implementation notes

  • The schema is evolved by ~74 numbered migrations (001 onward).
  • RLS policies reference a current-user-role helper and the caller's organization.
  • Soft deletes are used where history must be preserved (for example, users carry is_active and deleted_at).

#Limitations

Known limitations

  • There is no separate analytical store; reporting reads the operational tables.
  • Schema changes are migration-driven; there is no runtime schema editing.

#Security considerations

Security

  • RLS is the primary isolation boundary — policies must accompany every tenant table.
  • The service-role key bypasses RLS and must remain server-side.
  • Encrypted columns (e.g., WordPress credentials) rely on pgcrypto and a server-side key.

#Best practices

  • Add an RLS policy with every new tenant table.
  • Prefer soft delete where historical integrity matters.
  • Keep migrations forward-only and numbered.
Authorization & Isolation

RLS & roles.

Security Model

Isolation & secrets.

Deployment & Migrations

Applying schema.

Still need help?

Can’t find what you’re looking for? The DevSphere OS team is happy to help.

Contact Support