概念モデル (ER 図)

waste-link のドメイン主要エンティティと関連を 1 枚の ER 図で示します。カラムは主要 キー (PK) と外部キー (FK)、業務上重要な属性に絞っています。型・カラム名・FK は supabase/migrations/** を典拠にしています。

テナント分離

すべての業務テーブルは tenant_id を持ち、RLS の current_tenant_id() で 自テナントに限定されます (migration 0001 / 0003)。唯一の例外は全テナント共通の マスタ item_types で、authenticated 全員が参照できます。weighing_items / dispatch_course_stops 等は親テーブル経由でテナントを継承します。 認証 ID (auth.users) は Supabase Auth が管理し、profiles.user_id が 1:1 で紐付きます。

erDiagram
    auth_users ||--o| profiles : "1:1 (user_id, unique)"

    tenants ||--o{ profiles : "所属"
    tenants ||--o{ factories : "拠点"
    tenants ||--o{ partners : "取引先"
    tenants ||--o{ jobs : "発注 (discharger)"
    tenants ||--o{ job_assignments : "受注 (collector_tenant_id)"
    tenants ||--o{ manifests : "保有"
    tenants ||--o{ dispatch_courses : "保有"
    tenants ||--o| subscriptions : "1:1 課金"
    tenants ||--o{ permits : "許可証"
    tenants ||--o{ service_areas : "対応エリア"
    tenants ||--o{ pricing_rules : "料金"
    tenants |o--o{ signup_invite_codes : "消費 (used_by_tenant)"

    profiles ||--o{ profile_factories : "所属工場 (M:N)"
    factories ||--o{ profile_factories : "所属メンバー (M:N)"
    factories ||--o{ vehicles : "車両所属"
    profiles |o--o{ vehicles : "担当 (assigned_driver_id)"

    partners ||--o{ collection_sites : "回収先 (1:N)"
    collection_sites ||--o{ collection_site_schedules : "繰り返し設定"

    dispatch_courses ||--o{ dispatch_course_stops : "コース内回収先"
    dispatch_courses ||--o{ dispatch_course_assignments : "曜日×週序数 割当"
    collection_sites ||--o{ dispatch_course_stops : "回収先参照"
    profiles ||--o{ dispatch_course_assignments : "担当ドライバー"

    profiles ||--o{ dispatch_stops : "担当ドライバー"
    collection_sites ||--o{ dispatch_stops : "訪問先"
    profiles ||--o{ dispatch_ride_alongs : "ルートのドライバー(driver_id)"
    profiles ||--o{ dispatch_ride_alongs : "同乗者(profile_id)"

    vehicles ||--o{ vehicle_daily_logs : "日次運行ログ"
    vehicles ||--o{ vehicle_cost_entries : "コスト明細"
    factories ||--o{ vehicle_daily_logs : "工場"
    factories ||--o{ vehicle_cost_entries : "工場"
    profiles |o--o{ vehicle_daily_logs : "運転者 (driver_id)"

    jobs ||--o{ job_assignments : "見積・受託"
    jobs ||--o{ job_collection_sites : "1案件:N回収先 (mig85)"
    collection_sites ||--o{ job_collection_sites : "紐付け"
    jobs ||--o{ job_recurrence_schedules : "定期スケジュール (mig80)"
    jobs |o--o{ manifests : "紐付け (job_id, nullable)"

    partners ||--o{ partner_item_contracts : "単価契約"
    item_types ||--o{ partner_item_contracts : "品目"
    partners ||--o{ weighings : "計量"
    weighings ||--o{ weighing_items : "品目別重量"
    item_types ||--o{ weighing_items : "品目"

    tenants {
        uuid id PK
        text name
        text type "discharger|collector"
    }
    profiles {
        uuid id PK
        uuid tenant_id FK
        uuid user_id FK "unique -> auth.users"
        text role "admin|normal|driver"
        bool can_drive
        text login_id "unique"
        uuid factory_id FK "nullable"
        text email "nullable (連絡先)"
        text display_name
    }
    profile_factories {
        uuid profile_id PK, FK
        uuid factory_id PK, FK
        uuid tenant_id
    }
    factories {
        uuid id PK
        uuid tenant_id
        text name
        bool active
    }
    partners {
        uuid id PK
        uuid tenant_id
        text name
        bool active
    }
    collection_sites {
        uuid id PK
        uuid tenant_id
        uuid partner_id FK
        text name
        bool active
    }
    collection_site_schedules {
        uuid id PK
        uuid site_id FK
        uuid tenant_id
        smallint weekday "0-6"
        text week_ordinal "every|1-5|last"
    }
    vehicles {
        uuid id PK
        uuid tenant_id
        text plate_number
        text vehicle_type
        uuid factory_id FK
        uuid assigned_driver_id FK "nullable"
        bool active
    }
    vehicle_daily_logs {
        uuid id PK
        uuid tenant_id
        uuid vehicle_id FK
        uuid factory_id FK
        date log_date
        uuid driver_id FK "nullable"
        int odometer_out
        int odometer_in
        int distance_km "generated"
        numeric diesel_liters
        numeric adblue_liters
        numeric gasoline_liters
    }
    vehicle_cost_entries {
        uuid id PK
        uuid tenant_id
        uuid vehicle_id FK
        uuid factory_id FK
        date entry_date
        text category "fuel|maintenance|..."
        numeric amount
    }
    dispatch_courses {
        uuid id PK
        uuid tenant_id FK
        text name
        bool active
    }
    dispatch_course_stops {
        uuid id PK
        uuid tenant_id FK
        uuid course_id FK
        uuid collection_site_id FK
        int stop_order
    }
    dispatch_course_assignments {
        uuid id PK
        uuid tenant_id FK
        uuid course_id FK
        uuid driver_id FK
        int weekday "0-6"
        text week_ordinal "every|1-4|last"
    }
    dispatch_stops {
        uuid id PK
        uuid tenant_id
        date dispatch_date
        uuid driver_id FK
        uuid collection_site_id FK
        int stop_order
        text status "pending|completed"
        timestamptz completed_at
    }
    dispatch_ride_alongs {
        uuid id PK
        uuid tenant_id
        date dispatch_date
        uuid driver_id FK
        uuid profile_id FK
        uuid created_by FK "nullable"
    }
    jobs {
        uuid id PK
        uuid tenant_id "discharger"
        text title "案件名 (排出が設定, nullable, mig84)"
        text waste_type
        numeric quantity
        boolean is_recurring "定期回収 (mig80)"
        text status "draft|open_for_quotes|..."
    }
    job_collection_sites {
        uuid id PK
        uuid job_id FK
        uuid collection_site_id FK
        uuid collector_tenant_id "トリガ補完 (mig85)"
    }
    job_recurrence_schedules {
        uuid id PK
        uuid job_id FK
        int weekday "0=日..6=土"
        text week_ordinal "every|1..5|last"
    }
    job_assignments {
        uuid id PK
        uuid job_id FK
        uuid collector_tenant_id "collector"
        numeric quoted_price
        text status "proposed|accepted_by_discharger|..."
    }
    manifests {
        uuid id PK
        uuid tenant_id
        uuid job_id FK "nullable"
        text manifest_number "unique/tenant"
        text waste_type
        text status "pending|processing|completed|error"
        numeric confidence
    }
    item_types {
        uuid id PK
        text code "unique (全テナント共通)"
        text name
    }
    partner_item_contracts {
        uuid id PK
        uuid tenant_id
        uuid partner_id FK
        uuid item_type_id FK
        text direction "charge|buyback"
        numeric unit_price
    }
    weighings {
        uuid id PK
        uuid tenant_id
        uuid partner_id FK
        date weighed_on
        numeric net_kg "generated"
    }
    weighing_items {
        uuid id PK
        uuid weighing_id FK
        uuid item_type_id FK
        numeric weight_kg
    }
    signup_invite_codes {
        uuid id PK
        text email
        text code_hash
        int attempts "上限 5"
        timestamptz used_at
        uuid used_by_tenant FK "nullable"
    }
    subscriptions {
        uuid id PK
        uuid tenant_id FK "unique"
        text plan "free|starter|growth|enterprise"
        text status "active|inactive|..."
    }
    permits {
        uuid id PK
        uuid tenant_id
        text permit_type
    }
    service_areas {
        uuid id PK
        uuid tenant_id
        text prefecture
    }
    pricing_rules {
        uuid id PK
        uuid tenant_id
        text waste_type
        numeric unit_price
    }

ドメイン別の読み解き

ドメイン	中心エンティティ	補足
組織・認証	tenants profiles profile_factories factories	tenants.type で discharger / collector を区別。profiles は auth.users と 1:1、所属工場は M:N。
取引先・回収先	partners collection_sites collection_site_schedules	partner 1→N collection_site。回収先ごとに (曜日 × 週序数) の繰り返しを持つ。
配車	dispatch_courses dispatch_course_stops dispatch_course_assignments dispatch_stops dispatch_ride_alongs	コース割当から当日の dispatch_stops を生成 (RPC)。dispatch_stops は migration 0037 で partner から collection_site へ rekey 済み。dispatch_ride_alongs は当日ルート (driver_id × dispatch_date) への同乗者 (横乗り) で、ストップには干渉しない (migration 0057 / ADR 0016)。
車両	vehicles vehicle_daily_logs vehicle_cost_entries	車両は 1 工場に所属。日次ログは (tenant, vehicle, date) で一意。
マッチング・マニフェスト	jobs job_assignments manifests	discharger の jobs に collector が job_assignments で見積。manifests は任意で job_id に紐付く。
資源計量・請求	item_types partners partner_item_contracts weighings weighing_items	item_types のみ全テナント共通マスタ。
課金・オンボーディング	subscriptions signup_invite_codes	subscriptions は tenant と 1:1。招待は service_role のみ可読。

関連 ADR: 0003 RLS マルチテナント分離、 0005 ロールモデル、 0006 工場スコープのアクセス制御、 0008 配車コースモデル。