PostgreSQL centric - Planetary Architecture

Product Requirements Document

PostgreSQL is not the persistence layer. It is the application. Everything else is orbit.

---

1. Vision

Modern web stacks treat the database as a dumb filing cabinet at the end of a long chain — request → router → controller → service → ORM → DB. Business logic is smeared across every layer. Security is enforced in the app. Permissions live in middleware. Mutations go through serializers. The database just executes INSERT and stays quiet.

Planetary Architecture inverts this.

PostgreSQL is the sun. Every other component — the admin dashboard, the HTTP adapter, the frontend, the external services — orbits it. Business logic, authorization, validation, transformation, and auditing all live inside Postgres. Downstream layers are deliberately dumb: they render, they route, they receive webhooks. They do not own logic.

The platform — django-pg-planetary — is the control plane that makes this architecture operable without writing a single line of SQL. It extends Django admin into a full database operations dashboard, serving every persona involved in building and running a Planetary stack.

---

2. The Stack

┌─────────────────────────────────────────────────────────────┐
│                      CONTROL PLANE                          │
│         django-pg-planetary (Django Admin Extension)        │
│         Karen · Bob · Senior Dev — one unified dashboard    │
└──────────────────────────┬──────────────────────────────────┘
                           │  DDL only · metadata only
                           │  never raw table data
┌──────────────────────────▼──────────────────────────────────┐
│                   ☀️  POSTGRESQL (the app)                   │
│                                                             │
│  raw tables      — superadmin only, REVOKE ALL on everyone  │
│  views           — DTOs, redacted, role-scoped              │
│  INSTEAD OF      — the only way CUD ever happens            │
│  functions       — business logic, overloaded by signature  │
│  RLS policies    — authorization at the row level           │
│  triggers        — mutations, audits, notifications         │
│  types/domains   — validated, reusable data shapes          │
│  FDW             — forensic audit to separate DB            │
│  pg_notify       — async event emission                     │
└────────┬────────────────────────────────────────────────────┘
         │                              │
         │ pg_notify / pg_net           │ SQL over HTTP
         │                  ┌───────────▼──────────┐
         │                  │       PostgREST       │
         │                  │   dumb HTTP adapter   │
         │                  │   exposes views +     │
         │                  │   functions as REST   │
         │                  └───────────┬──────────┘
         │                              │ REST + JWT
         │                  ┌───────────▼──────────┐
         │                  │     Next.js BFF       │
         │                  │  renders · consumes   │
         │                  │  zero business logic  │
         │                  └───────────┬──────────┘
         │                              │
┌────────▼──────────────────────────────▼──────────────────────┐
│                   SERVICES (dumb, isolated)                   │
│     pdf-export · email · payment · sms · storage · etc.      │
│     receive payload · do one thing · return result           │
│     know nothing about the DB schema                         │
└──────────────────────────────────────────────────────────────┘
         │
┌────────▼──────────────────────────────────────────────────────┐
│                    AUDIT DB (FDW shadow)                       │
│     separate server · append-only · forensic isolation        │
│     full before/after JSON trail per row per operation        │
└────────────────────────────────────────────────────────────────┘

---

3. Personas

Karen — Business Operations

Uses Django admin to browse and manage data rows. Her experience is unchanged from standard Django admin. She interacts with views only — never raw tables. RLS ensures she sees exactly what her role allows, automatically.

Bob — DevOps / DB Administrator

Uses the planetary extension to manage the full Postgres security and infrastructure layer. Zero SQL written. He manages roles, grants, policies, table health, scheduled jobs, replication, and configuration through GUI forms that generate and execute SQL behind the scenes.

Senior Developer

Uses the extension to design and apply the Postgres application layer. Writes function bodies, designs view schemas, declares protected tables, manages types, configures FTS, and controls the audit setup. The platform scaffolds everything; Senior fills in the business logic.

PostgREST (system actor)

Watches Postgres. Exposes whatever views and functions exist as REST endpoints, scoped by JWT role claims. Picks up every change Senior makes automatically. No configuration required per new view or function.

Next.js BFF (system actor)

Consumes PostgREST endpoints. Renders data. Calls service endpoints for non-DB operations. Has no knowledge of the underlying schema, RLS rules, or function signatures.

---

4. Core Principles

4.1 The Protected Table Contract

Every raw table in a Planetary stack follows this contract:

