FreshBoB PostgreSQL 16 Final ERD
Tables (53)
addresses
- address_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- label — TEXT — NOT NULL
- recipient_name — TEXT — NOT NULL
- phone — TEXT — NOT NULL
- postal_code — TEXT — NOT NULL
- address1 — TEXT — NOT NULL
- address2 — TEXT
- entrance_method — TEXT
- building_type — TEXT — NOT NULL
- entrance_type — TEXT — NOT NULL
- entrance_code_encrypted — TEXT
- dropoff_location — TEXT — NOT NULL
- contact_preference — TEXT — NOT NULL
- ring_doorbell — BOOLEAN — NOT NULL
- knock_allowed — BOOLEAN — NOT NULL
- fallback_instruction — TEXT
- is_default — BOOLEAN — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
- latitude — REAL
- longitude — REAL
- selected_hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id
audit_logs
- audit_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT
- action — TEXT — NOT NULL
- target_type — TEXT — NOT NULL
- target_id — TEXT
- request_id — TEXT
- ip_masked — TEXT
- user_agent_hash — TEXT
- payload_hash — TEXT
- created_at — TIMESTAMPTZ — NOT NULL
auth_identities
- auth_identity_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- provider — TEXT — NOT NULL
- provider_subject — TEXT — NOT NULL
- email_at_link — TEXT — NOT NULL
- linked_at — TIMESTAMPTZ — NOT NULL
- last_login_at — TIMESTAMPTZ — NOT NULL
cart_items
- cart_item_id — TEXT — Primary key — NOT NULL
- cart_id — TEXT — Foreign key → carts.cart_id — NOT NULL
- sku_id — TEXT — Foreign key → skus.sku_id — NOT NULL
- quantity — INTEGER — NOT NULL
- price_observed — INTEGER — NOT NULL
- promise_id — TEXT
carts
- cart_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id
- guest_token — TEXT
- hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id
- status — TEXT — NOT NULL
- version — INTEGER — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
categories
- category_id — TEXT — Primary key — NOT NULL
- name — TEXT — NOT NULL
- icon — TEXT — NOT NULL
- sort_order — INTEGER — NOT NULL
checkout_quotes
- quote_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- cart_id — TEXT — Foreign key → carts.cart_id — NOT NULL
- cart_version — INTEGER — NOT NULL
- address_id — TEXT — Foreign key → addresses.address_id — NOT NULL
- hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id
- status — TEXT — NOT NULL
- consumed_at — TIMESTAMPTZ
- consumed_by_order_id — TEXT
- quote_json — JSONB — NOT NULL
- expires_at — TIMESTAMPTZ — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
claim_evidence
- claim_evidence_id — TEXT — Primary key — NOT NULL
- claim_id — TEXT — Foreign key → claims.claim_id — NOT NULL
- asset_id — TEXT — Foreign key → upload_assets.asset_id — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
claims
- claim_id — TEXT — Primary key — NOT NULL
- order_item_id — TEXT — Foreign key → order_items.order_item_id — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- reason_code — TEXT — NOT NULL
- quantity — INTEGER — NOT NULL
- description — TEXT — NOT NULL
- remedy — TEXT — NOT NULL
- status — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- resolved_at — TIMESTAMPTZ
consents
- consent_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- policy_type — TEXT — NOT NULL
- version — TEXT — NOT NULL
- granted — BOOLEAN — NOT NULL
- channel — TEXT — NOT NULL
- occurred_at — TIMESTAMPTZ — NOT NULL
- document_version — TEXT
- scope_id — TEXT
- order_id — TEXT
- details_json — JSONB — NOT NULL
customers
- customer_id — TEXT — Primary key — NOT NULL
- primary_email — TEXT — NOT NULL
- email_verified — BOOLEAN — NOT NULL
- display_name — TEXT — NOT NULL
- profile_image_url — TEXT
- status — TEXT — NOT NULL
- withdrawal_requested_at — TIMESTAMPTZ
- personal_data_purge_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
delivery_events
- delivery_event_id — TEXT — Primary key — NOT NULL
- shipment_id — TEXT — Foreign key → shipments.shipment_id — NOT NULL
- external_event_id — TEXT — NOT NULL
- sequence — INTEGER — NOT NULL
- status — TEXT — NOT NULL
- occurred_at — TIMESTAMPTZ — NOT NULL
- description — TEXT — NOT NULL
devices
- device_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- platform — TEXT — NOT NULL
- push_token_encrypted — TEXT
- app_version — TEXT — NOT NULL
- notifications_enabled — BOOLEAN — NOT NULL
- last_seen_at — TIMESTAMPTZ — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
faq_categories
- category_code — TEXT — Primary key — NOT NULL
- name — TEXT — NOT NULL
- sort_order — INTEGER — NOT NULL
faqs
- faq_id — TEXT — Primary key — NOT NULL
- category_code — TEXT — Foreign key → faq_categories.category_code — NOT NULL
- question — TEXT — NOT NULL
- answer — TEXT — NOT NULL
- sort_order — INTEGER — NOT NULL
- published — BOOLEAN — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
favorites
- favorite_id — TEXT — Primary key — NOT NULL
- product_id — TEXT — Foreign key → products.product_id — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
fulfillment_hubs
- hub_id — TEXT — Primary key — NOT NULL
- name — TEXT — NOT NULL
- address — TEXT — NOT NULL
- postal_code — TEXT — NOT NULL
- latitude — REAL — NOT NULL
- longitude — REAL — NOT NULL
- delivery_radius_km — REAL — NOT NULL
- eta_minutes — INTEGER — NOT NULL
- minimum_order — INTEGER — NOT NULL
- delivery_fee — INTEGER — NOT NULL
- free_shipping_threshold — INTEGER — NOT NULL
- status — TEXT — NOT NULL
- sort_order — INTEGER — NOT NULL
hub_sku_inventory
- hub_inventory_id — TEXT — Primary key — NOT NULL
- sku_id — TEXT — Foreign key → skus.sku_id — NOT NULL
- hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id — NOT NULL
- available_quantity — INTEGER — NOT NULL
- reserved_quantity — INTEGER — NOT NULL
- safety_stock — INTEGER — NOT NULL
- sale_status — TEXT — NOT NULL
- version — INTEGER — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
hub_sku_offers
- hub_offer_id — TEXT — Primary key — NOT NULL
- sku_id — TEXT — Foreign key → skus.sku_id — NOT NULL
- hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id — NOT NULL
- list_price — INTEGER
- sale_price — INTEGER
- max_quantity — INTEGER
- sale_status — TEXT — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
idempotency_records
- idempotency_record_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- request_hash — TEXT
- response_json — JSONB — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- idempotency_key — TEXT — NOT NULL
inquiry_evidence
- inquiry_evidence_id — TEXT — Primary key — NOT NULL
- inquiry_id — TEXT — Foreign key → support_inquiries.inquiry_id — NOT NULL
- asset_id — TEXT — Foreign key → upload_assets.asset_id — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
inventory_movements
- movement_id — TEXT — Primary key — NOT NULL
- hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id — NOT NULL
- sku_id — TEXT — Foreign key → skus.sku_id — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id
- reservation_id — TEXT — Foreign key → inventory_reservations.reservation_id
- movement_type — TEXT — NOT NULL
- quantity_delta — INTEGER — NOT NULL
- available_before — INTEGER — NOT NULL
- available_after — INTEGER — NOT NULL
- reserved_before — INTEGER — NOT NULL
- reserved_after — INTEGER — NOT NULL
- reference_key — TEXT — NOT NULL
- metadata_json — JSONB — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
inventory_reservations
- reservation_id — TEXT — Primary key — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id — NOT NULL
- hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id — NOT NULL
- sku_id — TEXT — Foreign key → skus.sku_id — NOT NULL
- quantity — INTEGER — NOT NULL
- status — TEXT — NOT NULL
- expires_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
- released_at — TIMESTAMPTZ
notification_preferences
- notification_preference_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- order_email — BOOLEAN — NOT NULL
- delivery_email — BOOLEAN — NOT NULL
- marketing_email — BOOLEAN — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
- order_push — BOOLEAN — NOT NULL
- delivery_push — BOOLEAN — NOT NULL
- claim_push — BOOLEAN — NOT NULL
- marketing_push — BOOLEAN — NOT NULL
notifications
- notification_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- type — TEXT — NOT NULL
- title — TEXT — NOT NULL
- body — TEXT — NOT NULL
- target_path — TEXT
- read_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
oauth_states
- state_hash — TEXT — Primary key — NOT NULL
- nonce — TEXT — NOT NULL
- verifier — TEXT — NOT NULL
- return_to — TEXT — NOT NULL
- expires_at — TIMESTAMPTZ — NOT NULL
- used_at — TIMESTAMPTZ
order_items
- order_item_id — TEXT — Primary key — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id — NOT NULL
- sku_id — TEXT — Foreign key → skus.sku_id — NOT NULL
- product_id — TEXT — NOT NULL
- product_name — TEXT — NOT NULL
- option_name — TEXT — NOT NULL
- quantity — INTEGER — NOT NULL
- unit_price — INTEGER — NOT NULL
- line_total — INTEGER — NOT NULL
- product_snapshot — JSONB — NOT NULL
- promise_snapshot — JSONB — NOT NULL
- seller_id — TEXT — Foreign key → planned_sellers.seller_id — NOT NULL
order_number_sequences
- order_date — DATE — Primary key — NOT NULL
- last_value — INTEGER — NOT NULL
orders
- order_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- order_number — TEXT — NOT NULL
- status — TEXT — NOT NULL
- fulfillment_hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id
- checkout_quote_id — TEXT — Foreign key → checkout_quotes.quote_id
- address_snapshot — JSONB — NOT NULL
- subtotal — INTEGER — NOT NULL
- discount — INTEGER — NOT NULL
- shipping_fee — INTEGER — NOT NULL
- total_amount — INTEGER — NOT NULL
- recipient_message — TEXT
- placed_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
payment_intents
- payment_intent_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- quote_id — TEXT — Foreign key → checkout_quotes.quote_id — NOT NULL
- external_order_id — TEXT — NOT NULL
- order_name — TEXT — NOT NULL
- expected_amount — INTEGER — NOT NULL
- quoted_amount — INTEGER — NOT NULL
- recipient_message — TEXT
- consents_json — JSONB — NOT NULL
- status — TEXT — NOT NULL
- payment_key — TEXT
- order_id — TEXT — Foreign key → orders.order_id
- failure_code — TEXT
- failure_message — TEXT
- expires_at — TIMESTAMPTZ — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- confirmed_at — TIMESTAMPTZ
payments
- payment_id — TEXT — Primary key — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id — NOT NULL
- provider — TEXT — NOT NULL
- provider_tx_id — TEXT — NOT NULL
- amount — INTEGER — NOT NULL
- status — TEXT — NOT NULL
- approved_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
- method — TEXT
- receipt_url — TEXT
planned_delivery_partners
- delivery_partner_id — TEXT — Primary key — NOT NULL
- partner_name — TEXT — NOT NULL
- provider_code — TEXT — NOT NULL
- contract_status — TEXT — NOT NULL
- effective_from — DATE — NOT NULL
- effective_to — DATE
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
planned_delivery_settlement_items
- delivery_settlement_item_id — TEXT — Primary key — NOT NULL
- delivery_settlement_id — TEXT — Foreign key → planned_delivery_settlements.delivery_settlement_id — NOT NULL
- item_type — TEXT — NOT NULL
- amount — INTEGER — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- shipment_id — TEXT — Foreign key → shipments.shipment_id — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id — NOT NULL
planned_delivery_settlements
- delivery_settlement_id — TEXT — Primary key — NOT NULL
- delivery_partner_id — TEXT — Foreign key → planned_delivery_partners.delivery_partner_id — NOT NULL
- period_start — DATE — NOT NULL
- period_end — DATE — NOT NULL
- gross_amount — INTEGER — NOT NULL
- deduction_amount — INTEGER — NOT NULL
- net_amount — INTEGER — NOT NULL
- status — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
planned_seller_contracts
- seller_contract_id — TEXT — Primary key — NOT NULL
- seller_id — TEXT — Foreign key → planned_sellers.seller_id — NOT NULL
- contract_status — TEXT — NOT NULL
- effective_from — DATE — NOT NULL
- effective_to — DATE
- terminated_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
planned_seller_skus
- seller_sku_id — TEXT — Primary key — NOT NULL
- seller_id — TEXT — Foreign key → planned_sellers.seller_id — NOT NULL
- sku_id — TEXT — Foreign key → skus.sku_id — NOT NULL
- seller_sku_code — TEXT
- status — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
planned_sellers
- seller_id — TEXT — Primary key — NOT NULL
- hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id
- seller_name — TEXT — NOT NULL
- business_number_masked — TEXT
- status — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
planned_settlement_items
- settlement_item_id — TEXT — Primary key — NOT NULL
- settlement_id — TEXT — Foreign key → planned_settlements.settlement_id — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id — NOT NULL
- payment_id — TEXT — Foreign key → payments.payment_id
- refund_id — TEXT — Foreign key → refunds.refund_id
- item_type — TEXT — NOT NULL
- amount — INTEGER — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- order_item_id — TEXT — Foreign key → order_items.order_item_id — NOT NULL
planned_settlements
- settlement_id — TEXT — Primary key — NOT NULL
- seller_id — TEXT — Foreign key → planned_sellers.seller_id — NOT NULL
- period_start — DATE — NOT NULL
- period_end — DATE — NOT NULL
- gross_amount — INTEGER — NOT NULL
- deduction_amount — INTEGER — NOT NULL
- net_amount — INTEGER — NOT NULL
- status — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
producers
- producer_id — TEXT — Primary key — NOT NULL
- name — TEXT — NOT NULL
- region — TEXT — NOT NULL
- story_summary — TEXT — NOT NULL
- status — TEXT — NOT NULL
products
- product_id — TEXT — Primary key — NOT NULL
- producer_id — TEXT — Foreign key → producers.producer_id — NOT NULL
- category_id — TEXT — Foreign key → categories.category_id — NOT NULL
- name — TEXT — NOT NULL
- description — TEXT — NOT NULL
- image_url — TEXT — NOT NULL
- badge — TEXT
- status — TEXT — NOT NULL
- published_version — INTEGER — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- search_terms — TEXT — NOT NULL
refunds
- refund_id — TEXT — Primary key — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id — NOT NULL
- payment_id — TEXT — Foreign key → payments.payment_id — NOT NULL
- amount — INTEGER — NOT NULL
- reason_code — TEXT — NOT NULL
- status — TEXT — NOT NULL
- requested_at — TIMESTAMPTZ — NOT NULL
- completed_at — TIMESTAMPTZ
reviews
- review_id — TEXT — Primary key — NOT NULL
- order_item_id — TEXT — Foreign key → order_items.order_item_id — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- product_id — TEXT — Foreign key → products.product_id — NOT NULL
- rating — INTEGER — NOT NULL
- body — TEXT — NOT NULL
- status — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
schema_migrations
- version — INTEGER — Primary key — NOT NULL
- name — TEXT — NOT NULL
- applied_at — TIMESTAMPTZ — NOT NULL
sessions
- session_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- token_hash — TEXT — NOT NULL
- expires_at — TIMESTAMPTZ — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- last_seen_at — TIMESTAMPTZ — NOT NULL
- revoked_at — TIMESTAMPTZ
shipment_items
- shipment_item_id — TEXT — Primary key — NOT NULL
- shipment_id — TEXT — Foreign key → shipments.shipment_id — NOT NULL
- order_item_id — TEXT — Foreign key → order_items.order_item_id — NOT NULL
- quantity — INTEGER — NOT NULL
shipments
- shipment_id — TEXT — Primary key — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id — NOT NULL
- hub_id — TEXT — Foreign key → fulfillment_hubs.hub_id
- group_key — TEXT — NOT NULL
- service_type — TEXT — NOT NULL
- carrier_name_snapshot — JSONB
- tracking_masked — TEXT
- status — TEXT — NOT NULL
- eta — TIMESTAMPTZ — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- delivery_partner_id — TEXT — Foreign key → planned_delivery_partners.delivery_partner_id
skus
- sku_id — TEXT — Primary key — NOT NULL
- product_id — TEXT — Foreign key → products.product_id — NOT NULL
- option_name — TEXT — NOT NULL
- unit — TEXT — NOT NULL
- grade — TEXT — NOT NULL
- list_price — INTEGER — NOT NULL
- sale_price — INTEGER — NOT NULL
- sale_status — TEXT — NOT NULL
- max_quantity — INTEGER — NOT NULL
- inventory_total_cached — INTEGER — NOT NULL
- temperature — TEXT — NOT NULL
- service_type — TEXT — NOT NULL
support_inquiries
- inquiry_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- category_code — TEXT — NOT NULL
- order_id — TEXT — Foreign key → orders.order_id
- order_item_id — TEXT — Foreign key → order_items.order_item_id
- subject — TEXT — NOT NULL
- status — TEXT — NOT NULL
- contact_preference — TEXT
- expected_response_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
- closed_at — TIMESTAMPTZ
support_messages
- message_id — TEXT — Primary key — NOT NULL
- inquiry_id — TEXT — Foreign key → support_inquiries.inquiry_id — NOT NULL
- sender_type — TEXT — NOT NULL
- body — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
trace_records
- trace_record_id — TEXT — Primary key — NOT NULL
- sku_id — TEXT — Foreign key → skus.sku_id — NOT NULL
- lot_no_masked — TEXT — NOT NULL
- harvested_at — TIMESTAMPTZ — NOT NULL
- packed_at — TIMESTAMPTZ — NOT NULL
- inspection_grade — TEXT — NOT NULL
- storage_method — TEXT — NOT NULL
upload_assets
- asset_id — TEXT — Primary key — NOT NULL
- customer_id — TEXT — Foreign key → customers.customer_id — NOT NULL
- purpose — TEXT — NOT NULL
- file_name — TEXT — NOT NULL
- content_type — TEXT — NOT NULL
- size_bytes — INTEGER — NOT NULL
- sha256 — TEXT
- status — TEXT — NOT NULL
- scan_status — TEXT — NOT NULL
- expires_at — TIMESTAMPTZ — NOT NULL
- completed_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
- object_key — TEXT — NOT NULL
- storage_provider — TEXT — NOT NULL
- retention_until — TIMESTAMPTZ
webhook_events
- provider — TEXT — Primary key — NOT NULL
- event_id — TEXT — Primary key — NOT NULL
- event_type — TEXT — NOT NULL
- body_hash — TEXT — NOT NULL
- signature_valid — BOOLEAN — NOT NULL
- status — TEXT — NOT NULL
- payload_json — JSONB — NOT NULL
- received_at — TIMESTAMPTZ — NOT NULL
- processed_at — TIMESTAMPTZ