ElaRide — Database Design

Version: 1.3 | Date: June 2026 | Status: Internal

Complete schema, ER diagrams, indexes, RLS, and migrations. System architecture is in ElaRide-SDD.md.

Technology & ORM
Enums & Types
Schema Overview
Complete Schema Map
Identity & Users
Rides & Booking
Driver Operations
Billing & Payments
Safety & Incidents
System Tables
Indexes
Row Level Security
Partitioning
Migrations
Data Retention

1. Technology & ORM

Concern	Choice	Rationale
Engine	PostgreSQL 16	ACID, JSONB, array types, RLS, PostGIS extension
Geospatial	PostGIS	`ST_Distance`, geo-fencing, route-deviation distance queries
Hosting	Neon (serverless)	Branch per environment, PITR, built-in PgBouncer pooling, zero egress to Railway; migrates to RDS Aurora without code changes
ORM	Prisma 5	Type-safe TypeScript client, migration tooling, client output into `packages/shared-types`
Soft delete	`deleted_at` on `users` only	GDPR erasure cascades from the user record; all other tables use hard deletes
Money	`INTEGER` (EUR cents)	Never `FLOAT` for money
Timestamps	`TIMESTAMPTZ` (UTC)	Timezone conversion handled client-side
Field encryption	AES-256-GCM via Prisma middleware	Sensitive fields stored as `BYTEA`; key in Railway secret store
Account types	`caregiver` \| `self`	`caregiver` = books for others (parent, legal guardian, care worker). `self` = adult books for themselves. Stored as `account_type` on `users` — nullable for non-guardian roles

Prisma configuration

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [postgis, pgcrypto, uuid_ossp]
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions", "tracing"]
  output          = "../../packages/shared-types/prisma-client"
}

2. Enums & Types

All enums are defined as PostgreSQL ENUM types and mirrored as Prisma enums. Domain sections reference these by name instead of repeating values inline.

Identity

Enum	Values
`UserRole`	`guardian` · `dependent` · `driver` · `dispatcher` · `support` · `admin`
`AccountType`	`caregiver` · `self`
`SeatRequirement`	`none` · `booster` · `child_seat`
`DeviceType`	`ios` · `android`

Rides & Booking

Enum	Values
`ProductType`	`ela_abo` · `ela_ride_plus`
`RideStatus`	`pending_review` · `scheduled` · `confirmed` · `assigned` · `accepted` · `en_route` · `arrived` · `in_progress` · `completed` · `cancelled` · `no_show`
`RideDirection`	`one_way` · `return`
`StopType`	`pickup` · `dropoff` · `waypoint`
`AssignmentStatus`	`pending` · `accepted` · `rejected` · `expired` · `cancelled`
`RideEventType`	`status_changed` · `pin_validated` · `pin_override_guardian_approved` · `pin_override_temp_pin_issued` · `pin_override_identity_verified` · `pin_override_support` · `sos_triggered` · `deviation_level_1` · `deviation_level_2` · `deviation_level_3` · `deviation_level_4`
`AddonType`	`safe_handover` · `wait_plus` · `begleitung_plus` · `return_plus`
`PricingMode`	`subscription` · `single_trip`

Driver Operations

Enum	Values
`OnboardingStatus`	`pending_documents` · `documents_submitted` · `under_review` · `approved` · `action_required` · `suspended`
`DocumentType`	`driving_licence` · `fuhrungszeugnis` · `vehicle_insurance` · `training_certificate`
`DocumentStatus`	`pending` · `approved` · `rejected` · `expired`
`ShiftStatus`	`scheduled` · `checked_in` · `on_break` · `checked_out` · `no_show` · `blocked_unfit`
`RoutePlanStatus`	`proposed` · `approved` · `discarded`

Billing & Payments