1. REVOKE ALL ON raw_table FROM PUBLIC — no one touches data directly
2. REVOKE ALL ON raw_table FROM app_role — includes the Django DB user
3. One or more views declared as the only access points
4. INSTEAD OF triggers on each view — the only mutation path
5. Overloaded functions per operation type — validation and transformation
6. RLS on views — row-level authorization per role/claim

The platform scaffolds steps 1–6 from a single "Protect this table" action. Senior fills in function bodies. Everything else is generated.

4.2 Views as DTOs

A view is not a convenience — it is an explicit API contract. One raw table can have many views:

invoice_raw          ← locked, superadmin only
  invoice_v          ← standard ops view, status + amounts
  invoice_v_finance  ← finance role, full breakdown
  invoice_v_redacted ← public-facing, PII masked
  invoice_v_audit    ← compliance, all fields + metadata

PostgREST exposes each view as a separate endpoint. RLS on each view enforces who can query what. No app-layer serializers needed.

4.3 Metadata ≠ Data Privileges

Revoking data access from the Django DB user does NOT revoke metadata access. The platform can fully introspect any table's columns, types, constraints, indexes, policies, and triggers via pg_catalog and information_schema — without ever reading a row of actual data. This is the foundation of the view builder, policy editor, and trigger scaffolder.

4.4 Functions as the Business Logic Layer

Postgres functions are:

• Overloadable by signature — process_invoice(a) and process_invoice(a, b) coexist
• Transactional — they run inside the trigger's transaction
• Testable — callable directly via PostgREST or SELECT
• Replaceable — CREATE OR REPLACE with no downtime

All validation, transformation, computed fields, and side-effect orchestration live in functions. The app layer calls views. It never implements business logic.

4.5 FDW Forensic Audit

Audit triggers write to a foreign table backed by a separate database server via postgres_fdw. The audit DB is:

• On a different host (optionally different provider)
• Append-only by policy — no UPDATE, no DELETE
• Invisible to application roles
• Full row_to_json(OLD) / row_to_json(NEW) per operation

If the main DB is compromised, wiped, or ransomwared — the audit trail is untouched on a completely separate server. The platform wires this up per table with a toggle.

---

5. Platform Features

5.1 Introspection Engine

The foundation. Pure pg_catalog + information_schema queries. Returns structured metadata the UI builds on. No data access required.

• Tables, columns, data types, nullability, defaults
• Constraints — PK, FK, unique, check, exclusion
• Indexes — type, columns, partial condition, expression
• Views + materialized views — definition, dependencies
• Stored functions + procedures — signature, language, body, security
• Triggers — timing, event, level, condition, function
• Policies — command, roles, USING, WITH CHECK expressions
• Roles + grants — membership, table/column/schema privileges
• Extensions — installed, available, version
• FDW servers + foreign tables
• Publications + subscriptions
• Table health — live tuples, dead tuples, bloat, last vacuum/analyze

5.2 Protected Table Manager (Senior + Bob)

The core workflow of the platform.

Declare a table as protected:

• Select table from introspected list
• Platform generates REVOKE statements for all non-superadmin roles
• Column picker: drag-drop columns into one or more named views
• Per-column: include / exclude / apply redaction function
• Platform generates CREATE VIEW for each declared view
• INSTEAD OF trigger skeleton auto-generated per view
• Senior writes function body in inline editor
• One-click apply — REVOKE + views + triggers executed in single transaction

View builder:

• Visual column selector from introspected schema
• Redaction function picker (mask_pan, mask_email, hash, nullify, etc.)
• Live SQL preview
• Role assignment — which PostgREST role sees this view
• RLS policy generator — column picker for USING expression

5.3 Policy Manager — RLS / RBAC / ABAC / PBAC (Bob + Senior)

RLS Policies:

• Enable/disable RLS per table/view — toggle
• Create policy: name, table, command (ALL/SELECT/INSERT/UPDATE/DELETE)
• USING expression builder — column picker + operator + value/function
• WITH CHECK expression builder
• PERMISSIVE vs RESTRICTIVE toggle
• Role assignment
• Live SQL preview
• Active policies list with enable/disable per policy

Role Management (RBAC):

• Create / rename / drop roles
• Role membership — assign roles to roles (hierarchy)
• Grant / revoke table privileges per role
• Grant / revoke column-level privileges
• Grant / revoke schema privileges
• Grant / revoke function execute privileges
• Role matrix view — roles × tables × privileges grid

Session Claims (ABAC):

• Define current_setting('app.x') claim variables used in policies
• JWT claim → set_config mapping documentation per role
• Policy expression helpers using claim variables

Policy Templates (PBAC):

