piilot-pack-supabase

Supabase connector — schema-aware integration with multi-account support. Read-only access via dedicated PG user, no data mirror, sqlglot validation.

🧩 This repo is both a template and a working "hello" plugin.

Just want to demo / dogfood? Install it as-is, it loads as plugin hello with one module and one migration — no edits required.

Want to start your own plugin? Fork via gh repo create --template, then rename everything to your namespace in one go:

./init-plugin.sh <namespace> "<description>" <category>

See the Piilot plugin development guide for the full workflow.

---

What it does

TODO: one-paragraph summary of this plugin's purpose and the problem it solves for a Piilot company.

What this template demonstrates (SDK v0.3)

The hello plugin is a functional showcase of the Piilot SDK v0.2+ primitives, pinned to piilot-sdk>=0.3.0. Fork the template and start from a plugin that already wires:

File	SDK primitive	What it illustrates
piilot_pack_supabase/handlers.py	ctx.handlers.register	Module handlers dispatched by the module runtime
piilot_pack_supabase/migrations/ + __init__.py	ctx.migrations.register_schema	Idempotent per-plugin SQL schema + RLS policies
piilot_pack_supabase/locales/ + __init__.py	ctx.i18n.register_locales	Per-namespace locale catalogs merged into /i18n/catalog
piilot_pack_supabase/repo.py + migrations/002_*.sql	piilot.sdk.db	cursor(), run_in_thread, Json — direct SQL with RLS propagation
piilot_pack_supabase/routes.py	piilot.sdk.http	register_router + Depends(require_user) + get_real_ip
piilot_pack_supabase/tools.py	piilot.sdk.tools + piilot.sdk.session	Agent StructuredTool with system_prompt_builder + session read
piilot_pack_supabase/seeds.py	piilot.sdk.modules + piilot.sdk.templates	Idempotent register_module + register_template upserts
piilot_pack_supabase/connector.py	piilot.sdk.connectors	Commented out — pattern for declaring an external API connector
piilot_pack_supabase/jobs.py	piilot.sdk.scheduler	Commented out — pattern for a periodic sync handler
frontend/src/index.ts	core.registerModuleView + registerI18nBundle	Plugin UI entry — ships as piilot-pack-supabase-ui on npm
frontend/src/HelloModuleView.tsx	React component rendered by the host ModuleViewShell	URL-backed sub-routing via useSearchParams, imports from @plugin-host/*
.github/workflows/release-ui.yml	npm publish on ui-v* tag	Mirrors the PyPI workflow for the backend

Full reference: see the Piilot plugin development guide.

Dual distribution

• Backend — piilot-pack-supabase on PyPI, tagged v<version>.
• Frontend — piilot-pack-supabase-ui on npm, tagged ui-v<version>.

Both ship from this single repo; the two release workflows publish independently so a backend hot-fix doesn't force a frontend release and vice-versa.

Install

Self-hosted Piilot (docker-compose.selfhost.yml)

# Drop the package into /app/plugins/
pip install piilot-pack-supabase
# or for a git-based install while the SDK is still pre-PyPI:
pip install git+https://github.com/Kinetics-Consulting-V2/piilot-pack-supabase.git@v0.1.0

# Restart the backend
docker compose restart backend

Piilot Cloud (SaaS)

Add the dependency to the core's requirements.txt:

piilot-pack-supabase @ git+https://github.com/Kinetics-Consulting-V2/piilot-pack-supabase.git@v0.1.0

Coolify rebuilds the backend image; the loader picks up the plugin at startup and applies migrations. Enable for a given company via:

curl -X PUT \
  -H "Authorization: Bearer $ADMIN_TOKEN" \
  -H "X-Company-Id: $COMPANY_ID" \
  https://api.piilot.ai/admin/plugins/hello/activations/$COMPANY_ID

Usage

TODO: how a user interacts with the plugin (module screens, agent tools, integrations…).

Development

Local setup

# Clone next to the Piilot core
cd /opt/factory/projects
git clone https://github.com/Kinetics-Consulting-V2/piilot-pack-supabase.git

# Install in editable mode with dev extras
cd piilot-pack-supabase
pip install -e .[dev]

Then add the plugin as a bind mount in the core's docker-compose.override.yml (gitignored):

services:
  backend:
    volumes:
      - ../piilot-pack-supabase:/app/plugins/piilot-pack-supabase

Restart the backend and watch for:

[plugins] Loaded: hello v0.1.0 (handlers=1, tools=0)

Run the tests

pytest

SQL validator — what's allowed, what's blocked

Every tool that runs SQL on the customer's Supabase project goes through validator.validate(sql, allowed_tables=…) (see piilot_pack_supabase/validator.py). The gate parses the SQL with sqlglot, walks the AST, and refuses anything that's not a strict SELECT against the admin's whitelisted tables.

Allowed at the top level: SELECT, WITH … SELECT, UNION, INTERSECT, EXCEPT, parenthesized subqueries.

Blocked everywhere in the tree (DML/DDL gate, even inside CTEs): INSERT, UPDATE, DELETE, MERGE, TRUNCATE, CREATE, DROP, ALTER, GRANT, REVOKE, COPY FROM/TO PROGRAM, SET SESSION.

Refused functions: pg_read_file, lo_export, dblink, pg_terminate_backend, pg_advisory_lock, pg_ls_dir, plus pg_sleep with a non-literal argument or a literal > 1 second.

Whitelist: every table referenced in the SQL must be in the connection's selected_tables config (set via the TablePicker UI). Schema-qualified references are matched case-insensitively. Tables in auth, storage, vault, pg_catalog, information_schema and other internal Supabase schemas are filtered out of the snapshot upstream and can never land in the whitelist.

Known limitations

The corpus at tests/fixtures/known_false_positives.sql lists the patterns the validator refuses on purpose (pg_catalog access, multi-statement payloads, CTEs wrapping DML, pg_sleep with a runtime arg) along with the workarounds. Two false positives (INTERSECT / EXCEPT at the top level) were fixed in v0.1 during the Phase 10 red-teaming pass — both queries now pass.

tests/fixtures/known_legitimate_selects.sql is the regression corpus (50+ legitimate SELECTs) the validator is asserted to accept in CI; add a new case there before assuming a refusal is a bug.

Versioning

This plugin follows Semantic Versioning. The sdk_compat range in pyproject.toml pins the Piilot SDK versions we build against. Watch the core's docs/SDK_CHANGELOG.md for breaking changes.

License

Apache-2.0. See LICENSE.