Associations & Time Entries

Three pieces shipped with Work Requests but are generic — any record type in the entity registry can use them:

Piece	Code	Tables
Entity registry	apps/api/src/modules/shared/entity-registry.ts	—
Record associations	shared/associations.service.ts, shared/associations.controller.ts (in the global SharedModule)	record_associations
Time tracking	modules/time-entries/* (TimeEntriesModule)	time_entries, active_timers

All tables are created by migration 088_work_requests.

Entity Registry

ENTITY_REGISTRY is a static map from entity type to the metadata services need to treat records generically. The key is also the table name, the RBAC module name, and the entity_type value stored in polymorphic columns.

Type	Noun	Route	Owner columns (for record access)
leads	lead	/leads/:id	owner_id
opportunities	opportunity	/opportunities/:id	owner_id
accounts	account	/accounts/:id	owner_id
contacts	contact	/contacts/:id	owner_id
projects	project	/projects/:id	owner_id
tasks	task	/tasks/:id	assigned_to, owner_id
work_requests	work request	/work-requests/:id	requested_by, assigned_to

Each entry also carries label and subtitle SQL expressions and searchColumns, all written against the alias x:

work_requests: {
  table: 'work_requests', noun: 'work request', path: 'work-requests',
  label: `CONCAT(x.request_number, ' · ', x.title)`,
  subtitle: `x.status`,
  ownerColumns: ['requested_by', 'assigned_to'],
  searchColumns: ['x.request_number', 'x.title'],
},

SQL fragments are constants

Callers interpolate meta.table, meta.label, meta.subtitle and meta.searchColumns directly into SQL. That is only safe because they are compile-time constants — never build an EntityMeta from user input. Always validate an incoming type with getEntityMeta(type) first (unknown types → 400 Unsupported record type).

The module also exports ENTITY_TYPES, getEntityMeta(), the RequestActor interface and actorFromJwt() (see RBAC Deep Dive).

Adding a type

1. Add an entry to ENTITY_REGISTRY (the table must have id and deleted_at).
2. Make sure the type is an RBAC module so hasPermission(actor.permissions, type, 'view') and getAccessLevel() resolve.
3. Optionally add derived links for it in DERIVED_LINKS (below).
4. Work request types can then allow it in allowed_entities (every registry type except work_requests is requestable).

Record Access for Any Entity

Two DataAccessService methods work off the registry rather than a single owner_id:

buildEntityAccessFilter(schema, userId, entityType, alias, paramOffset = 1)
  → { whereClause, params, resolvedUserIds }

canAccessEntity(schema, userId, entityType, entityId) → boolean

A record is visible when any of these hold:

• record access for the type is all (whereClause = '1=1', no params)
• any of meta.ownerColumns is in getScopeUserIds(...) for the user's level
• the user is in record_team_members for the record
• work requests only: the user is in user_teams for the request's team_id

The filter always contributes three params ([scopeUserIds, entityType, userId]) at $paramOffset … $paramOffset + 2; place them after your own params. Unknown types return 1=0.

const filter = await this.dataAccess.buildEntityAccessFilter(schema, actor.id, 'tasks', 'x', 3);
const rows = await this.dataSource.query(
  `SELECT x.id FROM "${schema}".tasks x
    WHERE x.title ILIKE $1 AND x.deleted_at IS NULL AND ${filter.whereClause}
    LIMIT $2`,
  [`%${q}%`, 20, ...filter.params],
);

getAccessLevel() reads roles.record_access from the database, so scope changes apply on the next request.

Record Associations

Manual many-to-many links between any two registry records, read from both sides.

Table

record_associations (
  id UUID PK,
  source_type VARCHAR(50), source_id UUID,
  target_type VARCHAR(50), target_id UUID,
  label VARCHAR(100),
  created_by UUID, created_at TIMESTAMPTZ, deleted_at TIMESTAMPTZ,
  CHECK (NOT (source_type = target_type AND source_id = target_id))   -- no self-links
)

Pair uniqueness

A link A→B and B→A is the same link, so uniqueness is enforced on the ordered pair, not on the columns:

CREATE UNIQUE INDEX uq_record_associations_pair
  ON record_associations (
    LEAST(source_type || ':' || source_id::text, target_type || ':' || target_id::text),
    GREATEST(source_type || ':' || source_id::text, target_type || ':' || target_id::text)
  ) WHERE deleted_at IS NULL;

Inserts use ON CONFLICT DO NOTHING. When nothing is returned, add() looks up the existing pair in either direction and returns it — adding an existing link is idempotent. Because the index is partial on deleted_at IS NULL, a removed (soft-deleted) link can be re-added. Partial indexes on (source_type, source_id) and (target_type, target_id) serve the two-sided read.

Derived links

list() merges manual links with links the schema already holds, so nothing is stored twice. DERIVED_LINKS in associations.service.ts declares, per source type, a query returning related ids and a "via" label:

Source	Derived from
leads	account_id / converted_account_id, contact_id / converted_contact_id, converted_opportunity_id / opportunities.lead_id, tasks on the lead, requests raised on the lead
opportunities	account_id, the converting lead, opportunity_contacts, projects with opportunity_id, tasks, requests
accounts	contact_accounts + contacts.account_id, opportunities, leads, projects, tasks, requests
contacts	contact_accounts + account_id, opportunity_contacts, leads, projects, tasks, requests
projects	source opportunity, account, contact, tasks (project_id or related entity), work requests (project_id or raised on the project)
tasks	related_entity_type/id, project_id
work_requests	the primary record (entity_type/id), project_id (Delivered as), tasks on the request

Derived links are returned with via set and removable: false. A manual link to the same record replaces the derived entry (keeping via) so it can still be removed. A failing derived query is logged and skipped rather than failing the list.

Access filtering

list() never leaks records the caller can't open:

1. The caller must be able to view the source record (view permission + canAccessEntity), else 404.
2. Linked ids are grouped by type. For each type, if the caller lacks view permission on it, all its records are hidden; otherwise labels are fetched through buildEntityAccessFilter, so only in-scope records come back.
3. Each group reports hiddenCount — linked records that exist but the caller may not open.

[
  {
    "type": "opportunities", "noun": "opportunity", "hiddenCount": 1,
    "records": [
      { "id": "…", "label": "Acme renewal", "subtitle": null, "url": "/opportunities/…",
        "associationId": "…", "associationLabel": "Upsell", "via": null, "removable": true }
    ]
  }
]

Write rules:

Action	Requires
add	edit permission on the source type, and view access to both records
remove	edit permission + record access on either side
linkSystem	Nothing — for system flows (lead-conversion carry-over). Idempotent.

Add and remove write an audit log entry (entityType: 'record_associations') and a record_linked / record_unlinked activity on both records. The record picker (GET /associations/search) needs view on the type, a 2-character minimum, and returns at most 50 rows filtered by buildEntityAccessFilter.

On the frontend, AssociatedRecordsPanel is shown on lead, opportunity, contact, account, task and project detail pages (api/associations.api.ts).

Time Entries

Generic time tracking for any registry record. Project time keeps its own tables (project_time_entries, project_active_timers); see Projects API.

Tables

time_entries (
  id, entity_type, entity_id,          -- the record time is logged on
  task_id → tasks,                     -- optional CRM task (e.g. the meeting)
  user_id → users (CASCADE),
  description, minutes INT CHECK (minutes > 0),
  logged_at DATE DEFAULT CURRENT_DATE,
  started_at, ended_at,                -- set for timer entries
  source 'manual' | 'timer',
  is_billable, hourly_rate, currency,
  created_by, created_at, updated_at, deleted_at
)

active_timers (
  id, user_id UNIQUE → users (CASCADE),
  entity_type, entity_id, task_id, description,
  started_at, created_at
)

Indexes: time_entries(entity_type, entity_id), (user_id, logged_at), (task_id); active_timers(entity_type, entity_id).

One timer per user

A user runs at most one timer across both systems:

1. startTimer() rejects if the user has a row in active_timers.
2. It also rejects if the user has a row in project_active_timers ("A project timer is running on …").
3. The INSERT … ON CONFLICT (user_id) DO NOTHING on active_timers.user_id UNIQUE is the last guard against a double click.

stopTimer() deletes the timer row (DELETE … RETURNING) and inserts a time_entries row with source = 'timer', started_at/ended_at, and minutes = elapsed (minimum 1, capped at 24 h) unless the body supplies minutes. discardTimer() deletes without logging. Completing, cancelling or deleting a work request deletes any timer running on it.

note

The check is one-directional: the generic timer checks project_active_timers, but the project timer endpoint (POST /projects/:id/tasks/:taskId/timer/start) checks only project_active_timers.

Rules

Rule	Detail
Minutes	1 – 1440 per entry
Billing defaults	From the record when it carries them — for work_requests, the request's is_billable, hourly_rate, currency. Overridable per entry on create/update. Other records default to non-billable.
Who can log	Work requests: the assignee, members of the owning team, or managers of the request. Other records: anyone who can view the record. Plus time_entries.create.
Who can see	Entries on a record: anyone who can view the record. Cross-record list (no entityType): entries by users inside the caller's time_entries scope.
Who can edit / delete	The entry's own user, or a caller whose time_entries record scope covers that user (canAccessRecord). Plus time_entries.edit / delete.
Side effects	Audit log on create / update / delete; time_logged / time_updated / time_deleted activity on the record the time is on.

Amounts are computed as minutes / 60 * hourly_rate for billable entries with a rate. GET /time-entries returns totals { minutes, billableMinutes, billableAmount } alongside the paginated data / meta; GET /time-entries/summary returns per-person totals on one record.

RBAC

Module time_entries (label Time Tracking, actions view/create/edit/delete), record-scoped. Seeded by 088: all roles get all four actions; record access all for admin roles, own for others.

API

See Associations API and Time Entries API.