• User-owns-row: user_id = current_setting('app.user_id')::uuid
• Tenant isolation: tenant_id = (auth.jwt() ->> 'tenant_id')::uuid
• Soft-delete filter: deleted_at IS NULL
• Time-bounded: valid_from <= now() AND valid_to >= now()
• Save custom templates — reusable across tables

5.4 Function & Trigger Manager (Senior)

Functions:

• List all stored functions with signature, language, security mode
• Create / edit function — inline code editor with syntax highlighting
• Language picker — plpgsql, sql, python (plpython3u)
• SECURITY INVOKER vs SECURITY DEFINER toggle
• Parameter builder — name, type, default, mode (IN/OUT/INOUT)
• Return type picker — scalar, setof, table, trigger, void
• Function overload group view — all signatures for same name
• Test runner — call function with sample args, see output

Triggers:

• List all triggers per table with status
• Create trigger: timing (BEFORE/AFTER/INSTEAD OF), event (INSERT/UPDATE/DELETE/TRUNCATE)
• Column-specific UPDATE trigger (OF col1, col2)
• FOR EACH ROW vs FOR EACH STATEMENT toggle
• WHEN condition builder
• Function picker from existing trigger functions
• Enable / disable per trigger
• Deferrable + deferred toggle

Event Triggers (DDL-level):

• Fire on CREATE TABLE, ALTER TABLE, DROP, etc.
• Auto-attach audit triggers to any new table — set-and-forget for Bob
• Enforce naming conventions on DDL operations

5.5 Schema Object Manager (Senior + Bob)

Views:

• List all views with definition preview
• Create / edit view — column picker + SQL editor
• Dependency graph — which tables/functions a view uses
• Drop cascade safety — shows what breaks before executing

Materialized Views:

• Create materialized view from view or raw SQL
• Refresh strategy — manual / pg_cron scheduled
• Refresh schedule builder (cron expression)
• Index management on materialized view columns
• Concurrent refresh toggle

Custom Types:

• ENUM types — create, add values, rename, drop
• Composite types — field builder with name + type
• Domain types — base type + CHECK constraint
• Range types — subtype + canonical function

Extensions:

• Available extensions list with description
• Install / drop per extension
• Version display
• Commonly useful: uuid-ossp, pgcrypto, pg_stat_statements, pg_cron, pg_net, postgres_fdw, postgis, unaccent, btree_gin

Sequences:

• List, create, alter (start, increment, min, max, cycle)
• Current value display
• Owned-by column display

Schemas:

• Create / drop schemas
• search_path configuration per role
• Move tables between schemas

5.6 Index Manager (Bob + Senior)

• List all indexes — type, columns, size, usage stats
• Detect unused indexes via pg_stat_user_indexes (idx_scan = 0)
• Create index — type picker (B-tree, GIN, GiST, BRIN, Hash)
• Partial index — WHERE clause builder
• Expression index — expression input with column picker
• Concurrent build toggle (non-blocking)
• Index size vs query benefit display

5.7 Full Text Search (Senior)

• Text search configuration manager
• Dictionary management
• tsvector column setup — which columns, which config
• to_tsvector expression builder
• GIN index auto-suggestion on tsvector columns
• Test query — enter search terms, preview ranked results

5.8 Audit Layer (Bob)

• Enable audit per table — toggle
• FDW server configuration — host, dbname, credentials
• Foreign table auto-creation on audit DB
• Audit trigger auto-generated and attached
• Audit log viewer (reads from foreign table — read-only)
• Audit DB health status
• Retention policy — pg_cron job to prune old audit records (on audit DB side)

5.9 Replication & CDC (Bob)

• Publications — create, add/remove tables, manage row filters
• Subscriptions — create, monitor lag, enable/disable
• Logical replication slot monitoring
• FDW connections — list, test, drop

5.10 Scheduled Jobs — pg_cron (Bob)

• List all cron jobs with schedule, last run, status
• Create job — SQL input + cron expression builder
• Enable / disable per job
• Run now (immediate one-off execution)
• Job run history + error log

5.11 Notifications — pg_notify / pg_net (Senior)

• NOTIFY channels in use — list active LISTEN connections
• pg_net webhook trigger builder — target URL, payload template
• Outbound webhook log (via net._http_response)

5.12 Performance & Health (Bob)

