SessionEvent

SessionEvent is an append-only time-series log of Firebase auth token issuances, recording one row per distinct (membership, token_issued_at) tuple. It is written exclusively by the Firebase authentication strategy at request time and is the canonical source for WAU/MAU, retention, stickiness, and engagement-bucketing analytics. Each row is tied to a Membership (which in turn scopes a user to a workspace); there is no workspace FK on the row itself. The table was renamed from login_events in migration 20260511113135 and carries no soft-delete column — it is explicitly append-only.

Naming	Value
Object	SessionEvent
Resource type (JSON:API `type`)	`session_event`
Collection / records root	— _{(not a records root)}
REST base	`/v1/session-events`
Entity class	`SessionEvent`

Internal object. Not currently exposed on the public REST API. The operations below describe the intended contract.

API operations

Operation	Method & path	Status
List	`GET /v1/session-events`	🟡 Planned
Retrieve	`GET /v1/session-events/{id}`	🟡 Planned
Create	`POST /v1/session-events`	🟡 Planned
Update	`PATCH /v1/session-events/{id}`	🟡 Planned
Delete	`DELETE /v1/session-events/{id}`	🟡 Planned

Data model

Attributes

Field	Type	Required	Constraints	Allowed values	Description
session_event_id	uuid (🔒 system)	✅ Yes	unique; default gen_random_uuid()	—	Public stable identifier for this session event. Generated server-side via gen_random_uuid(); never supplied by the caller.
token_issued_at	timestamptz (🔒 system)	✅ Yes	NOT NULL; composite UNIQUE with membership_pk (session_events_membership_pk_token_issued_at_unique); indexed DESC (session_events_membership_pk_token_issued_at_desc_idx, session_events_token_issued_at_desc_idx)	—	The decoded.iat of the Firebase ID token, expressed as a timestamp. Identical for every API request served by the same 1-hour token, making this the structural dedup key. Concurrent inserts for the same (membership, token_issued_at) silently collapse to one row.
event_type	text (🔒 system)	✅ Yes	NOT NULL; DEFAULT 'login'; CHECK event_type IN ('login','refresh') — constraint name session_events_event_type_check	login \| refresh	Discriminates fresh sign-ins from silent token refreshes. 'login' when decoded.auth_time equals decoded.iat (within clock skew — user just authenticated); 'refresh' when decoded.iat > decoded.auth_time (Firebase SDK silently refreshed the token). Both are derived from the same decoded token at write time.
created_at	timestamptz (🔒 system)	✅ Yes	NOT NULL; set on insert via onCreate hook	—	Wall-clock timestamp of when the row was inserted. Distinct from token_issued_at (which is the token's iat). Legacy rows backfilled token_issued_at from this column during the rename migration.

Relationships

Name	Type	Required	Description
membership	to-one (ManyToOne)	✅ Yes	The Membership row that authenticated. Identifies both the user (Person) and the workspace scope. The FK column membership_pk carries the composite unique constraint with token_issued_at.

System-computed

session_event_id — generated via gen_random_uuid() DB default; also initialised in-process via randomUUID() as the ORM default value
created_at — set by MikroORM onCreate hook to new Date() at insert time; never updated
token_issued_at — sourced from decoded Firebase ID token iat field by firebase.strategy.ts; NOT user-supplied
event_type — derived from decoded.auth_time vs decoded.iat comparison in firebase.strategy.ts; default 'login'
Dedup on (membership_pk, token_issued_at) — enforced at the DB layer by the unique index; SessionEventRepository.recordIfNew catches SQLSTATE 23505 with constraint name session_events_membership_pk_token_issued_at_unique for silent dedup without swallowing unrelated unique violations
No deleted_at — the table is append-only by design; soft-delete does not apply to this entity

Example

{
  "data": {
    "type": "session_event",
    "id": "a3c7f2e1-84bb-4d2a-b910-1e5c2f3d4a5b",
    "attributes": {
      "session_event_id": "a3c7f2e1-84bb-4d2a-b910-1e5c2f3d4a5b",
      "token_issued_at": "2026-06-02T09:14:00.000Z",
      "event_type": "login",
      "created_at": "2026-06-02T09:14:01.123Z"
    },
    "relationships": {
      "membership": {
        "data": { "type": "membership", "id": "d1e2f3a4-5b6c-7d8e-9f0a-b1c2d3e4f5a6" }
      }
    }
  }
}

_{Source: apps/api/src/database/entities/SessionEvent.ts · domain: platform · tier: Activity}