データモデル Data model

主要エンティティ、フィールド、リレーションをドメイン別に整理しました。 詳細な型は parky/web/portal/admin/src/lib/api.ts の TypeScript インターフェースが一次情報源です。

Core entities, fields, and relationships grouped by domain. The canonical source for field types is the TypeScript interfaces in parky/web/portal/admin/src/lib/api.ts.

ドメイン俯瞰図 Domain map

flowchart LR
  subgraph Parking["🅿️ Parking domain"]
    PL[parking_lots]
    PLI[parking_lot_images]
    PLT[parking_lot_tags]
    PLA[parking_lot_attributes]
    PLP[parking_lot_payment_methods]
    PLO[parking_lot_owners]
    PR[parking_reviews]
    PS[parking_sessions]
  end
  subgraph Users["👤 Users"]
    AU[app_users]
    USP[user_saved_parkings]
    UAL[user_activity_logs]
    US[user_subscriptions]
  end
  subgraph Owner["🏢 Owners"]
    OW[owners]
    OA[owner_applications]
    OC[owner_credits]
    CT[credit_transactions]
    B[boosts]
  end
  subgraph Game["🏆 Gamification"]
    BD[badge_definitions]
    UBP[user_badge_progress]
    UE[user_exp]
    LD[level_definitions]
    AER[activity_exp_rules]
  end
  subgraph Theme["🎨 Customization"]
    CTh[customization_themes]
    CTI[customization_theme_items]
    CTP[customization_theme_parts]
    TG[theme_gifts]
    UT[user_themes]
  end
  subgraph Ops["🛠 Operations"]
    ST[support_tickets]
    ER[error_reports]
    AT[admin_tasks]
    UN[user_notifications]
    AN[admin_notifications]
  end
  subgraph Content["📝 Content"]
    ART[articles]
    ADS[ads]
    TAG[tags]
  end
  subgraph Rev["💰 Revenue"]
    SP[subscription_plans]
    RT[revenue_transactions]
    RMS[(revenue_monthly_summary
view)]
  end
  subgraph Sys["⚙️ System"]
    AD[admins]
    RL[roles]
    RP[role_permissions]
    AS[assets]
    CD[codes]
  end

  PL --> PLI
  PL --> PLT
  PL --> PLA
  PL --> PLP
  PL --> PLO
  PL --> PR
  PL --> PS
  PLO --> OW
  AU --> PS
  AU --> PR
  AU --> USP
  AU --> UAL
  AU --> US
  AU --> UE
  AU --> UBP
  OW --> OA
  OW --> OC
  OW --> CT
  OW --> B
  B --> PL
  BD --> UBP
  LD --> UE
  CTh --> CTI
  CTI --> CTP
  CTh --> TG
  TG --> UT
  AT -.-> ST
  AT -.-> ER
  AT -.-> OA
  AT -.-> PL
  SP --> US
  RT --> SP
  AD --> RL
  RL --> RP

主要テーブル定義 Core table definitions

parking_lots core

駐車場の中核テーブル。GPS 座標は PostGIS の geography 型で保持され、nearby_parking_lots() RPC で周辺検索が可能。

The core parking lot table. GPS is stored in a PostGIS geography column and queried via the nearby_parking_lots() RPC.

app_users core

parking_sessions

ユーザーの駐車履歴。入出庫時刻と請求額を保持。

Per-user parking history with entry / exit timestamps and billed amount.

owners, owner_applications, owner_credits, credit_transactions

• owners — 個人/法人の 2 種類、ステータス管理。individual / business, with status.
• owner_applications — 申請ごとに 1 行。書類は JSONB で保存。one row per application, documents stored as JSONB.
• owner_credits — オーナー 1 件につき 1 行の残高スナップショット。one row per owner with the current balance.
• credit_transactions — 4 種の取引履歴 (admin_charge / purchase / consumption / refund)。four transaction types (admin_charge / purchase / consumption / refund).

badge_definitions, user_badge_progress, user_exp, level_definitions, activity_exp_rules gamification

customization_themes, ..._items, ..._parts, theme_gifts, user_themes theming

• customization_themes — テーマの容器。theme container.
• customization_theme_items — テーマ ↔ パーツの M2M。M2M between theme and parts.
• customization_theme_parts — pin / icon / color / loading の個別パーツ。アセット参照。individual pin / icon / color / loading parts, referencing assets.
• theme_gifts — 管理者 → ユーザーへの配布記録。records of gifts from admin to user.
• user_themes — ユーザーの所有テーマと取得経路 (purchase/gift)。user-owned themes with acquisition type.

support_tickets, error_reports, admin_tasks ops

admin_tasks が 4 種類のソース (support / misinformation_report / owner_application / parking_new_registration) を task_kind + ref_id で指す設計。ポリモーフィックなリレーション。

admin_tasks polymorphically references four source tables via task_kind + ref_id, which is what enables the unified task inbox.

revenue_transactions & revenue_monthly_summary revenue

• revenue_transactions — 全ての売上行。channel (subscription / parking / boost) と transaction_type を持つ。every revenue row, with a channel and transaction_type.
• revenue_monthly_summary — 月別集計ビュー。Sales ページの「月別」タブで使用。a monthly rollup view used by the Sales page's monthly tab.

admins, roles, role_permissions system

管理者は Supabase Auth ユーザーと admins テーブルの 2 層で管理。ロールは roles、権限キーは role_permissions。

Admins are tracked in both Supabase Auth and the admins table. Roles live in roles, permission keys in role_permissions.

codes master

カテゴリ付きラベル辞書 (user_status / vehicle_type / ...)。起動時に一括ロードされ、UI のドロップダウンとラベル表示に使われます。

A category-keyed label dictionary (user_status / vehicle_type / ...) that's loaded once at boot and powers every dropdown and label in the UI.

TypeScript 型との対応 TypeScript type mapping

各テーブルは src/lib/api.ts に対応する TS インターフェースを持ちます。 代表的なものを以下に示しますが、新しい機能を追加するときは必ずこのファイルを一次情報として確認してください。

Every table has a matching TS interface in src/lib/api.ts. Use that file as the source of truth when adding new features.