DutchPayDB

Tables (13)

app_user

  • user_id — BIGINT — Primary key — NOT NULL
  • provider — VARCHAR(20) — NOT NULL
  • provider_user_id — VARCHAR(100) — NOT NULL
  • name — VARCHAR(50)
  • phone — VARCHAR(20)
  • created_at — DATETIME — NOT NULL

checkout

  • checkout_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → table_session.session_id — NOT NULL
  • checkout_type — VARCHAR(20) — NOT NULL
  • split_method — VARCHAR(20)
  • total_amount — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • checkout_token — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • expires_at — DATETIME

checkout_participant

  • checkout_participant_id — BIGINT — Primary key — NOT NULL
  • checkout_id — BIGINT — Foreign key → checkout.checkout_id — NOT NULL
  • participant_id — BIGINT — Foreign key → session_participant.participant_id — NOT NULL
  • amount_to_pay — INT — NOT NULL
  • paid_amount — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • paid_at — DATETIME

dining_table

  • table_id — BIGINT — Primary key — NOT NULL
  • store_id — BIGINT — Foreign key → store.store_id — NOT NULL
  • table_no — INT — NOT NULL
  • qr_token — VARCHAR(255) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL

menu_category

  • category_id — BIGINT — Primary key — NOT NULL
  • store_id — BIGINT — Foreign key → store.store_id — NOT NULL
  • category_name — VARCHAR(100) — NOT NULL
  • sort_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL

menu_item

  • menu_item_id — BIGINT — Primary key — NOT NULL
  • category_id — BIGINT — Foreign key → menu_category.category_id — NOT NULL
  • menu_name — VARCHAR(100) — NOT NULL
  • price — INT — NOT NULL
  • description — TEXT
  • image_url — VARCHAR(255)
  • is_sold_out — BOOLEAN — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

order_item

  • order_item_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • menu_item_id — BIGINT — Foreign key → menu_item.menu_item_id
  • menu_name_snapshot — VARCHAR(100) — NOT NULL
  • unit_price_snapshot — INT — NOT NULL
  • quantity — INT — NOT NULL
  • item_total_amount — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL

orders

  • order_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → table_session.session_id — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • total_amount — INT — NOT NULL
  • created_by — BIGINT — Foreign key → session_participant.participant_id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

payment

  • payment_id — BIGINT — Primary key — NOT NULL
  • checkout_id — BIGINT — Foreign key → checkout.checkout_id — NOT NULL
  • checkout_participant_id — BIGINT — Foreign key → checkout_participant.checkout_participant_id
  • payer_user_id — BIGINT — Foreign key → app_user.user_id
  • provider — VARCHAR(30) — NOT NULL
  • provider_payment_key — VARCHAR(255)
  • amount — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • requested_at — DATETIME — NOT NULL
  • approved_at — DATETIME
  • failed_reason — VARCHAR(255)

payment_event

  • payment_event_id — BIGINT — Primary key — NOT NULL
  • payment_id — BIGINT — Foreign key → payment.payment_id
  • provider — VARCHAR(30) — NOT NULL
  • event_type — VARCHAR(50) — NOT NULL
  • raw_payload — JSON
  • received_at — DATETIME — NOT NULL

session_participant

  • participant_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → table_session.session_id — NOT NULL
  • user_id — BIGINT — Foreign key → app_user.user_id
  • nickname — VARCHAR(50)
  • join_token — VARCHAR(255) — NOT NULL
  • joined_at — DATETIME — NOT NULL

store

  • store_id — BIGINT — Primary key — NOT NULL
  • store_name — VARCHAR(100) — NOT NULL
  • address — VARCHAR(255)
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL

table_session

  • session_id — BIGINT — Primary key — NOT NULL
  • table_id — BIGINT — Foreign key → dining_table.table_id — NOT NULL
  • session_token — VARCHAR(255) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • started_at — DATETIME — NOT NULL
  • closed_at — DATETIME