Enum	Values
`SubscriptionPlanType`	`ela_abo_12` · `ela_abo_24` · `ela_abo_40`
`SubscriptionStatus`	`active` · `paused` · `cancelled` · `past_due` · `unpaid`
`SubscriptionPeriodStatus`	`open` · `calculating` · `invoiced` · `paid` · `failed`
`PaymentContext`	`ride` · `subscription`
`PaymentStatus`	`authorized` · `succeeded` · `failed` · `refunded` · `partially_refunded` · `cancelled`

Safety

Enum	Values
`IncidentType`	`sos` · `accident` · `complaint` · `route_deviation` · `no_show` · `other`
`IncidentStatus`	`open` · `in_review` · `resolved` · `escalated`
`IncidentEventType`	`status_changed` · `note_added` · `escalated` · `acknowledged`

3. Schema Overview

guardian_profiles and ops_profiles are eliminated — their fields live directly on users. All guardian_id FKs on rides / subscriptions / payments point to users.id.

[Diagram]

4. Complete Schema Map

All 26 tables and their relationships. Attribute details are in the domain sections. system_config and audit_log are standalone configuration tables with no FK relationships and are omitted here.

[Diagram]

5. Identity & Users

[Diagram]

JWT payload: familyId = users.id for role = guardian. One less table lookup — the guardian's user ID is the family scope ID.

6. Rides & Booking

[Diagram]

ride_pricings.ride_id is unique — enforced via a partial unique index (see §11). arrived_at, started_at, completed_at are denormalized from ride_events for fast billing queries (Wait+ tolerance calculation needs arrived_at vs started_at diff).

7. Driver Operations

[Diagram]

8. Billing & Payments

Stripe integration pattern

ElaAbo:
  Stripe Subscription  →  monthly base fee (recurring)
  Stripe InvoiceItem   →  overage legs added at cycle end before invoice finalizes
  We track legs internally; Stripe handles payment collection + retries

ElaRide+:
  Stripe PaymentIntent (capture_method=manual)
    authorized at booking   →  payments.status = authorized
    captured at completion  →  payments.status = succeeded, captured_amount_cents set
    cancelled/partial       →  payments.status = cancelled | partially_refunded

[Diagram]

Subscription period lifecycle

[Diagram]

9. Safety & Incidents

[Diagram]

10. System Tables

Table	Purpose
`system_config`	Runtime key-value configuration (prices, timeouts, thresholds) — writable by `admin` only
`audit_log`	Immutable log of data-retention runs and GDPR erasure operations

