Data model and ownership

Principal graph

User ── OrganizationMembership ── Organization ── OrgSettings
  │              └── OrganizationMembershipGrant │
  │                                               ├── IntegrationConnection ── provider links/cursors/jobs
  │                                               └── Project ── WorkItem ── WorkItemTransition
  │                                                      └── Iteration
  └── TimeEntry ─────────────────────────────────────────┘
         ├── Screenshot
         ├── ActivitySnapshot
         └── AppUsage

User ── AuthSession
User ── DesktopInstallation
          ├── ComplianceEnrollment ── posture snapshots/evaluations/findings/exceptions/refresh
          ├── AiPackInstallationReport
          └── RemoteActionDeviceCapability ── executions/terminal sessions
AuthChallenge
IdempotencyRecord
IntegrationConnection ── ImportJob ── segments/issues
Organization ── SyncJob
AiModelPack ── AiPackCatalogEvent
Organization ── Customer ── CustomerContact
              └── CustomerProject ── Project
WorkItem ── WorkItemCustomerVisibility ── CustomerProject
TimeEntry ── TimeEntryCustomerAllocation ── CustomerProject
DeveloperWorkSession ── AgentRun/AgentRunSegment
                     └── ProjectTimeAllocation ── TimeEntry(AGENT_ALLOCATION)
AgentIngestionOutbox ── AgentTelemetryProjectorCursor
Organization ── TelemetryGovernanceJob
Organization ── ReportDefinition/Revision ── ReportModelBinding
              └── ReportTemplate/Version
              └── ReportRun ── ReportSnapshot/Artifact/Delivery/DeliveryAttempt
              └── ReportSchedule/ReportAuditEvent/ReportObjectDeletion

The automatic-time authority is DeveloperWorkSession plus revisioned ProjectTimeAllocation. Finalization creates closed TimeEntry(source = AGENT_ALLOCATION) projections for existing timesheet consumers; corrections supersede projections and stale dependent customer allocation without mutating frozen report snapshots. Customer visibility and exact customer-second allocation are separate, fail-closed relations. Report objects are tracked independently from their immutable database ledger so retryable deletion and orphan reconciliation never erase historical audit state.¹²

Ownership invariants

• OrganizationMembership is active only while at least one non-revoked OrganizationMembershipGrant survives; the highest active grant determines effective authorization.
• Project.organizationId is the tenant anchor for project work data.
• A TimeEntry belongs to a user and either a project or an explicit organization for automatic tracking. A work item requires a project. Unattributed allocation projections reference their DeveloperWorkSession; manualAllocation preserves developer corrections from late automatic reconciliation.³
• Screenshot, activity and app usage inherit ownership and tenant from their time entry.
• Work items, iterations and transitions inherit tenant from their project. Provider identity exists only in explicit external-link rows.
• An import mapping for a tenant may reference only users and projects belonging to that tenant.
• Device feature rows reference one shared DesktopInstallation; feature policy, audit and revocation remain separate.
• AiModelPack is a global immutable catalog record. AiPackCatalogEvent records operator identity, previous/next status and evidence for every supported publication transition independently of organization assignment audit.

These invariants are enforced by centralized application policies and database predicates. Generic project mutation cannot silently transfer a project between organizations.

Concurrency invariant

A user may have at most one running time entry. PostgreSQL enforces this with a partial unique index, while application conflict handling returns the existing state safely. Activity snapshots and screenshot storage paths have uniqueness constraints where a retry would otherwise duplicate a logical write.

Session and challenge state

• AuthChallenge is the expiring, atomically consumed authority for native identity recovery, OAuth PKCE and installation registration purposes.
• AuthSession owns refresh-family state, expiry, revocation and reuse detection; User.tokenVersion invalidates previously issued access claims.
• Access-token role claims are hints only: administrative access rechecks the current database session and membership.

Durable work and idempotency

• ImportJob and SyncJob persist status, attempt count, lease owner/expiry, heartbeat, checkpoint, cancellation and terminal error. Partial unique constraints prevent duplicate active synchronization and overlapping incremental Hubstaff work for one connection.
• IdempotencyRecord binds an actor, operation key and request fingerprint to a recorded response and expiry.
• Workers reclaim expired leases, advance checkpoints and never rely on process memory as the authoritative job state.

Privacy and object lifecycle

Organization settings hold explicit external-AI, window-context and retention controls with privacy-preserving defaults. Screenshot rows retain a scoped storage path and deletion state so object deletion can be retried and orphaned objects can be reported instead of hiding an inconsistent delete.

Schema authority

apps/backend/prisma/schema.prisma and migrations define executable persistence. This article explains intent and must be updated with every meaningful model or ownership change.

Database engineering review — 2026-09-08

The database engineering review distinguishes intended invariants from observed implementation gaps. Live PostgreSQL contains 117 application models plus migration metadata and is approximately 45 MB. Screenshot asset reuse conflicts with the still-unique occurrence storage path; direct/derived TimeEntry tenant ownership leaves projectless data outside some retention and analytics queries; nullable composite unique keys require domain-specific review. These are findings and recommendations, not implemented schema changes.⁴

Factory command persistence (issue109)

Local implementation adds FactoryProject (unique immutable project/repository binding) and FactoryCommand (UUID, original actor, issue/role, repository snapshot, delivery state, validated receipt and fenced delivery lease). Both retain project references with restricted deletion. Their additive migration does not alter existing rows. See Factory control and snapshot; deployment remains open.

Factory request persistence (issue113)

FactoryRequest retains the original actor, immutable text and UUID before a GitHub issue exists. It references FactoryProject with restricted deletion and holds the validated remote receipt plus delivery lease/backoff fields. It is separate from FactoryCommand, which requires an existing issue and execution role. The additive generated migration preserves existing rows. See implementation snapshot; online acceptance remains open.

Footnotes

1. AT, TS, customer and report foundation implementation evidence. ↩

2. Active AT, TS, CU and RP workstreams at maximum repository-local implementation. ↩

3. Projectless automatic developer tracking implementation ↩

4. Database audit evidence. ↩