Bounded FMP-to-PostgreSQL reference

This reference application composes PyCharter's public Pipeline, native v2 pipeline/contract files, and run-local PipelineIO. It runs as a terminating batch job. It needs no PyCharter API server, queue worker, metadata database, or MCP server. The runtime code lives in examples/fmp_reference/; versioned domain artifacts live in src/pycharter/data/seed/examples/fmp_reference/.

The supported scope is one EOD dataset, at most ten explicitly selected symbols, a window of at most 31 days, and explicitly reviewed eligible dates. The bundled DEMO records are synthetic. Fixture acceptance does not establish FMP entitlement, full-universe capacity, current market freshness, or a live scheduled deployment. This is an application reference, not a new general PyCharter engine or package store backend.

Create and review the project

This reference currently requires an authenticated, reviewed source checkout of the private PyCharter repository. The published pycharter==0.0.67 package does not contain PipelineIO or the Pipeline.arun(io=...) seam used here; installing that release cannot run this reference. Package release and public bundle distribution remain gated under FMP-11 in the canonical backlog.

From that checkout at the reviewed implementation commit:

python -m venv .venv-reference
. .venv-reference/bin/activate
pip install -e '.[reference]'
cp -R src/pycharter/data/seed/examples/fmp_reference ./my-fmp-project
python -m examples.fmp_reference validate --project my-fmp-project/project.yaml

examples/fmp_reference/requirements.txt records the tested supporting-library versions for a pinned reference environment. Pin the repository commit as well; installing the library alone does not install this repository application.

The agent and user review the universe, eligible trading dates, destination, access entitlement, request budget, cadence, retention, and alert owner. Edit project.yaml to set symbols and expected dates. Each symbol must return exactly one record per expected date; duplicates, extra dates, missing dates, and empty responses fail the entire bounded run. The application deliberately does not invent an exchange calendar or assume that a missing date is a holiday.

Keep the reviewed YAML in the user's own repository. Secret names belong in the policy; secret values belong in the operator's secret manager or local environment. Do not paste database passwords or API keys into agent prompts. The reference supports the exact documented two-step profile and rejects native source/target/contract settings it cannot honor.

Provision a destination explicitly

Set FMP_DATABASE_URL through your environment/secret manager to a dedicated PostgreSQL database using a provisioning role. Review these objects first:

Object	Purpose
fmp_reference_prices	Latest supplied values by (dataset_id, symbol, date)
fmp_reference_receipts	Correlated committed/failed attempt evidence
fmp_reference_quarantine	Retained bounded batch after validation/publication failure

python -m examples.fmp_reference provision --project my-fmp-project/project.yaml

