Data Model (ERD)
Lookup Catalog & Access Paths
Lookup type catalog, row-level security paths and SRS traceability.
4. Lookup type catalog
Seeded with is_system = true. Admin can add values only to types marked "extensible".
| Lookup type | Values | Extensible |
|---|---|---|
| account_status | active, suspended, deletion_requested, closed | No |
| role | traveler, organization_owner, organization_employee, platform_driver, platform_photographer, org_driver, org_photographer, moderator, admin_super, admin_operations, admin_finance | No |
| application_type / application_status | See 3.3 | No |
| decision_reason, suspension_reason | D-234 list | Yes |
| trip_status, booking_status, seat_status, deal_status, subscription_status, coupon_status, lab_test_status | See SRS section 7 | No |
| pricing_model | free, per_person, by_quantity, per_group, tiered | No |
| payment_method, gateway | See 3.1 | Yes |
| incident_category | safety, medical, vehicle_fault, lost_traveler, conduct, other (D-331) | No |
| dispute_type | booking, payment, refund, supplier_conduct, organization_conduct, other (D-254) | No |
| notification_category | account, booking, payment, trip_reminder, trip_day, deal, application, lab_test, plan, promotion, safety | Yes |
| trip_category | school, corporate, religious, tourism, event, other | Yes |
| permission_area | trips, bookings, operations, accounting, coupons, employees, fleet, subscription, integrations, analytics | No |
5. Row-level security and access paths
| Path | Rule |
|---|---|
| Organization staff | organization_id = app.current_organization_id on every tenant table, plus permission checks in the service layer. |
| Platform supplier | Reads deals, assignments, albums and earnings where provider_user_id or photographer_user_id equals the caller, through a platform-scoped repository that logs every call. |
| Moderator | Reads booking_passengers and writes checkin_events only for trip_vehicle_id in an accepted, unexpired assignment. |
| Traveler | Reads own bookings, passengers, receipts and allowed albums. Never reads tracking_points (BRL-29). |
| Parent | Reads child status through child_links with status active; live position only when the trip is in progress and eligible (FR-PAR-03). |
| Admin | Bypass policy role; every query logged to audit_logs. |
6. Traceability
| SRS entity (section 6.1) | Tables |
|---|---|
| Account, Role assignment | users, user_roles, auth_factors, sessions |
| Organization, Employee permission set | organizations, organization_members, member_permissions, permissions |
| Application | applications, application_documents, application_decisions, appeals |
| Driver profile, Photographer profile | driver_profiles, photographer_profiles, provider_vehicles, portfolio_items, availability_slots |
| Vehicle type, Vehicle | vehicle_types, vehicle_type_seats, vehicles |
| Trip, Trip vehicle, Program | trips, trip_stops, trip_audiences, trip_vehicles, trip_staff_assignments, programs, program_tiers |
| Booking, Booking passenger, Seat | bookings, booking_passengers, booking_programs, trip_seats, booking_remedies |
| Deal and deal offer | deals, deal_offers |
| Coupon, Coupon redemption | coupons, coupon_programs, coupon_audiences, coupon_redemptions, abuse_flags |
| Plan, Subscription | plans, plan_limits, plan_prices, subscriptions, subscription_charges, plan_overrides, usage_counters |
| Ledger entry, Payout, Invoice | ledger_accounts, ledger_transactions, ledger_entries, payout_runs, payouts, invoices, invoice_lines |
| Settlement (new in v1.1) | settlements, settlement_lines |
| Album and photo | albums, photos, album_shared_links, album_members, download_logs |
| Check-in event, Tracking point | checkin_events, tracking_points, incidents, trip_reports |
| Child link | child_links, safety_cases |
| Lab test | lab_tests, lab_partners |
| Notification preference | notification_preferences, notifications, notification_deliveries, notification_templates |
| Audit log entry | audit_logs |
| Market (new in v1.1) | countries, currencies, markets, market_payment_methods, tax_rates |
| Analytics (new in v1.1) | analytics_events, analytics_daily |
| Dispute | disputes, dispute_files |