Stock lake: data contracts and deployment runbook

What is implemented and what qualifies as evidence

The current reference-price catalog remains accessible independently of this pipeline. /api/stocks/pipeline reports actual readiness and blockers. /api/stocks/history?id=…&basis=dex returns unavailable until a populated snapshot-backed DEX series has been published. Missing on-chain evidence never falls back to reference prices.

Canonical event records require chain_id, block_number, block_hash, transaction_hash, transaction_index, log_index, and block_timestamp in UTC epoch seconds. Raw amounts remain integer strings. Header evidence requires independently verified block_hash, block_timestamp, and finalized: true for every block claimed complete. Coverage ranges carry chain_id, start_block, end_block, and complete. Source coverage is a claim made by the extraction coordinator after verifying job completion, not inferred merely from the presence of headers.

Raw evidence, canonical headers and coverage are content-addressed under raw/. Canonical partition files are committed by an atomic immutable manifest under manifests/. Consumers read manifests, never partially written snapshot directories. Duplicate deliveries are idempotent. Unknown operations, deletes, conflicting payloads, disconnected headers and reorg mismatches produce a reextract/ report and no published manifest. Re-extract the affected canonical source range and regenerate dependent results; never resolve a correction by filename order. Complete ranges with no events remain distinct from gaps.

Canonical event validation does not certify an executable pool. Execution research additionally requires independently verified implementation, exact liquidity/tick state, supported hook behavior, checkpoint validation, actual fee configuration, gas valuation and quote/USD observations. Unsupported pools remain blocked. The scanner does not propagate hypothetical bot impact into later observed market states. Results remain execution scenarios rather than proof a historical trade could have been captured.

Local setup and validation

The workspace uses Python 3.12 and pinned dependencies in requirements-stocklake.txt:

.local/bin/uv venv .local/venv --python 3.12
.local/bin/uv pip install --python .local/venv/bin/python -r requirements-stocklake.txt
.local/venv/bin/python -m unittest discover -s tests -p 'test_stocklake_*.py'
.local/venv/bin/python infra/stock-lake/test_infra.py
node tests/stocks.test.mjs

.env and .env.* are ignored except .env.example. Goldsky project credentials remain server-side. Do not use shell source .env, print API keys, or place credentials in CloudFormation outputs. The preflight command performs read-only provider checks and writes a credential-free local status artifact:

.local/bin/aws sts get-caller-identity --query Arn --output text
.local/venv/bin/python scripts/stocklake-preflight.py
.local/venv/bin/python scripts/discover-stocklake-pools.py --offline

The offline census is a starting point, not proof of historical pool completeness. Factory and singleton-manager creation events must reconcile historical pools, including inactive pools and v4 pool IDs.

To canonicalize a downloaded, normalized batch:

.local/venv/bin/python scripts/canonicalize-stocklake.py \
  --input .local/stocklake/extracted/logs.parquet \
  --headers .local/stocklake/extracted/verified-headers.json \
  --coverage .local/stocklake/extracted/coverage.json \
  --output .local/stocklake

The CLI accepts repeated inputs in JSON, JSONL or Parquet form. A correction exits with code 2 and lists dirty UTC partitions. Add --trades validated-trades.json --as-of EPOCH_SECONDS to derive bars and trailing-volume rankings from separately validated USD trades. No-trade bars are omitted; gap coverage is reported separately. Inputs should be bounded batches; this initial canonicalizer materializes a batch in memory.

Initial AWS storage deployment

