[Spike] Notifications

[davidgamez]

Notifications — Implementation Proposal

Related issues: #196 · #197

---

MVP scope

In MVP	Post-MVP
User migration from Firebase to PostgreSQL	Organization entity, membership, join requests
GET/PUT /v1/users/me profile API	Organization CRUD and merge API
Notification subscriptions (user-level)	Org-level notification fan-out
Brevo email dispatch (daily digest)	Org data migration from Firestore free-text
Predefined notification types	Custom query-based notifications

---

1. User Data Storage

Decision: migrate to PostgreSQL, isolated schema, synced via a GET /v1/users/me upsert.

Currently user records live in Firebase Firestore. The UI (mobilitydatabase-web) retrieves and writes them through two Firebase Callable Functions defined in the functions/ folder of that repo:

Firebase Function	UI call site	Purpose
retrieveUserInformation	auth-saga.ts — on every login and OAuth provider sign-in	Fetch { fullName, organization, isRegisteredToReceiveAPIAnnouncements } from Firestore
updateUserInformation	profile-saga.ts — on profile save	Write the same fields back to Firestore

Authentication continues to use Firebase/Google OAuth2 (the token flow in functions-python/tokens/ and RequestContextMiddleware remain unchanged — they already extract user_id (Firebase UID) and user_email from the Bearer/IAP JWT).

User data (profile, org membership, subscriptions) moves to PostgreSQL under a dedicated users schema so it cannot be inadvertently joined against feed data and PII stays compartmentalised.

Sync with the sign-up / login process

The GET /v1/users/me endpoint performs an upsert on every call: if no app_user row exists for the Firebase UID, it is created automatically. This means:

• Sign-up (signUpSaga): after Firebase createUserWithEmailAndPassword, a call to GET /v1/users/me provisions the row. No separate registration step is needed.
• Login (emailLoginSaga, loginWithProviderSaga): same call replaces the current retrieveUserInformation Firebase callable.
• Existing users: migrated by a recurring tasks_executor task (see §1.1 below). Any user not yet migrated is provisioned on demand at next login via the upsert.
• The UI replaces both Firebase callables with REST calls to the new API (see Section 8).

1.1 Firebase migration task (tasks_executor)

Rather than a one-shot backfill script, the migration runs as a repeatable task inside the existing tasks_executor Cloud Function. This handles the deployment gap: the UI and backend may be deployed independently, so new Firestore users can appear after the first run.

2. OpenAPI Schema

Yes — one new schema file following the established pattern.

Every API in this repo is spec-first:

Schema file	Gen script	Output	Deployed as
docs/DatabaseCatalogAPI.yaml	scripts/api-gen.sh	api/src/feeds_gen/	Public API (api/)
docs/OperationsAPI.yaml	scripts/api-operations-gen.sh	functions-python/operations_api/src/feeds_gen/	Operations Cloud Function

The user service follows the same pattern:

Schema file	Gen script	Output	Deployed as
docs/UserServiceAPI.yaml (new)	scripts/api-user-service-gen.sh (new)	functions-python/user_service/src/user_service_gen/	User Service Cloud Function

One schema, not two. Users and notifications are tightly coupled (subscriptions reference users, orgs fan out to users). A single UserServiceAPI.yaml avoids cross-file $ref complexity. If the service grows significantly it can be split later.

Top-level structure of docs/UserServiceAPI.yaml

tags:
  - name: users
  - name: organizations
  - name: notifications

paths:
  /v1/users/me:           # GET (upsert+fetch), PUT (update profile)
  /v1/organizations:      # GET (search by name), POST (create)
  /v1/organizations/{id}: # GET, PATCH, DELETE
  /v1/organizations/{id}/members:         # GET, POST
  /v1/organizations/{id}/members/{uid}:   # PATCH (role), DELETE
  /v1/notifications/types:                # GET (list predefined types)
  /v1/notifications/subscriptions:        # GET, POST
  /v1/notifications/subscriptions/{id}:   # PATCH (toggle active), DELETE

Authorization

All user, notification, and organization endpoint results are scoped per user. A user can only fetch organizations linked to their profile; the same applies to notifications.

3. Modularity

Decision: new Cloud Function / FastAPI module inside this repo.

A completely separate repository would duplicate the shared libraries (shared/, auth middleware, DB session helpers) and complicate deployment. Instead, add a functions-python/user_service/ module following the same pattern as operations_api.

Separate database_gen per service

