Gregius Data – Schema Management

Overview

The Schema Management subsystem creates, verifies, versions, and upgrades the database contracts required by Gregius Data features — sync, search, and vectors. It handles both direct PostgreSQL connections and PostgREST/Supabase connections with provider-specific setup flows.

Prerequisites

• WordPress 6.9+ with Gregius Data plugin installed and activated
• At least one connection configured in the Connections hub
• For Direct PostgreSQL: DB user with extension install privileges and pgvector/pg_trgm availability on the server
• For PostgREST/Supabase: SQL editor access in the Supabase dashboard

---

How It Works

The schema manager orchestrates lifecycle per connection, routing behavior based on the connection’s provider type. All schema mutations require administrator privileges.

Direct PostgreSQL setup

For direct PostgreSQL connections, the schema manager creates WordPress-side tables (settings, sync metadata), then opens a transaction to create PostgreSQL-side structures in dependency order. It installs required extensions (pgvector, pg_trgm) using CREATE EXTENSION IF NOT EXISTS, creates all mirror tables, search functions, and vector support tables. On success the transaction is committed and the schema version is persisted.

PostgREST / Supabase setup

For PostgREST/Supabase connections, WordPress-side tables are created automatically by the schema manager. PostgreSQL-side structures require manual SQL execution. The system provides a SQL download endpoint (/schema/sql) that returns the canonical SQL file and step-by-step operator instructions. After applying the SQL in the Supabase editor, a verification endpoint (/schema/verify) confirms readiness by reading schema metadata via the PostgREST provider.

---

Schema REST Endpoints

All endpoints require administrator authorization and are registered under /wp-json/gg-data/v1/schema/.

Endpoint	Purpose	Connection Parameter
GET /schema/status	View schema existence, completeness, and version	Optional, defaults to default
POST /schema/create	Create schema structures for a connection	Optional, defaults to default
POST /schema/upgrade	Upgrade schema to the current plugin version	Optional, defaults to default
POST /schema/verify	Verify PostgREST schema after manual SQL	Required
GET /schema/sql	Download manual SQL for PostgREST setup	Required

The status endpoint returns compatibility payloads including schema_version, plugin_version, complete status, vector_extension and pg_trgm_extension presence, and per-table diagnostic fields. Legacy field aliases are preserved for dashboard compatibility.

---

Version Tracking

Schema version is tracked per connection in the settings table under the schema category. This enables multi-connection deployments with independent upgrade targeting. A legacy fallback migrates version values from wp_options to settings storage when detected.

The upgrade endpoint compares stored schema_version against the plugin’s current version and runs migration steps inside a database transaction. If the schema is already up to date, the endpoint reports upgraded=false without making changes.

---

Troubleshooting

Extension installation fails — Confirm the PostgreSQL host has pgvector and pg_trgm extension packages installed. DB user must have CREATE EXTENSION privileges. Managed-host environments may require a DBA to enable extensions.

Schema status shows not complete — Re-run the create flow for direct PostgreSQL connections. Verify tables exist under the expected prefix. Confirm schema version was persisted after a successful create or upgrade.

Supabase verify reports not created — Retrieve SQL via GET /schema/sql, execute it in the Supabase SQL editor, then re-run POST /schema/verify for the same connection.

Upgrade does not run — Compare schema_version against plugin_version in the status payload. Confirm the caller has manage_options capability. Check plugin logs for migration exceptions that triggered a rollback.

---