• Table stats — live tuples, dead tuples, bloat %, last vacuum/analyze
• pg_stat_statements — top queries by total time, calls, mean time
• Cache hit ratio — buffer hits vs disk reads
• Connection stats — active, idle, idle-in-transaction, by role
• Lock monitor — active locks, blocking queries, wait graph
• VACUUM / ANALYZE — trigger manually per table or ALL
• Autovacuum settings — per-table overrides (fillfactor, thresholds)
• Table size breakdown — table + indexes + toast

5.13 Configuration (Bob)

• ALTER SYSTEM GUI — categorized parameter list
• Search params by name or description
• Current vs pending (requires reload) indicator
• pg_reload_conf() trigger button
• Per-database and per-role parameter overrides via ALTER DATABASE SET / ALTER ROLE SET

5.14 PostgREST Integration

• View → PostgREST endpoint mapping display
• Function → RPC endpoint display (/rpc/function_name)
• JWT role claim → Postgres role mapping documentation
• Schema cache reload trigger (NOTIFY pgrst, 'reload schema')
• Endpoint health check per view

---

6. Non-Goals

• Not a data browser. Django admin owns rows and data management. This platform does not display table contents except for the audit log viewer.
• Not a query editor. Not a replacement for psql, DBeaver, or TablePlus. Senior who needs raw SQL uses those tools.
• Not an ORM. No model abstraction. Everything is native Postgres SQL, generated and executed directly.
• Not a migration framework. No Alembic/django-migrate style versioned migrations. DDL changes are applied directly. Event triggers handle DDL auditing.
• Not a connection pooler. PgBouncer / Supavisor are separate infrastructure concerns.

---

7. Privilege Architecture

superadmin         → everything. raw tables, DDL, pg_catalog, config
senior_dev role    → DDL via platform, metadata, no raw table data
bob_devops role    → platform UI operations, metadata, health stats
django_app role    → metadata on raw tables, data on views only
postgrest role     → data on views, scoped by JWT sub-role
karen role         → rows in views, filtered by RLS
audit_writer role  → INSERT only on foreign audit tables

The platform authenticates as django_app for introspection. DDL operations are executed via a separate platform_ddl role with elevated privileges, scoped to specific operations, never exposed to the HTTP layer.

---

8. Package Design

django-pg-planetary/
├── planetary/
│   ├── apps.py                  ← PlanetaryConfig, auto-registers admin
│   ├── introspect/
│   │   ├── tables.py            ← columns, types, constraints
│   │   ├── policies.py          ← pg_policies queries
│   │   ├── roles.py             ← pg_roles, memberships, grants
│   │   ├── routines.py          ← functions, triggers, event triggers
│   │   ├── objects.py           ← views, matviews, types, sequences
│   │   ├── indexes.py           ← index stats, usage
│   │   ├── health.py            ← pg_stat_*, vacuum, bloat
│   │   └── replication.py       ← publications, subscriptions, slots
│   ├── builders/
│   │   ├── policy_builder.py    ← CREATE/ALTER/DROP POLICY → SQL
│   │   ├── role_builder.py      ← GRANT/REVOKE/CREATE ROLE → SQL
│   │   ├── trigger_builder.py   ← CREATE/DROP TRIGGER → SQL
│   │   ├── view_builder.py      ← CREATE VIEW / INSTEAD OF → SQL
│   │   ├── function_builder.py  ← CREATE OR REPLACE FUNCTION → SQL
│   │   └── audit_builder.py     ← FDW setup, audit trigger → SQL
│   ├── executor.py              ← safe DDL execution, transaction wrapper
│   ├── admin/
│   │   ├── policy_admin.py
│   │   ├── role_admin.py
│   │   ├── schema_admin.py
│   │   ├── trigger_admin.py
│   │   ├── function_admin.py
│   │   ├── health_admin.py
│   │   ├── audit_admin.py
│   │   └── cron_admin.py
│   ├── templates/
│   │   └── admin/planetary/     ← per-view HTML templates
│   └── static/
│       └── planetary/           ← JS for live SQL preview, editors
└── setup.py

Installation:

# settings.py
INSTALLED_APPS = [
    'django.contrib.admin',
    'planetary',               # adds Planetary section to admin
    ...
]

PLANETARY = {
    'DDL_ROLE': 'platform_ddl',          # elevated role for DDL ops
    'AUDIT_SERVER': 'audit_db_server',   # FDW server name for audit
    'POSTGREST_URL': 'http://localhost:3000',
}

---

9. The Paradigm in One Sentence

Karen checks the data. Bob secures the database. Senior encodes the rules. Postgres enforces everything. PostgREST exposes it. Next.js renders it. Services handle the side effects. Nobody writes middleware.