Repository navigation
docs backend data model
proposed, no production migrations in this delivery. One PostgreSQL/PostGIS database; table names below are explicit ownership prefixes, not separate services/databases. UTC timestamptz; UUID PKs; money bigint milli-BRL; exact source statistical decimals use numeric. Foreign keys RESTRICT by default; no broad CASCADE that erases history.
Source matching stays behind Directory-owned source_identity.sql queries and
read-only application values. Exact history returns at most two canonical IDs;
base-prefix evidence can only route to review. Operational identifier timestamps
are not business validity windows. Migration102 indexes retained full-CNPJ/base
history. Source publication guards lock expected active identifier/station rows
and recheck canonical continuity through the publication transaction, including
replay; they never create or reassign a station.
-
directory_stations(id,display_name,address,municipality_code,state,status,created_at); canonical ID independent of source identity. -
directory_identifiers(id,station_id,kind,normalized_value,valid_from,valid_to,source_revision_id); unique active(kind,normalized_value); ASCII normalized CNPJ with format/checksum validated by domain. Historical identifier changes remain recorded. -
directory_location_revisions(id,station_id,point geography(Point,4326),quality,provider,source_reference,obtained_at,supersedes_id); append-only; current station location projection stores one reviewed point/quality and revision reference. GiST on current point, B-tree municipality/state; nullable/unverified location must remain explicit. -
directory_condition_qualifiers(id,kind,normalized_name,status)for canonical APP/LOYALTY/OTHER restrictions. Unresolved client text is not an automatically trusted catalog entry.
Geo nearby query uses ST_DWithin on geography (metres), indexed bounding prefilter then distance ordering and stable UUID tie-break. No computed full-table distance sort without indexed radius restriction. Verify plans with realistic density and EXPLAIN ANALYZE. PostGIS documentation defines units and index behavior.
-
official_import_runs(id,source_url,source_checksum,parser_version,discovered_at,status,started_at,finished_at,row_counts,error_summary); unique dataset identity+checksum+parser version; same bytes with changed parser produce a traceable reprocessing revision. -
official_revisions(id,import_run_id,survey_start,survey_end,published_at,supersedes_revision_id,status); unique successful publication per revision ID; an active pointer selects current revision transactionally after complete validation. -
official_station_prices(id,revision_id,station_id,fuel_product,unit,amount_milli_brl,raw_price_text,collected_on,source_row); unique(revision_id,source_row)and domain natural key including collection date/product/unit; duplicates with conflicting source values quarantined, not last-row-wins. -
official_summary_prices(id,revision_id,scope,location_code,fuel_product,unit,mean,min,max,std_dev,station_count,raw_fields); explicit decimal columns, approved source fields only. -
official_rejected_rows(id,import_run_id,row_number,reason_code,redacted_source); scoped retention. Raw source file checksum is immutable; raw public files archived separately if redistribution terms permit.
Indexes: station+fuel+collected_on DESC+revision, revision+station, summary scope/location/fuel/week. Approved read adapters can join directory station IDs, but only Official writes its tables. Partial import never changes the published revision pointer.
-
identity_contributors(id,status,created_at,deleted_at); no email/phone columns in MVP. -
identity_keys(id,contributor_id,algorithm,public_jwk,fingerprint,created_at,revoked_at); unique fingerprint; no private key. -
identity_challenges(id,nonce_hash,key_id_or_fingerprint,purpose,expires_at,consumed_at); atomic consume with conditionconsumed_at IS NULL AND expires_at>server_now; expiry index. -
identity_idempotency(contributor_id,method,route,key,request_hash,response_code,response_json,created_at,expires_at); unique scope; no raw sensitive request body. -
identity_rate_windows(subject_digest,operation,window_start,count,expires_at); unique tuple and atomic UPSERT under transaction. IP digests use rotating keyed hashing and short retention; public key identity is still personal/pseudonymous data.
-
community_observations(id,contributor_ref,client_submission_id,station_id,fuel_product,unit,amount_milli_brl,condition_kind,qualifier_id,evidence_id,received_at,claimed_captured_at,supersedes_id,policy_version); immutable core. Unique contributor_ref+client_submission_id; indexes price key+received_at and contributor_ref+received_at. Check constraints mirror structural price/unit/condition rules. -
community_private_observation_data(observation_id,encrypted_location,accuracy_m,fix_age_ms,ocr_claims,expires_at); separate short-lived personal data; restricted role. Delete after derivation within 24 h, never copy into events. -
community_observation_decisions(id,observation_id,sequence,to_state,reason_codes,policy_version,occurred_at,actor_ref); unique observation+sequence. Latest state/signal projection is mutable and rebuildable. -
community_confirmations(id,observation_id,contributor_ref,client_submission_id,received_at); unique observation+contributor and contributor+client submission. Eligibility joins use documented read port; attribution separate from publicly visible content. -
community_disputes(id,target_observation_id,contributor_ref,client_submission_id,reason,replacement_id,received_at)and append-only dispute decision history; partial unique open-report projection per reporter/target/reason. -
community_current_prices(station_id,fuel_product,unit,condition_kind,qualifier_key,amount_milli_brl,availability,confidence,representative_observation_id,independent_supporters,confirmation_count,anchor_received_at,expires_at,next_recompute_at,computed_at,projection_version,algorithm_version,policy_config_version); composite PK uses nonnullable qualifier_key (STANDARDuses a documented sentinel) so NULL uniqueness cannot create duplicate rows. Index expires_at and station+fuel. Representative observation matches displayed amount/condition and is revalidated on confirmation. No private coordinates or contributor IDs. -
community_projection_inputs(projection_key,version,input_cutoff,supporting_event_ids,reason_codes); restricted explanation snapshot with bounded retention; enough to reproduce while permitted source signals exist. After privacy erasure, record that full replay is no longer possible rather than keeping personal data indefinitely.
Queries need a narrow current-state/trust/evidence snapshot through declared ports/read adapters. No handler loads millions of facts per GET. contributor_ref is a random attribution token, not the public contributor UUID; a restricted identity-owned mapping resolves it for ownership/trust and deletion. Removing that mapping is not automatically anonymization: if retained content remains identifying/linkable, delete/anonymize it too under the reviewed policy. Normal business writes cannot change attribution; the narrow privacy workflow can remove it. Append-only protects business corrections, not an exemption from data rights.
- Evidence:
evidence_uploads(id,owner_ref,client_submission_id,quarantine_key,declared_size,declared_sha256,state,expires_at);evidence_objects(id,upload_id,owner_ref,final_key,verified_sha256,perceptual_hash,mime,width,height,size_bytes,ready_at,delete_after,deleted_at). Unique upload/client command; one active binding per observation/evidence. Object key is server-generated and private. Verified hash index for duplicate signals. - Trust:
trust_decisions(id,contributor_ref,tier,reason,case_refs,policy_version,occurred_at),trust_current(contributor_ref,tier,version,updated_at); immutable decisions, rebuildable current view. - Moderation:
moderation_cases(id,target_type,target_id,status,priority,opened_at),moderation_actions(id,case_id,actor_id,action,reason,occurred_at,request_id); index open priority/date; no arbitrary payload dump or unbounded GPS copy. - Platform:
jobs(id,type,payload_version,payload,status,dedupe_key,attempts,scheduled_at,lease_until,lease_token,started_at,finished_at,last_error_code); unique dedupe key; partial ready index(scheduled_at,id)where queued, lease index for expired running jobs. Payload includes IDs, no sensitive media/location. - Privacy:
privacy_requests(id,contributor_ref,type,status,created_at,completed_at), minimalprivacy_deletion_ledgerneeded to reapply removals after restore; restricted access. Ledger itself has a documented retention bounded by backup horizon and legal need. - Migrations: ordered version/checksum ledger; dedicated migration role, advisory lock and checksum mismatch failure.
Observation insert + initial event + idempotency result + validation job is one transaction. Confirmation unique insert + idempotency + consensus job is one transaction. Moderation action + domain decision + recomputation jobs is one transaction. Cross-module application orchestration can share a transaction through an explicitly owned unit of work; no nested autonomous commits.
FK relationships enforce existence without handing ownership to callers. Optimistic version checks protect aggregate decisions; per-price-key locks serialize projection publication. Unique constraints arbitrate concurrent first creation/confirmations; don't rely on check-then-insert application code. API/worker use separate least-privilege roles; raw observation price updates are unavailable through normal repositories/roles. Personal-data cleanup has explicit narrow privileges.
See SECURITY_PRIVACY for the authoritative proposed retention periods. Initial sizing assumptions: 20k station-price rows/week means ~1.04M/year before corrections; 1,000 observations/day means 365k/year plus votes/events; 10,000/day means 3.65M/year. These are workload scenarios, not actual current usage. Measure relation/index size and WAL amplification from seeded data.
Do not partition v1. Reassess time partitions when retention deletion/vacuum, index size or p95 queries miss budgets after indexing/tuning. A future partition design must preserve natural uniqueness, FKs and idempotency across partitions; document it in an ADR before migration. Object storage growth depends on photo rate×average bytes×retention, not MAU alone.
Migrations are SQL and append-only after application. Every migration includes forward change, lock estimate, compatibility with old application version, recovery/rollback plan and tests from empty plus previous supported schema. Irreversible data transformations require backup and explicit operator review. Expand→deploy→backfill→validate→contract; rolling back a binary must not require deleting user data.
Community owns CityFeed in its existing sqlc package. Its read-only join to directory_stations selects only public canonical id/name and exact municipality/state/active status. Existing declared directory schema and city index are reused. Append-only migration 000041 adds rebuildable partial recent/cheapest projection indexes; no private observation/media/identity fields enter the feed, and no write ownership changes. Current AVAILABLE STANDARD prices expire at query time; each filter compares one canonical fuel/unit. HMAC keyset cursors and bounded pages remain live under concurrent projection updates.
Quality owns its editions, published/corrected revisions, frozen resources, manifest counts, period claims, assays, associations and private reports. Public reads carry a selected frozen revision lease. Archive parent and exact monthly member hashes/bytes are distinct provenance (migration100); legacy members stay absent. The stream current pointer/watermark cannot regress on late history.
quality_assays.source_observed_at (migration101) preserves the optional original
unzoned native clock; observed_on is its validated calendar day. Old/date-only
rows keep NULL. Native fingerprints/sample scopes retain the full clock. Quality
publication consumes Directory-owned read/guard ports through composition; it
never imports Directory generated SQL or writes canonical identities. Native
worker activation is a separate scoped acceptance obligation.
- Home
- Roadmap
- docs-README
- docs-android-build
- docs-android-design-system
- docs-android-forum
- docs-android-games
- docs-android-map-engine
- docs-android-navigation
- docs-android-ocr-image
- docs-android-ocr-live-framing
- docs-android-premium-area
- docs-android-premium-integration
- docs-android-release
- docs-android-ui-containers
- docs-architecture-android-stack
- docs-architecture-android
- docs-architecture-backend
- docs-architecture-data-sources
- docs-architecture-overview
- docs-architecture-sources-ev-research
- docs-architecture-sources-national-limits
- docs-architecture-sources-national-stations
- docs-architecture-sources-register
- docs-assets-app-tutorial-CAPTURE_CONTRACT
- docs-assets-app-tutorial-FABLE_LANDING_POLISH_PROMPT
- docs-assets-app-tutorial-README
- docs-assets-attribution
- docs-assets-brand-README
- docs-assets-flags-ATTRIBUTION
- docs-assets-fuel-icons-README
- docs-assets-games-fuel-v1-README
- docs-assets-home-icons-README
- docs-assets-landing-fleet-android-20261012-BRIEF
- docs-assets-landing-hero-20261011-BRIEF
- docs-assets-landing-illustrated-20261012-BRIEF
- docs-assets-landing-illustrated-20261012-README
- docs-assets-landing-minimal-20261012-BRIEF
- docs-assets-landing-open-data-20261026-BRIEF
- docs-assets-landing-redesign-20261011-PREPARATION_EVIDENCE
- docs-assets-landing-redesign-20261011-README
- docs-assets-landing-redesign-20261011-REFERENCE_BRIEF
- docs-assets-landing-sync-20261012-BRIEF
- docs-assets-map-icons-README
- docs-backend-anp-ingestion
- docs-backend-api
- docs-backend-community-pricing
- docs-backend-data-model
- docs-backend-domain-model
- docs-backend-driver-private-data
- docs-backend-forum-architecture
- docs-backend-forum-bulletins
- docs-backend-forum-sources
- docs-backend-payments-provider-admission
- docs-backend-payments-provider-register
- docs-backend-photos-capture
- docs-backend-photos-subset-submission
- docs-backend-premium-api
- docs-backend-premium-cloud-protocol
- docs-backend-testing
- docs-backend-toolchain
- docs-brand-app-icons
- docs-brand-identity
- docs-decisions-001-kotlin-compose-stack
- docs-decisions-002-price-import-immutability
- docs-decisions-003-nominatim-reverse-geocode
- docs-decisions-005-modular-monolith-and-postgresql
- docs-decisions-006-official-catalog-and-exact-contracts
- docs-decisions-007-anonymous-proof-of-possession
- docs-decisions-008-privacy-and-retention
- docs-decisions-009-community-consensus-and-projections
- docs-decisions-010-private-object-evidence
- docs-decisions-012-licensing-and-hosted-business
- docs-decisions-015-kotlin-multiplatform-shared-domain
- docs-decisions-016-android-commercial-community-ios-deferred
- docs-decisions-017-station-profile-and-verified-representation
- docs-decisions-021-community-first-contribution
- docs-decisions-022-comaps-derived-native-map-engine
- docs-decisions-023-admin-suite-granted-tiers-contributors
- docs-decisions-025-private-abastevo-empresas-organization-boundary
- docs-decisions-026-multilingual-public-site
- docs-decisions-README
- docs-enterprise-README
- docs-enterprise-architecture
- docs-enterprise-backlog-INDEX
- docs-enterprise-backlog-ep00-governance
- docs-enterprise-backlog-ep01-platform
- docs-enterprise-backlog-ep02-organizations
- docs-enterprise-backlog-ep03-identity-access
- docs-enterprise-backlog-ep04-sync
- docs-enterprise-backlog-ep05-fleet-meters
- docs-enterprise-backlog-ep06-trips
- docs-enterprise-backlog-ep07-gps-navigation
- docs-enterprise-backlog-ep08-fuelings
- docs-enterprise-backlog-ep09-documents
- docs-enterprise-backlog-ep10-expenses-finance
- docs-enterprise-backlog-ep11-maintenance
- docs-enterprise-backlog-ep12-fuel-stock
- docs-enterprise-backlog-ep13-intelligence
- docs-enterprise-backlog-ep14-station-claims
- docs-enterprise-backlog-ep15-station-commerce
- docs-enterprise-backlog-ep16-subscriptions
- docs-enterprise-backlog-ep17-data
- docs-enterprise-backlog-ep18-imports-notifications-integrations
- docs-enterprise-backlog-ep19-back-office
- docs-enterprise-backlog-ep20-release
- docs-enterprise-business-rules
- docs-enterprise-contract
- docs-enterprise-conventions
- docs-enterprise-data-offering
- docs-enterprise-decisions
- docs-enterprise-domain-model
- docs-enterprise-glossary
- docs-enterprise-journeys
- docs-enterprise-modules-back-office
- docs-enterprise-modules-data
- docs-enterprise-modules-documents
- docs-enterprise-modules-expenses-finance
- docs-enterprise-modules-fleet
- docs-enterprise-modules-fuel-stock
- docs-enterprise-modules-fuelings
- docs-enterprise-modules-gps-navigation
- docs-enterprise-modules-identity
- docs-enterprise-modules-imports-notifications-integrations
- docs-enterprise-modules-intelligence
- docs-enterprise-modules-maintenance
- docs-enterprise-modules-organizations
- docs-enterprise-modules-station-claims
- docs-enterprise-modules-station-commerce
- docs-enterprise-modules-subscriptions
- docs-enterprise-modules-trips
- docs-enterprise-offline-sync
- docs-enterprise-permissions
- docs-enterprise-portal-routes
- docs-enterprise-privacy-security
- docs-enterprise-product-vision
- docs-enterprise-source-admission
- docs-enterprise-state-model
- docs-enterprise-testing
- docs-enterprise-ux-app
- docs-enterprise-ux-web
- docs-operations-README
- docs-operations-backup
- docs-operations-deploy-agent
- docs-operations-dou-editions
- docs-operations-edge-cache
- docs-operations-enterprise-activation
- docs-operations-map-static-overlay
- docs-operations-moderation-feedback
- docs-operations-moderation-profiles
- docs-operations-monitoring
- docs-operations-operator-access
- docs-operations-payments
- docs-operations-premium-billing
- docs-operations-recovery
- docs-operations-registry-daily-sync
- docs-operations-registry-runs
- docs-product-README
- docs-product-admin-tiers
- docs-product-community-community-first
- docs-product-community-experience
- docs-product-community-metrics
- docs-product-community-moderation
- docs-product-community-safety
- docs-product-community-terms-pt-br
- docs-product-driver-suite-README
- docs-product-driver-suite-android-state
- docs-product-driver-suite-cloud-sync
- docs-product-driver-suite-cost-profiles-and-maintenance
- docs-product-driver-suite-driver-model-and-earnings
- docs-product-driver-suite-foundation-decisions
- docs-product-driver-suite-gps-lifecycle
- docs-product-driver-suite-mobile-hub
- docs-product-driver-suite-odometer-review
- docs-product-driver-suite-outbox-wire
- docs-product-driver-suite-quick-refuel
- docs-product-driver-suite-reports
- docs-product-driver-suite-shifts-and-distance
- docs-product-driver-suite-signed-commands
- docs-product-ev-README
- docs-product-ev-charging-stop-planner
- docs-product-ev-energy-model
- docs-product-ev-financial-method
- docs-product-ev-journey-ui
- docs-product-ev-preparation
- docs-product-ev-typed-data
- docs-product-exploration
- docs-product-forum-contract
- docs-product-forum-policies
- docs-product-glossary
- docs-product-landing-content
- docs-product-landing-guides
- docs-product-landing-organic-growth-i18n-plan
- docs-product-licensing
- docs-product-map-discovery
- docs-product-map-favorites-and-trips
- docs-product-map-reports-routing-guidance
- docs-product-notification-content
- docs-product-official-prices
- docs-product-open-source-business
- docs-product-overview
- docs-product-payments-README
- docs-product-payments-offer-policy
- docs-product-premium-README
- docs-product-premium-financial-method
- docs-product-premium-source-policy
- docs-product-stations-catalog
- docs-product-stations-fuel-feedback
- docs-product-stations-fuel-quality
- docs-product-stations-identity-and-ev-catalog
- docs-product-stations-profile-contract
- docs-product-stations-profile-representation
- docs-product-stations-registry-sources
- docs-product-vehicle-registration
- docs-product-vehicles-and-occurrences
- docs-security-android-terms-acceptance
- docs-security-free-account-access
- docs-security-identity-profile
- docs-security-media-location-policy
- docs-security-privacy-notice
- docs-security-privacy-policy
- docs-security-retention
- docs-security-risks
- docs-security-security-privacy
- docs-store-README
- docs-store-play-readiness
- docs-store-play-store-listing
- docs-store-ugc-safety
Source: AlexandreZanata/Abastevo @ e4a67580fc368a59bc1c0f1e0466fc011313f330