pickle ERD

Tables (12)

app_user

  • user_id — BIGSERIAL
  • email — VARCHAR(255) — NOT NULL
  • provider — user_provider — NOT NULL
  • nickname — VARCHAR(40) — NOT NULL
  • password_hash — VARCHAR(255)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ

cart_device

  • cart_device_id — BIGSERIAL
  • device_code — VARCHAR(64) — NOT NULL
  • display_name — VARCHAR(80)

cart_item

  • cart_item_id — BIGSERIAL
  • cart_session_id — BIGINT — Foreign key → cart_session.cart_session_id — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • qty — INTEGER — NOT NULL
  • unit_price — INTEGER — NOT NULL
  • added_at — TIMESTAMPTZ

cart_session

  • cart_session_id — BIGSERIAL
  • cart_device_id — BIGINT — Foreign key → cart_device.cart_device_id — NOT NULL
  • user_id — BIGINT — Foreign key → app_user.user_id
  • status — cart_session_status — NOT NULL
  • budget_limit — INTEGER
  • expected_total_g — INTEGER
  • measured_total_g — INTEGER
  • started_at — TIMESTAMPTZ — NOT NULL
  • ended_at — TIMESTAMPTZ

ledger_entry

  • ledger_entry_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → app_user.user_id — NOT NULL
  • payment_id — BIGINT — Foreign key → payment.payment_id
  • spend_date — DATE — NOT NULL
  • category — ledger_category — NOT NULL
  • amount — INTEGER — NOT NULL

payment

  • payment_id — BIGSERIAL
  • cart_session_id — BIGINT — Foreign key → cart_session.cart_session_id
  • user_id — BIGINT — Foreign key → app_user.user_id — NOT NULL
  • status — payment_status — NOT NULL
  • total_amount — INTEGER — NOT NULL
  • pg_provider — VARCHAR(50)
  • pg_tid — VARCHAR(255)
  • approved_at — TIMESTAMPTZ

payment_method

  • method_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → app_user.user_id — NOT NULL
  • card_name — VARCHAR(50)
  • card_number_enc — VARCHAR(255)
  • is_default — BOOLEAN
  • created_at — TIMESTAMPTZ

product

  • product_id — BIGSERIAL
  • category_id — BIGINT — Foreign key → product_category.category_id
  • name — VARCHAR(255) — NOT NULL
  • price — INTEGER — NOT NULL
  • unit_weight_g — INTEGER — NOT NULL
  • stock_quantity — INTEGER
  • image_url — TEXT
  • product_info — JSONB
  • created_at — TIMESTAMPTZ

product_category

  • category_id — BIGSERIAL
  • name — VARCHAR(60) — NOT NULL
  • zone_code — VARCHAR(30)
  • map_coordinates — JSONB

recipe

  • recipe_id — BIGSERIAL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • instructions — TEXT
  • image_url — TEXT
  • created_at — TIMESTAMPTZ

recipe_ingredient

  • recipe_id — BIGINT — Foreign key → recipe.recipe_id — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • quantity_info — VARCHAR(50)

saved_recipe

  • user_id — BIGINT — Foreign key → app_user.user_id — NOT NULL
  • recipe_id — BIGINT — Foreign key → recipe.recipe_id — NOT NULL
  • saved_at — TIMESTAMPTZ