The existing db-gen.sh generates api/src/shared/database_gen/sqlacodegen_models.py from search_path=public — it only contains feed/dataset models. The user_service uses a different PostgreSQL schema (users.*) so it needs its own generated models:

	Feeds / Operations API	User Service
PostgreSQL schema	public	users
Gen script	scripts/db-gen.sh	scripts/db-user-service-gen.sh (new)
Output	api/src/shared/database_gen/	api/src/shared/user_database_gen/ (new)
Packaged via	function_config.json → "include_api_folders": ["database_gen", ...]	function_config.json → "include_api_folders": ["user_database_gen", ...]

db-user-service-gen.sh runs sqlacodegen with search_path=users and writes to user_database_gen/. The user service imports from shared.user_database_gen.sqlacodegen_models — it never touches feed models and vice versa.

The RequestContextMiddleware already provides user_email from the validated OAuth2 token, so no new auth mechanism is needed.

---

4. Organization Entity (Post-MVP)

Organization management is out of MVP scope. All organization tables, endpoints, and data migration tasks described here are to be implemented after the MVP launch. The legacy_org_name column is kept in app_user during MVP so the data is preserved for the post-MVP migration.

Decision: organization as a first-class entity with role-based membership; any authenticated user can create one.

Role	Permissions
member	read org, subscribe to org notifications
admin	add/remove members, approve/reject join requests, assign admin role within their org
platform_admin	full access across all orgs (MobilityData staff)

A user can belong to multiple organizations. Any authenticated user can create a new organization (they become its first admin). Org admins manage their own org; platform admins override everything.

Role assignment endpoint (PATCH /v1/organizations/{id}/members/{user_id}): accepts { "role": "admin" | "member" | "platform_admin" }. Org admins can promote/demote within their org up to admin; only platform_admin can assign the platform_admin role.

Organization flow during sign-up / registration — with UI integration

Currently organization is a free-text string field on UserData in the UI (src/app/types.ts). After migration the registration/profile form makes organization optional and introduces a join-request model:

Scenario	Action
User leaves org field blank	No org membership created
User types a name that matches no existing org	POST /v1/organizations — user becomes admin of the new org
User selects an existing org	POST /v1/organizations/{id}/join-requests — request is pending until an org admin approves

POST /v1/organizations/{id}/join-requests
→ 202  { "request_id": "...", "status": "pending" }

PATCH /v1/organizations/{id}/join-requests/{request_id}
{ "status": "accepted" | "rejected" }   # org admin only
→ 200  { "request_id": "...", "status": "accepted" }
         # on accept: membership row is created with role "member"

Join requests are visible to org admins via GET /v1/organizations/{id}/join-requests?status=pending. When accepted, an organization_membership row is inserted. When rejected the request is marked rejected and no membership is created.

Organization data migration from Firestore (tasks_executor)

The Firestore user records contain organization as a free-text string with no deduplication. The migration requires:

1. Extract — the migrate_firebase_users task (§1.1) writes the raw string into app_user.legacy_org_name.
2. Cluster — a second task (cluster_legacy_organizations) reads all distinct legacy_org_name values and groups them by fuzzy similarity using rapidfuzz (already available in the Python ecosystem). It writes candidate groups to a staging table (users.org_merge_candidate) for review.
3. Human review — a platform_admin reviews the candidate groups in the UI and decides which strings map to the same real-world organization.
4. Apply — the admin calls POST /v1/organizations/{id}/merge to collapse duplicates, or POST /v1/organizations to create new entities for un-matched names.
5. Link users — a final task (assign_legacy_org_memberships) reads legacy_org_name, resolves the now-canonical org_id, and inserts organization_membership rows (role: member).
6. Cleanup — drop legacy_org_name column once all users are linked.

Staging table for review:

CREATE TABLE users.org_merge_candidate (
    id           TEXT PRIMARY KEY,
    names        TEXT[] NOT NULL,   -- raw strings that are similar
    similarity   FLOAT  NOT NULL,   -- max pairwise score (0–1)
    resolved_org_id TEXT REFERENCES users.organization(id),  -- set after review
    reviewed     BOOLEAN NOT NULL DEFAULT false
);

---

5. Notifications Design

Predefined notifications (MVP-TBD)

System-defined events that users can subscribe to

• "Core" - low priority
  • New feed published
  • Feed status changed (active → deprecated, etc.)
  • Validation report available
  • Dataset updated
• Change Tracker
  • New dataset with X [entity] [action]. action: modified, added, deleted. entity: routes, stops, etc.
  • New validation report with X notices [level] [action] action: added, deleted

