Skip to content

CLAUDE.md

Latest commit

 

History

History
216 lines (154 loc) · 10.5 KB

CLAUDE.md

File metadata and controls

216 lines (154 loc) · 10.5 KB
Raw

PgSTAC Development Instructions

Project Overview

PgSTAC is a PostgreSQL extension (SQL functions + schema) for Spatio-Temporal Asset Catalogs (STAC), paired with pypgstac, a Python package for database migrations and bulk data ingestion.

Architecture

src/pgstac/pyproject.toml ← pgpkg project config for SQL + migration artifacts
src/pgstac/sql/          ← ALL SQL source files (edit ONLY here)
src/pgstac/pgstac.sql    ← Assembled output (DO NOT edit directly)
src/pgstac/migrations/   ← Base + incremental migration files
src/pgstac/tests/        ← PGTap and basic SQL tests
src/pgstac-migrate/      ← Standalone pgstac-migrate wrapper package + baked artifact
src/pypgstac/src/pypgstac/ ← Python package source
src/pypgstac/tests/        ← pytest tests
scripts/                 ← Host-facing entrypoint scripts
scripts/container-scripts/ ← Scripts copied into the pypgstac container image

Documentation Files

  • CHANGELOG.md — the single source of truth for release notes
  • docs/src/release-notes.md — a manual copy of CHANGELOG.md, served by mkdocs. Keep them identical; update both when changing either.

Database Roles

  • pgstac_admin – schema owner, migrations
  • pgstac_ingest – read/write, execute functions
  • pgstac_read – SELECT only

Critical Rules

SQL Changes

