Skip to content

Database

Entity Relationship Overview

graph LR
    ARTIST["Artist\nid, name"] --> PRODUCT["Product\nartist_id\nsale_type\npublish_type\ncategory"]
    PRODUCT --> PV["ProductVariation\nproduct_id\nstock\nckc_*"]
    PV --> OD["OrderDetail\norder_id\nproduct_variation_id"]
    OD --> ORDER["Order\nuser_id\nstatus\npayment_method"]
    ORDER --> USER["User\nid\nemail\nstatus"]
    USER --> UA["UserAddress\nuser_id\nprefecture_id"]
    UA --> PREF["Prefecture\nid, name"]
    PV --> CART["CartItem\nuser_id nullable\nguest_id string\nproduct_variation_id"]
    PV --> LCART["LotteryCartItem\nuser_id\nproduct_variation_id"]
    PV --> LOTTERY["Lottery\nuser_id\nproduct_variation_id\norder_detail_id nullable\nstatus\nis_sent"]
    OD --> CKC["CkcCode\nproduct_variation_id\norder_detail_id nullable\nstatus\nshot_type"]
    USER --> BLACKLIST["Blacklist\nuser_id\nproduct_id"]
    ORDER --> RETRY["OrderRetry\norder_id\nstatus\ntoken"]
    PRODUCT --> IMG["Image\nimageable_type\nimageable_id"]
    PV --> IMG

Core Domain Tables

Product Catalog

Table Key Columns Notes
artists id, name Soft delete. Root of product hierarchy
products artist_id, sale_type, publish_type, category, publication_reserve Soft delete. Indexed: category, sale_type, publish_type
product_variations product_id, stock, ckc_start_date, ckc_end_date, with_ckc_code Soft delete. FK cascade from products

products.sale_type (SaleTypeEnum): normal → standard cart flow, lottery → lottery application flow.

products.publish_type (PublishTypeEnum): controls visibility; PublishJob monitors publication_reserve for scheduled publish.

Purchase Flow

Table Key Columns Notes
cart_items user_id nullable, guest_id string, product_variation_id, ckc_shot_type Guest cart: user_id = null, guest_id = JWT guest ID
lottery_cart_items user_id, product_variation_id User-only (no guest)
orders user_id, status, payment_method, orderer_prefecture No soft delete. Indexed: status
order_details order_id, product_variation_id Line items
order_retries order_id, status, token Payment retry tracking
lotteries user_id, product_variation_id, order_detail_id nullable, status, is_sent order_detail_id populated only on win+purchase. Indexed: status

Digital Codes

Table Key Columns Notes
ckc_codes product_variation_id, order_detail_id nullable, status, shot_type Assigned to order_detail_id at fulfilment

Membership

Table Key Columns Notes
users email, status, ckc_device_id Soft delete. UserStatusEnum
user_addresses user_id, prefecture_id One per user
tokens user_id, token unique Email-change verification
blacklists user_id, product_id Composite unique. Lottery blacklist
admins email Separate auth model. Soft delete

Support Tables

Table Purpose
prefectures JP prefecture reference data (47 prefectures)
oricon_prefectures Oricon reporting codes (group_code, store_code). Unique name
japan_post_addresses Postcode lookup (large reference table; SQL file untracked in git)
product_purchase_limits Per-product limits: limit_type (PER_ORDER/USER/DATE/MEMBER), limit_count
product_sliders Homepage featured product slider config
images Polymorphic image store (imageable_type, imageable_id)
items Oricon reporting line items (attached to products)
bonus_items Gift items attached to products
password_resets Laravel password reset tokens
failed_jobs, jobs Laravel queue tables

Key Patterns

Dual Cart System

cart_items supports guest users via guest_id string column (user_id nullable). On login, CartManager::copyUserCart() migrates guest cart items to the authenticated user.

lottery_cart_items has no guest support — requires authenticated user.

Two Sale Types

products.sale_type is the routing switch: - Normal: cart_items → orders → order_details → payment - Lottery: lottery_cart_items → lotteries (application) → lottery draw → order_detail_id populated on win

hasManyDeep Relationships

Product::orders() and Order::products() traverse through ProductVariation → OrderDetail using staudenmeir/eloquent-has-many-deep. Same pattern for OrderDetail::product().

Soft Deletes

Applied to: users, admins, artists, products, product_variations.

NOT applied to: orders — order hard-delete is irreversible with no recovery path.

Custom QueryBuilders

9 models use custom Eloquent QueryBuilder subclasses for domain-specific scoping. Bound via newEloquentBuilder() override:

User, Product, ProductVariation, Order, OrderDetail, Lottery, CartItem, CkcCode, Sales (via SalesManager).

Configurable Purchase Limits

product_purchase_limits stores per-product limit rules. PurchaseLimitTypeEnum: PER_ORDER, PER_USER, PER_DATE, PER_MEMBER. Evaluated at cart-add (partial) and order-placement (full — BuyableRule).

Order Status State Machine

open → success → processing → shipped → (end)
     ↘ failure

OrderRetry: open → success | failure

Admin statuses (processing, shipped, canceled) are set manually via admin UI or CSV import.

Indexes to Know

  • products: (category), (sale_type), (publish_type)
  • orders: (status)
  • lotteries: (status)
  • ckc_codes: (shot_type, status)
  • blacklists: UNIQUE (user_id, product_id)
  • tokens: UNIQUE (token)