Custom notifications (TBD PostPost MVP)

A user defines a saved search query (using the same filter parameters already exposed by /v1/feeds). The system re-runs the query daily and notifies the user when new results appear since the last run.

Delivery

• MVP: email only (one digest per day aggregating all pending notifications per user).
• Unsubscribe/resubscribe is per-notification-type.
• Post-MVP: Organizations can be targeted: a notification sent to an org is fanned out to all member+ users of that org.

---

6. Notification Engine

Recommendation: Cloud Scheduler + existing Python Cloud Function pattern.

No new framework is needed. A scheduled Cloud Function (notification_dispatcher) runs daily:

1. Queries notification_subscription for due subscriptions.
2. For custom subscriptions, re-executes the stored filter and diffs against last_notified_at.
3. Builds email bodies with Jinja2 (already available transitively).
4. Sends via Brevo transactional email API.
5. Writes a row to notification_log.

This mirrors the existing batch_datasets / tasks_executor pattern and requires no new scheduler infrastructure.

7. Proposed SQL Entities

All tables live in a users schema. Tables are labeled MVP or Post-MVP.

-- ── Schema ──────────────────────────────────────────────────────────────
CREATE SCHEMA IF NOT EXISTS users;

-- ── [MVP] Core user record ───────────────────────────────────────────────
CREATE TABLE users.app_user (
    id            TEXT PRIMARY KEY,          -- UUID v4
    firebase_uid  TEXT NOT NULL UNIQUE,      -- immutable Firebase/Google UID
    email         TEXT NOT NULL UNIQUE,
    full_name     TEXT,
    legacy_org_name TEXT,                    -- preserved from Firestore; used by post-MVP org migration
    is_registered_to_receive_api_announcements BOOLEAN NOT NULL DEFAULT false,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ── [MVP] Predefined notification types ─────────────────────────────────
CREATE TABLE users.notification_type (
    id          TEXT PRIMARY KEY,            -- e.g. 'feed.published'
    label       TEXT NOT NULL,
    description TEXT
);

-- ── [MVP] User notification subscription ────────────────────────────────
-- MVP: user_id only. org_id column added post-MVP when org fan-out is built.
CREATE TABLE users.notification_subscription (
    id                   TEXT PRIMARY KEY,   -- UUID v4
    user_id              TEXT NOT NULL REFERENCES users.app_user(id) ON DELETE CASCADE,
    notification_type_id TEXT NOT NULL REFERENCES users.notification_type(id),
    filter_params        JSONB,              -- null for predefined types; used by custom notifications (post-MVP)
    last_notified_at     TIMESTAMPTZ,
    active               BOOLEAN NOT NULL DEFAULT true,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ── [MVP] Delivery log ───────────────────────────────────────────────────
CREATE TABLE users.notification_log (
    id               TEXT PRIMARY KEY,       -- UUID v4
    subscription_id  TEXT NOT NULL REFERENCES users.notification_subscription(id),
    sent_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    channel          TEXT NOT NULL DEFAULT 'email',
    status           TEXT NOT NULL,          -- 'sent' | 'failed'
    error_message    TEXT
);

-- Indexes (MVP)
CREATE INDEX ON users.notification_subscription (user_id);
CREATE INDEX ON users.notification_subscription (notification_type_id);

-- ────────────────────────────────────────────────────────────────────────
-- POST-MVP: Organization tables (add via Liquibase migration after launch)
-- ────────────────────────────────────────────────────────────────────────

-- [Post-MVP] Organization
CREATE TABLE users.organization (
    id          TEXT PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- [Post-MVP] Membership
CREATE TABLE users.organization_membership (
    user_id   TEXT NOT NULL REFERENCES users.app_user(id) ON DELETE CASCADE,
    org_id    TEXT NOT NULL REFERENCES users.organization(id) ON DELETE CASCADE,
    role      TEXT NOT NULL DEFAULT 'member', -- 'member' | 'admin' | 'platform_admin'
    joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, org_id)
);

-- [Post-MVP] Join requests
CREATE TABLE users.organization_join_request (
    id           TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL REFERENCES users.app_user(id) ON DELETE CASCADE,
    org_id       TEXT NOT NULL REFERENCES users.organization(id) ON DELETE CASCADE,
    status       TEXT NOT NULL DEFAULT 'pending', -- 'pending' | 'accepted' | 'rejected'
    requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    resolved_at  TIMESTAMPTZ,
    resolved_by  TEXT REFERENCES users.app_user(id),
    UNIQUE (user_id, org_id)
);

-- [Post-MVP] Org merge candidate staging table (used during Firestore org migration)
CREATE TABLE users.org_merge_candidate (
    id              TEXT PRIMARY KEY,
    names           TEXT[] NOT NULL,
    similarity      FLOAT  NOT NULL,
    resolved_org_id TEXT REFERENCES users.organization(id),
    reviewed        BOOLEAN NOT NULL DEFAULT false
);

-- [Post-MVP] Add org_id to subscriptions for org-level fan-out
ALTER TABLE users.notification_subscription
    ADD COLUMN org_id TEXT REFERENCES users.organization(id) ON DELETE CASCADE;
-- Drop the NOT NULL on user_id and add check constraint at the same time:
-- (user_id IS NOT NULL)::int + (org_id IS NOT NULL)::int = 1

CREATE INDEX ON users.organization_membership (org_id);
CREATE INDEX ON users.notification_subscription (org_id);

---

8. UI Migration (mobilitydatabase-web)

What changes in profile-service.ts

Current	Replacement
retrieveUserInformation (Firebase callable)	GET /v1/users/me — returns { id, firebase_uid, email, full_name, organization }
updateUserInformation (Firebase callable)	PUT /v1/users/me — body { full_name, is_registered_to_receive_api_announcements }
organization: string (free text)	organization_id: string resolved via org search/create flow (see §3)

Where the calls happen (saga changes)

• auth-saga.ts — emailLoginSaga and loginWithProviderSaga: replace retrieveUserInformation with GET /v1/users/me. The upsert ensures the row exists; isRegistered is derived from whether full_name is set (same semantics as today).
• auth-saga.ts — signUpSaga: add a GET /v1/users/me call after createUserWithEmailAndPassword to provision the row immediately.
• profile-saga.ts — refreshUserInformation: replace updateUserInformation with PUT /v1/users/me.
• Registration form: organization field removed for MVP. Post-MVP it will be replaced with the search-or-create org flow described in §4.

Authentication header

The UI already attaches the Firebase ID token as a Bearer header via getUserAccessToken() in api-auth-middleware.ts. The new user_service endpoints use the same RequestContextMiddleware — no new auth wiring needed.

---

9. Feature Flags

Organization features are post-MVP but share the same codebase. Feature flags decouple deployment from release: org-related endpoints and UI flows are deployed but inactive until the flag is enabled per environment.

Current system (Firebase Remote Config)

The web already has a feature flag system backed by Firebase Remote Config (src/lib/remote-config.server.ts). Key characteristics:

• Flags are fetched server-side via Firebase Admin SDK and cached in Next.js data cache (5 min dev / 1 hr prod).
• Defaults are defined in src/app/interface/RemoteConfig.ts as a typed RemoteConfigValues interface.
• @mobilitydata.org users get all boolean flags forced to true via the featureFlagBypass email-regex config.
• Existing flags: enableMetrics, enableLanguageToggle, enableFeedStatusBadge, gbfsValidator, gtfsFeatureTracker, etc.

This system is not replaced — new SQL-based flags run in parallel until a full migration is complete.

New approach: SQL-based flags

SQL flags are more dynamic: no Firebase console access required, no deploy or cache flush needed, and they are queryable alongside user/subscription data.

SQL tables (in the users schema):

MVP — global flag definition + per-user:

-- Global flag registry: defines available flags and their default state.
CREATE TABLE users.feature_flag (
    name        TEXT PRIMARY KEY,               -- e.g. 'organizations'
    enabled     BOOLEAN NOT NULL DEFAULT FALSE, -- global default
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Per-user override: a row here overrides the global default for that user.
-- Absence of a row means the user inherits the global default.
CREATE TABLE users.user_feature_flag (
    user_id     TEXT NOT NULL REFERENCES users.app_user(firebase_uid) ON DELETE CASCADE,
    flag_name   TEXT NOT NULL REFERENCES users.feature_flag(name) ON DELETE CASCADE,
    enabled     BOOLEAN NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, flag_name)
);

-- Seed global flags
INSERT INTO users.feature_flag (name, enabled, description) VALUES
  ('organizations', false, 'Org CRUD, membership, join requests, merge endpoint, org-level subscriptions');

Post-MVP — org-level overrides:

-- Per-org override: evaluated after global default, before per-user override.
-- Resolution order: user override > org override > global default.
CREATE TABLE users.org_feature_flag (
    org_id      INTEGER NOT NULL REFERENCES users.organization(id) ON DELETE CASCADE,
    flag_name   TEXT NOT NULL REFERENCES users.feature_flag(name) ON DELETE CASCADE,
    enabled     BOOLEAN NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (org_id, flag_name)
);

Resolution logic (MVP: user → global; post-MVP: user → org → global):

effective_value =
  user_feature_flag.enabled          if row exists for (user_id, flag_name)
  ELSE org_feature_flag.enabled      if user has org membership and row exists  [post-MVP]
  ELSE feature_flag.enabled          (global default)

10. Proposed Follow-up Issues

MVP

#	Title	Scope
1	[user-service] docs/UserServiceAPI.yaml + gen scripts	Single OpenAPI schema covering users and notifications (org endpoints marked as post-MVP stubs). Add scripts/api-user-service-gen.sh, scripts/gen-user-service-config.yaml, and scripts/db-user-service-gen.sh (sqlacodegen search_path=users → api/src/shared/user_database_gen/).
2	[user-service] users schema Liquibase migration (MVP tables)	Create schema + MVP tables: app_user (with legacy_org_name), notification_type, notification_subscription, notification_log.
3	[user-service] migrate_firebase_users task in tasks_executor	Idempotent: reads all Firestore user records via Firebase Admin SDK, upserts users.app_user (skip existing by firebase_uid), stores raw org string in legacy_org_name. Supports dry_run. Safe to run repeatedly during deployment window.
4	[user-service] GET/PUT /v1/users/me — profile API with upsert	GET upserts app_user on first call (replaces retrieveUserInformation Firebase callable). PUT updates profile (replaces updateUserInformation). isRegistered derived from full_name != null.
5	[web] UI profile service migration	In mobilitydatabase-web: replace retrieveUserInformation / updateUserInformation Firebase callables with REST calls to /v1/users/me. Update signUpSaga to call the new API. TBDOrganization field removed from registration form for MVP.
6	[notifications] Define predefined notification types	Seed notification_type table; agree on the initial set of events (feed published, status changed, dataset updated, validation report ready).
7	[notifications] Subscription management API	POST/DELETE /v1/notifications/subscriptions; user-level only for MVP.
8	[notifications] Notification dispatcher Cloud Function	Scheduled function that evaluates subscriptions and sends daily email digest via Brevo.
9	[notifications] Email template design	Jinja2 templates for daily digest and event emails.
10	[infra] Brevo integration + secrets	Add BREVO_API_KEY to Secret Manager; wire into the dispatcher function.
11	[user-service] SQL-based feature flag infrastructure	Add users.feature_flag + users.user_feature_flag tables to Liquibase migration (MVP). Add api/src/utils/feature_flags.py with is_enabled(user_id) resolution. Add features object to UserProfile response in UserServiceAPI.yaml. Update web to merge SQL flags into RemoteConfigValues.
12	[web] stretch goal Notifications UI section	The user is able to subscribe or unsubscribe from pre-defined notifications

Post-MVP

#	Title	Scope
13	[user-service] users schema Liquibase migration (org tables)	Add organization, organization_membership, organization_join_request, org_merge_candidate. Add org_id column to notification_subscription.
14	[user-service] Organization CRUD + role assignment + join requests API	GET /v1/organizations?name= (search), POST /v1/organizations (create + auto-admin), member management, role assignment, POST/GET/PATCH /v1/organizations/{id}/join-requests. Organization optional at registration.
15	[user-service] POST /v1/organizations/{id}/merge endpoint	platform_admin only. Reassigns all memberships and subscriptions from source orgs to the target, then deletes sources.
16	[user-service] Organization data migration from Firestore	cluster_legacy_organizations task: fuzzy-clusters legacy_org_name with rapidfuzz → org_merge_candidate. Human review by platform_admin. assign_legacy_org_memberships task finalizes links. Drop legacy_org_name once complete.
17	[notifications] Org fan-out support	At dispatch time, expand org_id subscriptions to individual member emails.
18	[spike] Evaluate custom notification query safety	Decide whether custom filters re-use the existing feed-filter layer or allow raw queries; assess rate-limiting and abuse vectors.
19	[user-service] SQL-based Org. feature flag	add users.org_feature_flag and update resolution to user → org → global.

[davidgamez]

• Weekly cadence in the notifications.
• Be able to trigger notifications for a user on demand.