ONLY edit files in src/pgstac/sql/. Never edit pgstac.sql directly — it is assembled by concatenating all sql/*.sql files in alphabetical order during stageversion.

File execution order (alphabetical by prefix): 000_idempotent_pre → 001_core → 001a_jsonutils → 001s_stacutils → 002_collections → 002a_queryables → 002b_cql → 003a_items → 003b_partitions → 004_search → 004a_collectionsearch → 005_tileutils → 006_tilesearch → 997_maintenance → 998_idempotent_post → 999_version

Prefix ranges: 000-001 setup/core, 002 collections, 003 items/partitions, 004-006 search/tiles, 997-998 maintenance/post, 999 version (auto-generated).

Idempotency

000_idempotent_pre.sql and 998_idempotent_post.sql are included in both base installs and incremental migrations. Use IF NOT EXISTS, CREATE OR REPLACE, ON CONFLICT DO NOTHING.

Partitioning

Items partitioned by LIST(collection), optionally sub-partitioned by RANGE(datetime) (year/month via collections.partition_trunc). Naming: _items_{key}[_{YYYY|YYYYMM}].

Key functions: check_partition() (create/update), update_partition_stats() (recalculate constraints), partition_sys_meta (live VIEW — always current), partitions (MATERIALIZED VIEW — stale between refreshes).

Search Path

PgSTAC installs into the pgstac schema. All connections must have search_path set to pgstac, public.

pg_dump / pg_restore Compatibility

PgSTAC functions reference PostGIS functions (e.g. st_makeenvelope, st_geomfromgeojson) without schema qualification because PostGIS may be installed in either public or postgis schema. pg_dump clears search_path during restore, breaking these references.

Rules to maintain dump/restore compatibility:

  • Do NOT schema-qualify PostGIS function calls in PgSTAC SQL
  • Avoid cross-function dependencies in SQL functions used by GENERATED columns — pg_dump orders functions alphabetically, so func_a calling func_b may be created before func_b exists. Inline the logic instead.
  • Use pgstac_restore (via scripts/container-scripts/pgstac_restore in the image) to restore dumps — it installs a temporary event trigger that sets the correct search_path before each DDL command
  • Test with scripts/test --pgdump

Development Workflow

Setup

scripts/setup          # Build Docker images, start database
scripts/server         # Start database (use --detach for background)

Running Tests

scripts/test                    # All test suites
scripts/test --pypgstac         # pytest only
scripts/test --pgtap            # PGTap SQL tests
scripts/test --basicsql         # SQL output comparison tests
scripts/test --migrations       # Full migration chain test
scripts/test --formatting       # ruff + ty
scripts/test --pgdump           # pg_dump/pg_restore round-trip test

All tests run inside Docker via scripts/runinpypgstac. Use --build to rebuild images first.

Docker Architecture

  • pgstac container: PostgreSQL 17 + PostGIS 3 + extensions, port 5439→5432
  • pypgstac container: Python + Rust build tools, runs scripts
  • scripts/runinpypgstac uses the published-package path by default; set PGPKG_LOCAL_REPO_DIR to mount a local pgpkg checkout at /pgpkg and export PGPKG_REPO_DIR when stageversion or makemigration should run against a local checkout
  • When no local checkout is mounted, the in-container stageversion / makemigration helpers resolve pgpkg>=0.1.1,<0.2 from PyPI with uv run --no-project --with ...
  • Credentials: username / password, database: postgis

Migration Process

Creating Migrations (Release)

scripts/stageversion 0.9.11

This runs inside Docker and:

  1. Removes old *unreleased* migration files
  2. Writes SELECT set_version('0.9.11'); to 999_version.sql
  3. Runs pgpkg stageversion against src/pgstac/pyproject.toml → migrations/pgstac--0.9.11.sql
  4. Uses --also-write to keep pgstac.sql synchronized with the latest base migration
  5. Updates version.py and pyproject.toml version strings
  6. Runs makemigration -f 0.9.10 -t 0.9.11 to generate the wrapped incremental migration via pgpkg

How makemigration Works

makemigration (copied from scripts/container-scripts/makemigration into the image) now prefers a local checkout via PGPKG_REPO_DIR, otherwise it resolves the pinned published package with uv run --no-project --with "pgpkg[diff]>=0.1.1,<0.2" pgpkg makemigration:

  1. Uses src/pgstac/pyproject.toml to locate the canonical staged base files
  2. Uses results.temporary_local_db via pgpkg to diff the source and target staged bases
  3. Prepends 000_idempotent_pre.sql
  4. Appends 998_idempotent_post.sql and SELECT set_version(...)
  5. Writes migrations/pgstac--0.9.10--0.9.11.sql

Important:

  1. scripts/stageversion regenerates *unreleased* migration files on each run.
  2. If you hand-edit an incremental migration, do not rerun stageversion unless you want those edits overwritten.
  3. After hand-editing an incremental migration, rebuild the baked artifact: uv run --directory src/pgstac-migrate pgstac-migrate build-artifact
  4. Validate with scripts/test --migrations (or scripts/test for the full gate).

Running Migrations

pypgstac migrate                    # Backwards-compatible wrapper over pgstac-migrate
pypgstac migrate --toversion 0.9.10 # Backwards-compatible wrapper over pgstac-migrate
uv run --directory src/pgstac-migrate pgstac-migrate build-artifact
uv run --directory src/pgstac-migrate pgstac-migrate info
uv run --directory src/pgstac-migrate pgstac-migrate versions

pgstac-migrate owns runtime migration planning and apply logic. pypgstac migrate delegates to the same Python API for backwards compatibility and does not execute source-tree SQL files directly. The source-tree pgstac-migrate package prefers the baked artifact at src/pgstac-migrate/src/pgstac_migrate/migrations.tar.zst and rebuilds it from the source tree when that file is missing. src/pgstac-migrate/pyproject.toml resolves pgpkg>=0.1.1,<0.2 from PyPI. The standalone src/pgstac-migrate/scripts/build_artifact.py helper does not use that lockfile; it carries its own inline pgpkg>=0.1.1,<0.2 dependency. src/pypgstac/pyproject.toml keeps a local [tool.uv.sources] override to the sibling ../pgstac-migrate project so uv run --directory src/pypgstac ... resolves the wrapper stack from the source tree, while pgpkg resolves from PyPI. In the Docker-backed dev flow, scripts/runinpypgstac can mount a local pgpkg checkout at /pgpkg and export PGPKG_REPO_DIR for container-script testing.

Testing Details

Test Database Setup

Tests create pgstac_test_db_template from pgstac.sql, then clone it per test suite:

  • pgstac_test_pgtap – PGTap tests
  • pgstac_test_basicsql – basic SQL tests
  • pgstac_test_pypgstac – pytest (function-scoped fixture creates fresh DB per test)

Test Types

  1. PGTap: SQL assertions in src/pgstac/tests/pgtap.sql
  2. Basic SQL: .sql files in src/pgstac/tests/basic/, output compared to .sql.out
  3. Pytest: src/pypgstac/tests/test_load.py, test_benchmark.py, test_queryables.py, hydration/
  4. Migration: Installs v0.3.0, migrates to latest, runs all test suites against migrated DB
  5. pg_dump: Dumps a database with sample data, restores via pgstac_restore, verifies counts match

Pytest Fixtures (conftest.py)

  • db – function-scoped PgstacDB connected to fresh test DB
  • loader – Loader(db) instance

PR Checklist

  1. Changes only in src/pgstac/sql/ for SQL, src/pypgstac/ for Python
  2. Tests added if appropriate
  3. CHANGELOG.md updated under ## [UNRELEASED]
  4. docs/src/release-notes.md updated to match CHANGELOG.md (they must stay identical)
  5. Docs updated if needed
  6. All tests pass: scripts/test (or scripts/runinpypgstac --build test --pypgstac)

Release Checklist

  1. scripts/stageversion VERSION
  2. Review generated incremental migration for correctness
  3. If hand-edited, run uv run --directory src/pgstac-migrate pgstac-migrate build-artifact
  4. scripts/test --migrations
  5. Move CHANGELOG "Unreleased" → new version
  6. Copy updated CHANGELOG.md to docs/src/release-notes.md (keep identical)
  7. Create PR, merge
  8. git tag vVERSION && git push origin vVERSION
  9. CI publishes pypgstac and pgstac-migrate to PyPI plus the ghcr.io images (requires trusted publishers for both PyPI projects on .github/workflows/release.yml with the pypi environment)

Common Patterns

Adding a new SQL function

  1. Edit the appropriate file in src/pgstac/sql/ (use CREATE OR REPLACE FUNCTION)
  2. Add SECURITY DEFINER if the function modifies tables
  3. Grant execute in 998_idempotent_post.sql if needed
  4. Add PGTap or basic SQL tests

Adding a new queryable

INSERT INTO queryables (name, definition, property_wrapper, property_index_type)
VALUES ('prop_name', '{"$ref": "..."}', 'to_int', 'BTREE')
ON CONFLICT DO NOTHING;

Loading test data

scripts/loadsampledata