Data structures and storage
The source-to-report lineage, current core fields, storage responsibilities and schema coverage still to prove.
Source-of-truth map
| Record | Current source | Required relationship |
|---|---|---|
| Selectable catalog | hosted-app/content/pull-cost-catalog.json | Research menu, units, rates and source references; listing a module does not make it executable. |
| Execution levels and capabilities | hosted-app/content/pull-execution-levels.json; hosted-app/lib/pull-capabilities.mjs | Snapshot/Market/Deep defaults resolve to explicitly supported items and bounded quantities. |
| Immutable execution plan | hosted-app/lib/pull-execution-plan.mjs | Authoritative domain, market, selection, adapters, source revision, caps and plan hash. |
| Request ledger and execution receipt | hosted-app/lib/pull-request-ledger.mjs | Reservations, owned tasks, responses, uncertain spend and selected-item coverage remain bound to the plan. |
| Queue persistence | hosted-app/lib/comprehensive-pull-queue.mjs; hosted-app/lib/comprehensive-pull-queue-runner.mjs | Claim, lease, checkpoint and progress must agree across process loss and stale writers. |
| Capture and report projection | hosted-app/lib/comprehensive-pull.mjs; hosted-app/lib/local/customer-payload.mjs | Preserve evidence and receipts while removing private raw fields from customer output. |
| Exported brand system | src/export/client-brand-system.mjs; hosted-app/lib/report-downloads.mjs | Portable output is a versioned view of the same capture, not a second truth. |
| Database and retention | docs/supabase-brand-studio-pulls.sql; docs/supabase-brand-studio-retention.sql | Ownership, relationships, retention and deletion effects must match live schema before acceptance. |
Execution plan dictionary
| Field | Current type or meaning | Invariant |
|---|---|---|
| schema_version | brand-studio-execution/v1 | Unknown versions must not be interpreted as compatible by guess. |
| execution_contract | Object: adapter_version, source_sha256, capability_ids | Bind the plan to selected-plan-dispatch/v1 and the exercised capability source digest. |
| rate_version | Version string; observed dataforseo-2026-09-10 | DataForSEO rates remain source-versioned in code. Any credit-priced provider basis is separately refused when its verified_at evidence is older than 30 days or more than five minutes in the future. |
| domain | Normalized public hostname string | Exact confirmed target; reject private/reserved destinations at the network boundary. |
| market | Object: location_code integer; language_code string; confirmed true | US/English is a reviewed database choice, not the company address. |
| preset / selection | Preset label and normalized items/groups/options | Missing items mean off. An empty object or array is refused with no_executable_items_selected; do not silently clamp quantities or activate defaults. |
| rows[] | item_id, status, leg, groups, max_cost_usd, rate_id, source_ids as applicable | Keep zero-cost and unselected rows; reject unsupported purchased scope. |
| legs | Bounded configurations for SEO, backlinks, reviews, website, social, site evidence and ads | Every executable leg carries matching domain, market, cap and plan_id. |
| max_cost_usd / price_kind | Number and maximum_provider_cost_not_guaranteed_charge | A maximum is not the actual provider invoice. |
| customer_credit_price | Object: credits, basis, tier, byo_discount_resolved_at_reservation | Credits are separate from provider dollars; final reservation resolves applicable discount. |
| plan_hash | SHA-256 digest of the canonical payload | Changing bindings, quantities, rate/source identity or scope invalidates prior quote approval. |
Receipt dictionary
| Field family | Current observations | Required verification |
|---|---|---|
| Identity | schema_version, plan_hash, domain, market | Report, export, saved row and API agree on the same plan and run. |
| Cost | actual_cost_usd, known_cost_usd, held_cost_usd, max_cost_usd, unknown_requests | Unknown remains unknown; held amounts cannot be silently released after ambiguous outcomes. |
| Requests | Attempt, endpoint, method, target, task ID, phase, request cap, actual cost and response hash when present | Persist reservation before purchase and response evidence before further paid work. |
| Coverage | item_id, requested_groups, status; SEO group requested/returned/total_available/stop_reason/truncated | Evaluate group identity and count lineage, not just a passing leg flag. |
| Account reconciliation | account_balance_movement_usd, account_reconciliation and per-provider before/after movement metadata | The queue captures selected billed providers before dispatch and after all stages settle. Shared-account movement stays explicitly unattributed and cannot replace request receipts. |
| Customer billing | customer_credit_price plus separate stored ledger entries | Prove reservation, settlement, refund and final balance against the actual billing rules. |
Storage and deletion
Source SQL defines brand_studio_brands and brand_studio_pulls with user ownership and composite relationship enforcement. The inspected pulls schema references auth.users with ON DELETE CASCADE. Deleting an evaluation identity can therefore remove the evidence needed to reconcile its run.
The complete-credit reader calls brand_studio_ledger_snapshot with p_user_id and validates entries, row_count, recognized entry kinds and bounded finite amounts. Missing or inconsistent data is a failure, never a zero balance.
Before changing persistence, measure serialized bytes and write latency at claim, per-request checkpoint, stage completion and finalization. Distinguish duplicate resume state, JSON serialization, database transaction time and transport failure.
Required design: preserve full approved evidence without repeatedly rewriting unbounded raw bodies. The current local repair uses two compatible controls inside the existing JSON column: release only an exact settled duplicate, and gzip only large in-progress SEO/site-evidence resume bodies in a versioned, checksummed envelope. No schema migration or object-store dependency is introduced.
Opus 5 checkpoint contract, revision 2 is implemented locally: only SEO/site-evidence scratch is considered for release, only when the joint SEO owner passed, the full retained checkpoint is exactly equal, and per-result plus per-request costs are known. Partial/unavailable results, uncertain spend, pending work and crawl refunds awaiting reconciliation retain scratch. The original Boolean-settled proposal remains rejected because a placeholder leg can otherwise erase the only partial evidence.
Release shares the existing fenced database write with its full retained result and is adopted only after exactly one row confirms persistence. Focused tests preserve attempts, totals, unknown fields and idempotence. A disposable 5,000-row PostgreSQL proof reduced HTTP JSON from 4,603,811 to 3,069,523 bytes and jsonb from 1,409,243 to 939,756 bytes. The in-progress envelope validates declared sizes and checksum and fails closed before dispatch when corrupt; a queue-level test proves the fenced writer stores the compressed envelope and the next claimed slice restores the collector's exact original JSON shape.
Required retention: preserve the evaluation account, run, request ledger and report until reconciliation and independent verification are archived. Define expiry, ownership, cleanup and failure behavior; do not silently disable ordinary retention forever.
Field coverage obligation
The dictionary above covers the inspected core plan and receipt; it does not yet certify every nested provider, normalized report or database field. Every remaining record must document type, required/null/default semantics, cardinality, units, provenance, producer, transformations, consumers, version and compatibility behavior.
For each loss or transformation, record a reason: deduplication, normalization, privacy removal, unsupported value or source absence. Validate referential integrity and byte-preserving raw evidence where retention permits it.
Inventory generated schema filenames separately from proof that real producer output conforms to those schemas. A schema that is never exercised against a real boundary is incomplete coverage.
Required cost and retention invariants
Implemented local contract: nullable account_balance_movement_usd and explicit account_reconciliation travel together. A zero-provider selected plan records not_applicable_no_billed_provider and zero movement; a paid selected plan captures only its billed provider accounts immediately before dispatch and after all stages settle. Missing or non-comparable observations record observation_incomplete, never zero. Comparable movement is labeled observed_shared_account_delta_not_attributed because another process may share the provider account. Estimated, maximum, held/reserved, provider-reported actual and account movement remain separate bases.
A paid acceptance receipt requires immediate before/after provider evidence, attributable requests and a reconciled customer-credit ledger. Shared-account movement alone is insufficient when other activity may exist. If attribution remains uncertain, reconciliation stays incomplete and the run fails the financial acceptance gate.
Do not delete identities or rows for funded evaluation runs before evidence and reconciliation are durably retained. A tombstone or retention-schema change is a candidate for reviewed implementation, not an applied migration. Existing deletion cascades are a known gap, not an acceptable audit trail.