Deploy only storage.json initially. It creates an encrypted, versioned bucket with public access blocked, TLS required, and retention on stack deletion. The Goldsky writer can write only raw/goldsky/*; its IAM access key is created and passed to the Goldsky sink by the runtime provisioning workflow, never by this template. CloudFormation outputs contain only bucket identity and IAM username.

.local/bin/aws cloudformation validate-template \
  --template-body file://infra/stock-lake/storage.json --region us-east-1
.local/bin/aws cloudformation deploy \
  --template-file infra/stock-lake/storage.json \
  --stack-name canopy-stock-lake-storage --region us-east-1 \
  --capabilities CAPABILITY_IAM
.local/bin/aws cloudformation describe-stacks \
  --stack-name canopy-stock-lake-storage --region us-east-1 \
  --query 'Stacks[0].Outputs' --output json

To opt into AWS account-wide email budget alerts, add --parameter-overrides BudgetEmail=YOUR_EMAIL AwsMonthlyBudgetUsd=75. No email address is embedded. Confirm the notification subscription if AWS requests confirmation. Alerts do not stop spending and cover AWS only; Goldsky usage is metered separately. The bucket name can optionally be supplied through BucketName on the first deployment. Retained buckets and IAM credentials need deliberate lifecycle management if a stack is later retired.

Goldsky ingestion uses bounded EVM Fast Scan jobs, with the upper block condition in each source filter and job: true. Do not use EVM end_block as a stopping condition. Pin the live catalog's actual dataset name and version; do not guess the Robinhood dataset identifier. Run the small verified pool pilot before widening pool coverage or concurrency. Goldsky bounded jobs, supported networks

Use separate landing, snapshot and output prefixes:

Turbo does not perform streaming joins, aggregation or window functions. Keep pool replay, historical rankings, bars, adjustments and US session classification downstream. At-least-once delivery and correction handling remain consumer responsibilities. Turbo SQL, delivery guarantees

Portable research manifests and optional AWS compute

A research manifest references qualified checkpoint JSON files and a point-in-time ranking CSV. It is distinct from a canonical raw-log manifest. Every referenced file requires its SHA-256. rankingAsOf must not exceed selectionCutoff; upstream construction must also establish the actual historical ranking calculation. Checkpoint snapshotId values must match the manifest.

{
  "snapshotId": "CANONICAL_SNAPSHOT_ID",
  "rankingAsOf": 1788220800,
  "selectionCutoff": 1788220800,
  "checkpoints": [{"uri": "s3://BUCKET/published/checkpoints/POOL.json", "sha256": "64_LOWERCASE_HEX_CHARACTERS"}],
  "ranking": {"uri": "s3://BUCKET/published/rankings/AS_OF.csv", "sha256": "64_LOWERCASE_HEX_CHARACTERS"}
}

For local execution, object URIs can instead be local file paths. The wrapper verifies checksums, downloads into an isolated temporary directory and invokes scripts/search-stocklake-grids.py with four workers. Large universes should be partitioned into independent manifests. Retrying a interrupted job replays that manifest from the beginning; successful manifest outputs can be retained while retrying only failed shards. There is no within-job checkpoint/resume mechanism yet.

.local/venv/bin/python infra/stock-lake/run-job.py \
  --manifest .local/stocklake/research-manifest.json \
  --output .local/stocklake/research/result.json --workers 4

The optional images.json stack creates an immutable ECR repository. Build the ARM64 image before creating the compute stack. The Dockerfile pins Python 3.12, analytical package versions, and the AWS CLI archive version/checksum; it copies only required source and calendar files. Credentials come from the ECS task role.

.local/bin/aws cloudformation deploy --template-file infra/stock-lake/images.json \
  --stack-name canopy-stock-lake-images --region us-east-1
.local/bin/aws cloudformation describe-stacks --stack-name canopy-stock-lake-images \
  --region us-east-1 --query 'Stacks[0].Outputs' --output json

Set STOCKLAKE_REPOSITORY_URI and STOCKLAKE_ECR_REGISTRY to that output's repository URI and its registry host, and set STOCKLAKE_IMAGE_TAG to the Git commit identifier. These are non-secret values.

.local/bin/aws ecr get-login-password --region us-east-1 | docker login --username AWS --password-stdin "$STOCKLAKE_ECR_REGISTRY"
docker buildx build --platform linux/arm64 -f Dockerfile.stocklake \
  -t "$STOCKLAKE_REPOSITORY_URI:$STOCKLAKE_IMAGE_TAG" --push .

Resolve the pushed image digest with aws ecr describe-images. Deploy compute using the immutable REPOSITORY_URI@sha256:DIGEST, never a mutable tag:

.local/bin/aws cloudformation validate-template \
  --template-body file://infra/stock-lake/compute.json --region us-east-1
.local/bin/aws cloudformation deploy --template-file infra/stock-lake/compute.json \
  --stack-name canopy-stock-lake-compute --region us-east-1 \
  --capabilities CAPABILITY_IAM \
  --parameter-overrides LakeBucketName=BUCKET ImageUri=REPOSITORY_URI@sha256:DIGEST
.local/bin/aws cloudformation describe-stacks --stack-name canopy-stock-lake-compute \
  --region us-east-1 --query 'Stacks[0].Outputs' --output json
.local/bin/aws batch submit-job --region us-east-1 --job-name stock-lake-snapshot \
  --job-queue QUEUE_OUTPUT --job-definition JOB_DEFINITION_OUTPUT \
  --parameters manifest=s3://BUCKET/manifests/RESEARCH_MANIFEST.json,output=s3://BUCKET/research/RUN_ID/result.json

The compute stack creates two public subnets, an S3 gateway endpoint, no inbound security-group rules and no NAT gateway. HTTPS egress reaches ECR and AWS APIs. Public IPv4 addresses and encrypted gp3 disks are charged while instances exist. Minimum and desired capacity are zero; AWS eventually terminates idle workers. ARM64 EC2 instance types enforce architecture; the Fargate-only RuntimePlatform field is intentionally omitted. Batch runtime-platform scope

Batch can exceed its requested maximum by one instance. We restrict instance sizes to four vCPUs and set MaxvCpus=12 to leave a four-vCPU margin within the 16-vCPU planning bound. Jobs request four vCPUs and 12 GiB, expire after two hours per attempt, and retry at most once. Account Spot quotas and regional capacity can still leave jobs queued. The task role reads only published/manifests/snapshots prefixes and writes only research results. Batch capacity behavior

Cost controls and release criteria

Target combined Goldsky + AWS usage below $250/month, reserving $75 for AWS and $175 for Goldsky until the measured pilot supports a better allocation. These are operating budgets, not guaranteed bills. Stop expanding the backfill if the measured projection exceeds the allowance. Track Goldsky worker-hours and delivered records alongside S3 storage/requests, local downloads, EC2 Spot runtime, EBS, public IPv4, ECR and log retention. Limit the first Goldsky pilot to a $10 estimated allowance; a provider usage delay means this is not an automatic hard stop. Goldsky pricing

Daily during ingestion, inspect provider job progress and Goldsky usage in the project dashboard. Inspect AWS accrued costs using a bounded Cost Explorer query with actual month boundaries:

.local/bin/aws ce get-cost-and-usage --region us-east-1 \
  --time-period Start=YYYY-MM-01,End=YYYY-MM-DD --granularity DAILY \
  --metrics UnblendedCost --group-by Type=DIMENSION,Key=SERVICE
.local/bin/aws batch describe-compute-environments --region us-east-1 \
  --compute-environments COMPUTE_ENVIRONMENT_OUTPUT

Cost Explorer and budget figures can lag. Before each expansion, compare delivered-record counts and elapsed worker-hours with the remaining combined allowance. Cancel queued work if the budget no longer supports it; do not delete raw evidence to hide an incomplete run.

Publish only after duplicate/reorder/reorg tests pass, source ranges reconcile, unsupported venue coverage is explicit, and swap replay matches independent checkpoints. Keep UTC canonical timestamps and use America/New_York for DST-aware sessions and early closes. Current top-100 reference rankings are comparison data; a historical strategy screen requires its own point-in-time ranking artifact. An honest completed scan can report zero robust strategies.

The template guardrail tests run locally. CloudFormation validation and an actual container build are separate release checks; passing JSON/security tests alone does not certify regional capacity or a successfully deployed worker.

Current backfill authorization and scheduling

The current backfill has a $75 combined ceiling, authorized September 14, 2026. This overrides the earlier $10 pilot allocation for further work, while preserving the ongoing monthly target. Prioritize broad raw history for the current top 100, with validation following independently. Use at most two simultaneous bounded Goldsky jobs. Compare one-worker (s) and four-worker (m) jobs on the same scope and blocks before selecting larger workers. Small samples include deployment overhead and cannot establish sustained scan throughput.

scripts/stocklake-benchmark.py ledger records every attempted deployment, including paused and failed runs. Reserve $10 for AWS and unmetered usage; terminal jobs replace their forecast with a conservative estimate only after stable S3 delivery is measured. Preserve original forecast overruns and their explicit re-estimation evidence. This ledger and client watchdog are admission controls, not a provider-enforced billing cap. Pause delays and billing lag remain possible.

Factory discovery must filter stock currencies in SQL before S3 writes. The initial unrestricted factory run was paused after 730,607 delivered creation records; its partial source range is not a complete historical pool registry. Keep its evidence and costs in the ledger. Pool identity, canonical event validation, and execution qualification remain separate gates.

Measured transport choice: Edge for broad history

A matched top-100 sample on blocks 62,580,910–62,590,909 returned 3,889 identical unique log records through Turbo small, Turbo medium, Goldsky Edge, and the independent Robinhood public RPC. Observed elapsed times were 17.72 s, 17.07 s, 2.66 s, and 8.69 s respectively. Turbo timings include deployment and status polling; RPC timings include a chain-ID check and log requests. This sample supports Edge collection but does not establish whole-history throughput or prove arbitrary-range completeness. Detailed artifacts: benchmark-10000.json and rpc-benchmark-10000.json under data/stock-lake/.

Broad history therefore uses bounded, resumable Edge RPC log queries with direct S3 archival, avoiding Turbo sink charges for every log. Edge advertises $5/million requests with all methods priced equally and archive routing. The successful sample used three requests ($0.000015 before allowances). Keep Turbo's independently measured outputs for reconciliation, and keep the stock-filtered factory job for historical pool discovery. Edge pricing and indexing behavior

An earlier Edge sample returned exact duplicate logs; deduplication by block hash, transaction hash and log index reproduced all 373 Turbo records. Preserve conflicting duplicate and removed-log rejection. RPC range completion remains provider evidence until independently checked. Some Robinhood RPC responses contain blockTimestamp, but those values still require canonical header validation. Missing timestamps must remain missing, never estimated from average block duration.

Sieve mode: progressive research and the USDG-only scope (2026-09-14)

The progressive screen (scripts/stocklake-progressive-research.py --interval 60, output data/stock-lake/progressive/summary.json) is a sieve, not a verification step. It surfaces every evaluated configuration with its status, failedGates and warnings so candidates can be inspected on the backfill page while collection is still running. Three relaxations apply and are labelled in the output:

• Rows without exact-block cached decimals use constant per-token decimals from data/stock-lake/token-units.json (fetched once per ranked token by scripts/stocklake-token-units.py); the count is reported as constantDecimalsRows.
• Histories of at least 48 observations spanning 5 days are screened; the 28-day robustness requirement remains a reported gate, not a blocker.
• Price-quality failures are reported as flagged screen_failed rows (priceQualityPass: false) instead of being hidden; they are never promoted to price_only_candidate.

Research decodes only USDG-quoted pools. Goldsky Edge accepts an eth_getLogs topic filter listing every USDG-quoted pool ID for the top-100 scope (about 6,000 IDs), so scripts/stocklake-rpc-backfill.py --usdg-only --run-label <label> collects that subset directly at the provider, roughly fifty times faster than the coarse manager query in dense periods. Labeled collections live under .local/stock-lake/rpc-raw/<collectionId>/ and are read by the progressive scan alongside the production collection; the backfill page's coverage figures track only the unlabeled production job. Trades present in both collections are deduplicated by transaction hash and log index before screening.