CREATE TABLE system_config (
  key         VARCHAR(255) PRIMARY KEY,
  value       JSONB        NOT NULL,
  description TEXT,
  updated_by  UUID         REFERENCES users(id),
  updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Seeded defaults:
-- cancellation_windows, ela_abo_prices, overage_rate_cents, wait_plus_block_rate_cents
-- begleitung_plus_flat_cents, offer_expiry_seconds (default 300)
-- pin_forgotten_timeout_seconds (default 180), sos_ack_timeout_seconds (default 180)
-- payment_grace_period_hours (default 48)

CREATE TABLE audit_log (
  id               UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  action_type      VARCHAR(100) NOT NULL,  -- data_retention_run|gdpr_erasure|config_change
  target_table     VARCHAR(100),
  records_affected INTEGER,
  executed_by      VARCHAR(255) NOT NULL,  -- 'system:cron' or users.id
  metadata         JSONB,
  created_at       TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

11. Indexes

Table	Column(s)	Type	Purpose
`users`	`email`	B-tree UNIQUE	Login lookup
`users`	`phone`	B-tree UNIQUE	OTP verification
`users`	`role`	B-tree	Guard filtering
`users`	`stripe_customer_id`	B-tree UNIQUE partial (WHERE NOT NULL)	Stripe webhook routing
`dependent_profiles`	`guardian_id`	B-tree	Guardian's dependents list
`push_tokens`	`token`	B-tree UNIQUE	Token lookup on notification send
`push_tokens`	`(user_id, is_active)`	B-tree composite	Active tokens per user
`rides`	`guardian_id`	B-tree	Family ride history
`rides`	`dependent_id`	B-tree	Dependent ride list
`rides`	`status`	B-tree	Dispatch board filters
`rides`	`scheduled_at`	B-tree	Time-window queries
`rides`	`subscription_id`	B-tree	Subscription ride lookup
`rides`	`subscription_period_id`	B-tree	Period ride listing (invoices)
`rides`	`parent_ride_id`	B-tree partial (WHERE NOT NULL)	Return leg lookup
`rides`	`(pickup_lat, pickup_lng)`	GiST PostGIS	Proximity / geo-fence
`rides`	`(dropoff_lat, dropoff_lng)`	GiST PostGIS	Proximity / geo-fence
`ride_pricings`	`ride_id`	B-tree UNIQUE	One pricing record per ride
`ride_events`	`(ride_id, created_at)`	B-tree composite	Audit log reads
`ride_live_locations`	`(ride_id, recorded_at DESC)`	B-tree composite	Latest position query
`ride_assignments`	`ride_id`	B-tree	Assignment lookup
`ride_assignments`	`(driver_id, status)`	B-tree composite	Active driver assignments
`ride_assignments`	`expires_at`	B-tree	BullMQ expiry job
`driver_documents`	`(driver_id, document_type)`	B-tree composite	Onboarding status check
`driver_documents`	`(expires_at, status)`	B-tree composite	Renewal reminder cron
`driver_shifts`	`(driver_id, scheduled_start)`	B-tree composite	ArbZG pre-dispatch check
`subscriptions`	`guardian_id`	B-tree	Guardian subscription list
`subscriptions`	`stripe_subscription_id`	B-tree UNIQUE	Webhook routing
`subscriptions`	`(status, current_period_end)`	B-tree composite	Billing cron
`subscription_periods`	`(subscription_id, period_start)`	B-tree composite	Current period lookup
`subscription_periods`	`stripe_invoice_id`	B-tree partial (WHERE NOT NULL)	Webhook routing
`payments`	`guardian_id`	B-tree	Payment history
`payments`	`stripe_payment_intent_id`	B-tree UNIQUE partial (WHERE NOT NULL)	Webhook routing
`payments`	`ride_id`	B-tree partial (WHERE ride_id IS NOT NULL)	Ride payment lookup
`incidents`	`ride_id`	B-tree	Ride incident check
`incidents`	`(status, severity)`	B-tree composite	Safety board filters
`saved_places`	`(lat, lng)`	GiST PostGIS	Address autocomplete proximity

12. Row Level Security

RLS on the three most sensitive tables. Everything else is handled by CASL + NestJS guards. Prisma sets session variables via $executeRaw before queries on RLS-protected tables.

-- Set per query in Prisma middleware:
-- SET LOCAL app.current_user_id = '<uuid>';
-- SET LOCAL app.current_user_role = 'guardian';

-- ─── ride_live_locations ──────────────────────────────────────────

ALTER TABLE ride_live_locations ENABLE ROW LEVEL SECURITY;

CREATE POLICY rll_guardian_read ON ride_live_locations
  FOR SELECT USING (
    ride_id IN (
      SELECT id FROM rides
      WHERE guardian_id = current_setting('app.current_user_id')::uuid
    )
  );

CREATE POLICY rll_driver_write ON ride_live_locations
  FOR INSERT WITH CHECK (
    driver_id IN (
      SELECT id FROM driver_profiles
      WHERE user_id = current_setting('app.current_user_id')::uuid
    )
  );

CREATE POLICY rll_ops_read ON ride_live_locations
  FOR SELECT USING (
    current_setting('app.current_user_role') IN ('dispatcher', 'support', 'admin')
  );

-- ─── payments ─────────────────────────────────────────────────────

ALTER TABLE payments ENABLE ROW LEVEL SECURITY;

CREATE POLICY pay_guardian_read ON payments
  FOR SELECT USING (
    guardian_id = current_setting('app.current_user_id')::uuid
  );

CREATE POLICY pay_ops_read ON payments
  FOR SELECT USING (
    current_setting('app.current_user_role') IN ('dispatcher', 'support', 'admin')
  );

-- ─── driver_documents ─────────────────────────────────────────────

ALTER TABLE driver_documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY doc_driver_own ON driver_documents
  FOR SELECT USING (
    driver_id IN (
      SELECT id FROM driver_profiles
      WHERE user_id = current_setting('app.current_user_id')::uuid
    )
  );

CREATE POLICY doc_admin_all ON driver_documents
  FOR ALL USING (
    current_setting('app.current_user_role') = 'admin'
  );

13. Partitioning

ride_live_locations is the only high-write, short-lived table. Range-partitioned by recorded_at (monthly). The ride.cleanup BullMQ worker deletes rows per completed ride_id immediately. Monthly partitions allow bulk-dropping aged data via the retention cron without a table scan.

CREATE TABLE ride_live_locations (
  id          UUID          NOT NULL DEFAULT gen_random_uuid(),
  ride_id     UUID          NOT NULL,
  driver_id   UUID          NOT NULL,
  lat         DECIMAL(10,8) NOT NULL,
  lng         DECIMAL(11,8) NOT NULL,
  accuracy    DECIMAL(6,2),
  speed       DECIMAL(6,2),
  heading     DECIMAL(5,2),
  recorded_at TIMESTAMPTZ   NOT NULL,
  created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

-- Monthly partition (created by cron on the 25th of each month):
CREATE TABLE ride_live_locations_y2026m06
  PARTITION OF ride_live_locations
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

14. Migrations

Concern	Approach
Tool	`prisma migrate deploy` (CI/production) · `prisma migrate dev` (local)
Timing	Railway pre-deploy hook — migration must succeed before new instance starts
Strategy	Additive-only per deploy. Breaking changes (rename, type change, drop) split across two deploys: add new → dual-write → remove old
Branching	Each developer gets a Neon branch (`dev-<name>`); PR pipeline creates a `staging` Neon branch from `main` snapshot and runs `prisma migrate deploy` against it
Rollback	Forward-only migrations. Emergency rollback: Neon PITR restore to pre-deploy snapshot (RPO ≤ 1 hour, RTO ≤ 4 hours)
Seeding	`prisma db seed` — `system_config` defaults, staging test accounts (guardian × 2, driver × 3, dispatcher × 1), sample ElaAbo subscription
Linting	`prisma validate` + `prisma format` enforced in the CI lint step

15. Data Retention

Data	Retention	Mechanism
GPS live locations	Active ride only	`ride.cleanup` worker; partition drop after 90 days
Detailed GPS traces	90 days	Weekly cron → partition drop
Aggregated route data	12 months	Weekly cron → anonymise location fields
Trip metadata + billing	10 years (HGB §257)	Archive flag; hard delete after
Incidents — general	3–5 years (configurable in `system_config`)	Weekly cron
Incidents — accident / insurance	Up to 10 years	Manual admin confirmation required
Driver documents	Contract + 3 years	Admin must confirm deletion
Training records	Contract + 5 years	Admin must confirm deletion
Dependent profiles	Until guardian deletion or GDPR erasure request	Cascade from `users.deleted_at`

All retention executions are recorded in audit_log.

End of ElaRide Database Design v1.3

ElaRide — Database Design

Table of Contents

1. Technology & ORM

Prisma configuration

2. Enums & Types

Identity

Rides & Booking

Driver Operations

Billing & Payments

Safety

3. Schema Overview

4. Complete Schema Map

5. Identity & Users

6. Rides & Booking

7. Driver Operations

8. Billing & Payments

Stripe integration pattern

Subscription period lifecycle

9. Safety & Incidents

10. System Tables

11. Indexes

12. Row Level Security

13. Partitioning

14. Migrations

15. Data Retention