データモデル Data model

管理者ポータルが触る主要エンティティ・フィールド・リレーションをドメイン別に整理しました。 全 schema (public / admin / marketing / analytics / extensions) の俯瞰は 共通データモデルを、TS 型は parky/web/portal/admin/src/lib/api.ts を参照してください。 スキーマと TS 型がずれた場合はマイグレーション側を正とし、TS 型をフォローする運用です。

Core admin-portal entities, fields, and relationships grouped by domain. For the cross-product schema overview (public / admin / marketing / analytics / extensions) see the shared data model; TS interfaces live in parky/web/portal/admin/src/lib/api.ts. When schema and TS types diverge, migrations win — TS follows.

ドメイン俯瞰図 Domain map

flowchart LR
  subgraph Parking["🅿️ Parking domain"]
    PL[parking_lots]
    PLI[parking_lot_images]
    PLT[parking_lot_tags]
    PLO[parking_lot_owners]
    PLH[parking_lot_hours]
    PLDO[parking_lot_date_overrides]
    %% parking_lot_attributes (028) と parking_lot_payment_methods (057) は tags に統合済み
    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 --> PLH
  PL --> PLDO
  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

駐車場の中核テーブル。座標 SoT は PostGIS の location (geography 4326)、lat/lng はトリガー派生。shape_type で point/line/area を切替、code (citext) は自然キー。周辺検索は nearby_parking_lots() RPC。

The core lot table. Coordinate SoT is PostGIS location (geography 4326); lat/lng are trigger-derived. shape_type switches point/line/area, code (citext) is the natural key. Nearby search via nearby_parking_lots().

parking_lot_pricing_groups / parking_spots core 2026-04-24 new

2026-04-24 に lot → pricing_group → spot の 3 階層を導入。parking_lot_pricing_rules は pricing_group_id NOT NULL、parking_sessions は parking_spot_id NOT NULL になり、新規セッション作成時には spot を必ず確定させる必要がある。

2026-04-24 introduced the lot → pricing_group → spot hierarchy. parking_lot_pricing_rules.pricing_group_id is NOT NULL and parking_sessions.parking_spot_id is NOT NULL — every new session must pin to a specific spot.

app_users core

auth_user_id で auth.users と 1:1 紐付け。退会は status='withdrawn' + PII 匿名化で運用 (deleted_at 列なし)。

Linked 1:1 to auth.users via auth_user_id. Withdrawals use status='withdrawn' + PII anonymization (no deleted_at).

通知設定 / 同意履歴 / OS パーミッション / 検索プリセット / ストリーク等は user_notification_prefs / user_consents / user_device_permissions / user_search_presets / user_streaks に分離。

Notification prefs, consents, OS permissions, search presets and streaks live in their own tables (user_notification_prefs, user_consents, user_device_permissions, user_search_presets, user_streaks).

parking_sessions

ユーザーの駐車履歴。parking_spot_id NOT NULL で必ず特定 spot に紐付く。金額は total_amount_minor (bigint, minor unit)。geo_verified / geo_distance_m で位置検証、client_request_id で冪等性。updated_at / deleted_at は無く、cancel は status='cancelled' + cancelled_reason。

Per-user parking history. parking_spot_id is NOT NULL — sessions always pin to a specific spot. Amount is bigint in minor units. geo_verified / geo_distance_m validate location; client_request_id enforces idempotency. No updated_at / deleted_at — cancellation uses status='cancelled' + cancelled_reason.

parking_lot_hours core

駐車場の時間窓。1 行 = 1 種別の時間窓 (business / entry / exit / after_hours_exit)。 曜日ロジックは day_of_week / day_type、UNIQUE は部分 UNIQUE INDEX uq_parking_lot_hours_window_key（window_type 含む）で担保。

Time windows for a parking lot. Each row represents a single window kind (business / entry / exit / after_hours_exit). Day-of-week logic uses day_of_week / day_type; uniqueness is enforced via the partial UNIQUE index uq_parking_lot_hours_window_key (which includes window_type).

parking_lot_date_overrides core 2026-04-24 new

特定日の営業時間・料金オーバーライド。花火大会や元旦特別営業など、通常ルールを 1 日だけ上書きする例外テーブル。 1 駐車場 × 1 日で 1 行 (UNIQUE parking_lot_id + override_date)。hours jsonb で business / entry / exit / after_hours_exit の任意キーを上書き、pricing jsonb 配列で料金を置換 (NULL=通常通り / []=当日無料)。

Per-day hour / pricing overrides for a parking lot (fireworks, New-Year specials, …). One row per lot × date; hours jsonb overrides any subset of business / entry / exit / after_hours_exit, pricing jsonb array replaces rules (NULL = normal, [] = free that day).

helper: get_parking_lot_date_override(lot_id, date)。 優先順位 (時刻判定 RPC が参照): 日付オーバーライド → 祝日行 → 曜日行 → day_type → all フォールバック。詳細は 2026-04-24 ADR。

jp_holidays master 2026-04-24 new

日本の祝日マスター。date PK / kind∈(national / substitute / national_holiday_bridge)。2026・2027 年の 34 件を seed 済 (振替休日含む)。 helper is_jp_holiday(date) → boolean で参照。parking_lot_hours.day_type='holiday' の判定に使われる。

Japanese holiday master with 34 rows seeded for 2026 / 2027. Used by the window-active RPC to resolve day_type='holiday' rows.

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 ユーザー (auth.users) と admin.admins の 2 層で管理。結合キーは admin.admins.user_id（旧名 auth_user_id ではなく user_id）。email は uq_admins_email で UNIQUE、status は active / inactive 等。notification_prefs JSONB に通知購読フラグ (new_owner / new_parking / sales_daily / sales_monthly / system_alert) を持つ。ロール定義は admin.roles、付与権限キーは admin.role_permissions (role_id + permission_key UNIQUE)。

Admins are tracked in both Supabase Auth (auth.users) and admin.admins. The join column is admin.admins.user_id (not auth_user_id). email is UNIQUE (uq_admins_email); status uses values such as active / inactive. notification_prefs JSONB stores per-admin subscription flags (new_owner / new_parking / sales_daily / sales_monthly / system_alert). Role definitions live in admin.roles; granted permission keys in admin.role_permissions (role_id + permission_key UNIQUE).

admin_tasks, admin_notifications, admin_activity_logs system

• admin.admin_tasks — task_kind ∈ misinformation_report / support / owner_application / parking_new_registration / parking_fee_mismatch。(task_kind, ref_id) UNIQUE。urgency ∈ urgent / high / medium / low。task_kind values: misinformation_report / support / owner_application / parking_new_registration / parking_fee_mismatch. UNIQUE on (task_kind, ref_id); urgency ∈ urgent / high / medium / low.
• admin.admin_notifications — category default 'system'、read bool、deleted_at によるソフトデリート。Realtime 購読でヘッダーバッジを更新。category defaults to 'system', read bool, soft-deletable via deleted_at. Subscribed by the header badge over Realtime.
• admin.admin_activity_logs — 監査ログ（POST のみ非冪等）。fetchAdminActivityLogsPage / BFF /v1/admin/activity-logs で参照。Audit log (POST is non-idempotent). Read via fetchAdminActivityLogsPage / BFF /v1/admin/activity-logs.

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.