agol_fdw
PostGIS as the Enterprise Geodatabase for ArcGIS Online.
TechMaven · v0.1.0
The problem
Your data lives in ArcGIS Online.
But you need a real relational database as your system of record — with enforced domains, subtypes, attribute rules, and relationships. And you want data sovereignty in your own walls.
Until now, there's been no clean way to make PostGIS authoritative and keep AGOL in step.
The idea
PostGIS is the source of truth.
AGOL is a synchronized projection.
- AGOL layers become native Postgres tables with full geodatabase semantics.
- Edits flow both ways, asynchronously — neither side blocks the other.
- Every edit is accounted for — no silent data loss.
How it works
Capability
Schema introspection
- introspect_layer() reads the full AGOL layer definition.
- Captures fields, geometry, domains, subtypes, rules, relationships, contingent values.
- Stored in a metadata catalog — the source of truth for DDL.
SELECT agol_fdw.introspect_layer( layer_url := '.../FeatureServer/0', layer_name := 'poles' );
Capability
Full geodatabase semantics — enforced by Postgres
- Coded domains → lookup tables + foreign keys
- Range domains → CHECK constraints
- Subtypes → lookup + BEFORE trigger
- Attribute rules → CHECK / triggers / validation logging
- Relationships → real foreign keys / junction tables
- Contingent values → combo lookups + triggers
Not flattened to plain columns — the rules that make a geodatabase a geodatabase are enforced by Postgres itself.
Capability
One-command materialization
SELECT agol_fdw.materialize_layer( layer_url := '.../FeatureServer/0', layer_name := 'poles', target_schema := 'data' ); -- introspect → DDL → apply → backfill. Idempotent.
data.poles now exists as a real PostGIS table — populated, with full semantics. Available
Capability
Bidirectional async sync
- Outbound: AFTER trigger → outbox → worker → GeoServices REST
- Inbound: AGOL webhooks → inbound_queue → worker
- Optimistic concurrency keyed on agol_version
- Transactional: outbox write shares the feature transaction
The plumbing is designed and stubbed in v0.1.0; the standalone worker is in development.
Capability
No silent data loss
- Dead-letter queue — exhausted retries marked
deadfor review - conflict_log — concurrency conflicts, never auto-resolved
- validation_issues — rule/constraint failures recorded
- reconcile_audit() — periodic sweep catches missed edits
In active development
Capability
Secure credentials
- OAuth2 client-id/secret encrypted at rest with pgcrypto
- Key from the AGOL_FDW_KEY env var — not in the database
- Tokens cached and refreshed proactively
- A database dump alone can't expose your secrets
Available
Capability
agol_fdw Console — a browser UI
- Map viewer — browse/identify AGOL-backed data, MapLibre GL + MVT
- Admin console — run control functions, watch queues & logs, triage conflicts
- Built as GeoLibre plugins in a shared shell
- Thin FastAPI layer over the SQL functions — no new logic
Read/identify works now; map editing follows the sync worker.
Maturity & roadmap
v0.1.0 — foundation
Available
- Schema introspection
- Geodatabase semantics translation
- One-command materialization
- Secure credentials
In development
- Bidirectional sync worker
- Webhook receiver
- Full reconcile / reliability
- Console web app
Get started
# install pip install agol_fdw CREATE EXTENSION agol_fdw; # materialize a layer SELECT agol_fdw.materialize_layer( layer_url := '.../FeatureServer/0', layer_name := 'poles' );
PostGIS as the Enterprise Geodatabase for ArcGIS Online.
TechMaven · agol_fdw