Provision creates missing tables only. It does not migrate or drop existing objects. Existing-column, primary-key, or decimal-precision mismatches fail. Use a dedicated destination/schema: these explicit reference table names are fixed. Before routine runs, replace the provisioning connection with a role having only schema usage and SELECT/INSERT/UPDATE on these three objects. No DDL is issued during run, status, or monitor. Target preflight precedes every source request. PostgreSQL is the supported durable destination; SQLite (sqlite:///absolute/path.db) is a lightweight smoke path, whose numeric storage does not establish PostgreSQL decimal fidelity.

Run, inspect, and safely rerun

Run synthetic loopback HTTP without any FMP key:

python -m examples.fmp_reference run --fixture --project my-fmp-project/project.yaml
python -m examples.fmp_reference run --fixture --project my-fmp-project/project.yaml
python -m examples.fmp_reference status --project my-fmp-project/project.yaml
python -m examples.fmp_reference monitor --project my-fmp-project/project.yaml

The first fixture run reports inserted: 2; the second reports unchanged: 2, with two destination rows. A reviewed source correction reports updated: 1. accepted/native rows_loaded count successfully delivered records, including unchanged records; inserted, updated, and unchanged separately describe actual destination changes. A successful extraction alone is never a commit.

Rows and the required committed receipt share one destination transaction. Any pre-commit failure rolls back the entire bounded publication. A fresh process reruns the complete reviewed window; it does not trust the native settings.state_store as a wired incremental checkpoint. Replaying after a commit preserves keys and unchanged values. PostgreSQL's transaction advisory lock serializes the dataset before source I/O; SQLite serializes database writers. All reference writers must obey this lock; unrelated SQL writers are outside its protection. Database constraints remain the final duplicate-key protection.

Each receipt records run ID, project/dataset, exact file/code/policy hashes, PyCharter version, source coverage/counters, input hash, native validation outcomes, row changes, and publication observation time. Changed rows retain their publishing run ID. Unchanged rows retain their original row provenance; later receipts prove that the bounded window was re-observed. Dataset lineage is explicit. No column lineage, source publication timestamp, distributed exactly-once guarantee, or full raw-response replay is inferred. The project, pipeline and contract bytes are captured once into an owned temporary bundle before native parsing/execution. Later edits to authored files cannot change that run's inputs. Receipt hashes use distinct artifact roles; unsafe or ambiguous artifact references fail before I/O. This bounded profile rejects ${...} interpolation in declared artifacts, so environment/configuration values cannot change their effective validation semantics. Credentials use named environment references only at the outer I/O boundary. Code-file hashes are observed from the pinned reference checkout; run jobs from an immutable deployed revision rather than editing runtime source in a running interpreter.

For a rejected batch, inspect the failed receipt and quarantine using SELECT on the reference tables. Preserve the rejected record and evidence. Confirm a correction against a trusted source or reviewed contract/migration; rerun the window. Never substitute a guessed financial value or weaken validation to make the run green. Quarantine is full-batch retention, not automatic row-level replay. Set an operator retention policy; this initial profile does not delete financial data or quarantine automatically. If commit acknowledgement is lost, inspect the run ID's receipt first; replay remains idempotent.

Bounded live source check

Official documentation reviewed 2026-10-13:

• Endpoint identifies /stable/historical-price-eod/full with a symbol parameter.
• Provider example uses from, to, apikey, and symbol, returning a list of OHLCV records with change, percentage change, and VWAP. This profile preserves these supplied fields; it does not synthesize currency or mix other adjustment series.
• Quickstart describes account access and source errors. Account entitlements and budgets must be verified for the user's key; no universal quota is assumed.

Configure FMP_API_KEY locally, replace synthetic symbols/dates with a reviewed small real window, and run without --fixture. The only allowed live origin is the official HTTPS endpoint; redirects are rejected. Query-string credentials are added only at the HTTP boundary and are absent from saved plans/evidence. Responses, requests, attempts, total decoded bytes, per-response decoded bytes, and execution time have explicit caps. Retriable 429/5xx responses honor bounded Retry-After; excessive provider delays fail instead of retrying early. Access denial, malformed requests/envelopes, transport failures and incomplete coverage are failures. The application never promotes an error object to a row.

The budget is per bounded run, not a distributed credential-wide quota. Do not schedule multiple projects against one key until the owning shared-budget gate is implemented. This endpoint profile expects a complete array and has no pagination; other endpoints need their own verified pagination semantics.

The contract rejects unknown fields and missing/null numeric fields. Negative change and changePercent are valid; OHLC prices must be positive, volume nonnegative. Target decimals use NUMERIC(24,8) (volume NUMERIC(24,0)); unrepresentable precision or overflow fails rather than rounding silently. JSON numeric values pass through Python's normal JSON number decoding, so this does not promise preservation of arbitrary lexical decimal precision. Financial cross-field rules, currency identity and exchange-specific eligibility remain reviewed follow-up work.

Schedule and monitor outside the agent

An external scheduler executes the same run command in a fresh process with a pinned checkout/environment and injected secrets. It owns run retries, overlap policy, cancellation, deadlines and alerts. The application owns only bounded HTTP retries and one atomic publication. Source/API/PyCharter/MCP sessions do not need to stay alive between jobs. Installing this reference does not provision compute or deploy a schedule.

Run monitor independently of ingestion, including during runner outages. It reads durable rows/receipts without contacting FMP and exits nonzero for missing eligible dates, absent/overdue commit observations, future timestamps, or a failed latest attempt. An unobserved dataset is unhealthy. Its freshness receipt must match the currently reviewed policy and artifact/code identities; historical rows plus a recent observation of another window/universe cannot prove current observation freshness. The job deadline covers connection, preflight, extraction, publication and required evidence, with a separate five-second connection cleanup allowance. A timeout or lost commit acknowledgement returns a run ID and asks the operator to inspect the durable receipt; a killed process or unavailable database may leave no attempt receipt. Monitor data coverage/age and scheduler run outcomes independently rather than interpreting that absence as success. Its freshness scope is a reviewed static window plus observation age; a scheduler must publish a newly reviewed window/calendar policy to require new trading dates. The optional fixture-only Prefect profile demonstrates real recurring local schedules, independent monitoring and restart recovery around this same atomic application. It never schedules live source requests. Live scheduled operation, calendar-aware missed runs, alert delivery and full deployment reconciliation remain open gates.

Verification scope

tests/unit/test_fmp_reference.py covers policy, source error/budget behavior, profile compatibility and pure health evaluation. The integration matrix in tests/integration/test_fmp_reference_runtime.py uses controlled real HTTP plus real PostgreSQL and SQLite, checks destination values/counts, repeats/corrections, invalid/duplicate/incomplete/drifted/overprecise data, required-receipt rollback, fresh-process interruption before receipt and after commit, recovery, preflight and PostgreSQL overlap locking. No paid FMP access is needed for this matrix.

The general native Postgres loader still commits per batch and does not provide these reference transaction/count guarantees. This application's explicit destination adapter does not silently change that public loader's behavior. The canonical backlog retains the broader FMP and PIPE gates, including company profiles/statements, shared budgets, complete scheduling/monitoring, migrations, all-symbol capacity and independently verified live operation.