← All three datastores

Core datastore

Harvested records and their change history, plus organizations, teams, memberships, watches, API keys and integrations.

govdeets · DATABASE_URL · prisma/core/schema.prisma

GovDEETS — Core datastore

Generated by prisma-markdown

Records

Record

One row per source record we've ever seen (a contract award, grant opportunity, entity registration, etc). Updated in place as new polls come in; every change is additionally captured as a ChangeEvent below, so the full history stays queryable.

Properties as follows:

  • id:
  • sourceSystem:
  • sourceRecordId:
  • recordType:
  • title:
  • agencyName:
  • subAgencyName:
  • recipientName:
  • awardAmount:
  • obligatedAmount:
  • currency:
  • status:
  • naicsCode:
  • pscCode:
  • placeOfPerformance:
  • periodStart:
  • periodEnd:
  • description:
  • normalized

    JSON blob: full normalized record, a superset of the columns above. Anything a source gives us that has no column lands in here under extra rather than being dropped.

  • raw: JSON blob: last raw payload from the source API, for audit and debug.
  • contentHash: Hash of normalized, used to detect changes cheaply.
  • currentVersion

    Incremented on every detected change. The version history API addresses snapshots by this number, so it has to live on the record itself rather than being derived by counting events.

  • firstSeenAt:
  • lastSeenAt:
  • lastChangedAt:
  • removedAt: Set when a poll no longer returns this record.

ChangeEvent

Append-only audit log: one row per detected change to a Record.

Properties as follows:

  • id:
  • recordId:
  • pollRunId:
  • changeType:
  • changedFields: JSON array of field names that changed.
  • previousValues: JSON object of {field: oldValue}. Null for "created".
  • newValues: JSON object of {field: newValue}.
  • detectedAt:

RecordSnapshot

A full point-in-time copy of a record's normalized form.

ChangeEvent alone can reconstruct any past version by replaying diffs backwards, but that is expensive per request and fragile across schema changes. Materialising the snapshot makes "give me version 7" a single indexed read, which is what the paid version-history API needs to be.

Properties as follows:

  • id:
  • recordId:
  • version: Matches Record.currentVersion at the moment of capture.
  • normalized:
  • contentHash:
  • capturedAt:
  • pollRunId:

Polling

PollRun

One row per connector run against a source system, for observability: did the daily poll succeed, how many records changed.

Properties as follows:

  • id:
  • sourceSystem:
  • startedAt:
  • finishedAt:
  • status:
  • recordsFetched:
  • recordsCreated:
  • recordsUpdated:
  • recordsRemoved:
  • errorMessage:

PollRequest

One row per outbound HTTP request a connector makes while fulfilling a PollRun — a single poll can be many paginated requests, and PollRun alone can't answer "how many API calls actually worked", or catch a run that reports success having quietly dropped some pages.

Properties as follows:

  • id:
  • pollRunId:
  • endpoint:
  • statusCode:
  • latencyMs:
  • attempt:
  • error:
  • createdAt:

Tenancy

Organization

A tenant. Also the unit that pays: an Organization maps one-to-one onto a BillingAccount in the billing datastore.

Every user gets a personal organization on first sign-in, so there is exactly one tenancy model rather than a "personal account" special case that every query has to remember.

Properties as follows:

  • id:
  • slug:
  • name:
  • avatarUrl:
  • billingAccountId: Opaque pointer to BillingAccount in the billing datastore.
  • planKey

    Denormalized copy of the active plan key, refreshed whenever billing changes. Authorization checks run on every request; they must not need a second database round trip to answer "is this org on a paid plan". The billing datastore stays canonical — this is a cache, and lib/authz treats it as one.

  • isPersonal

    True for the auto-created single-user org, which cannot be renamed or have members invited into it.

  • createdByUserId:
  • createdAt:
  • updatedAt:

Membership

A user's place in an organization, and the role that place carries.

Roles are a fixed enum rather than rows in a Role table. The permission matrix lives in src/lib/authz/permissions.ts, where it is typed, diffable and testable; a database-driven RBAC table buys flexibility no customer has asked for at the cost of every check becoming a join. EntitlementOverride in billing already covers the per-customer exceptions that would otherwise justify it.

Properties as follows:

  • id:
  • organizationId:
  • userId: Opaque pointer to User in the auth datastore.
  • role:
  • status:
  • invitedByUserId:
  • createdAt:
  • updatedAt:

Team

A group within an organization. Teams own shared watches and shared views; they are the unit that stops a 40-person org from being one undifferentiated feed.

Properties as follows:

  • id:
  • organizationId:
  • slug:
  • name:
  • description:
  • createdByUserId:
  • createdAt:
  • updatedAt:

TeamMembership

Team membership hangs off Membership rather than off a raw userId, which makes it structurally impossible to put someone on a team in an organization they don't belong to.

Properties as follows:

  • id:
  • teamId:
  • membershipId:
  • role:
  • createdAt:

Invitation

A pending invitation. Only the hash of the invite token is stored, for the same reason session tokens are hashed: a read of this table must not be enough to join someone else's organization.

Properties as follows:

  • id:
  • organizationId:
  • email:
  • role:
  • teamId:
  • tokenHash:
  • expiresAt:
  • acceptedAt:
  • revokedAt:
  • invitedByUserId:
  • createdAt:

AuditEvent

Who did what, inside an organization. Distinct from AuthEvent in the auth datastore, which records who got in; this records what they did once they were in.

Properties as follows:

  • id:
  • organizationId:
  • actorUserId:
  • action

    member.role_changed, apikey.created, watch.deleted, integration.connected, …

  • target: The thing acted on, as type:id — membership:abc, watch:def.
  • metadata:
  • ip:
  • createdAt:

Watching

Watch

A standing interest in something. The unit users think of as "following".

Scope decides what is being followed, and only one of the three target shapes is populated:

RECORD — one specific record, via recordId SAVED_QUERY — everything matching a filter, via query ENTITY — everything touching an agency / recipient / NAICS / PSC, via entityKind + entityValue

A Watch on its own fires nothing. What it fires on is decided entirely by its WatchTriggers, which is what makes "tell me only if the money goes down" expressible without a bespoke column for every question.

Properties as follows:

  • id:
  • organizationId:
  • createdByUserId: Opaque pointer to User in the auth datastore.
  • membershipId:
  • teamId:
  • visibility:
  • name:
  • scope:
  • recordId:
  • query

    Populated when scope = SAVED_QUERY. The same filter shape the change feed UI produces, so a saved search and a watch are the same object.

  • entityKind: Populated when scope = ENTITY: agency, recipient, naics, psc.
  • entityValue:
  • muted:
  • digest

    How often matched hits are delivered. REALTIME sends per hit; the others batch, which is the difference between useful and unbearable on a broad saved query.

  • createdAt:
  • updatedAt:
  • lastEvaluatedAt:

WatchTrigger

One condition on one field. A Watch fires when any of its enabled triggers matches a ChangeEvent — triggers are OR-ed, because the alternative (arbitrary boolean trees) is a query builder, and nobody has ever enjoyed using one.

field is a normalized-record path: a top-level column name (awardAmount, description, periodEnd), a dotted path into the extra bag (extra.solicitationNumber), or * for any field at all.

operand is stored as text and parsed according to the operator — a number for the threshold operators, a regular expression for MATCHES, a literal for the equality and containment operators, and ignored entirely for ANY_CHANGE and the record-lifecycle operators.

Properties as follows:

  • id:
  • watchId:
  • field:
  • operator:
  • operand:
  • caseSensitive:
  • cooldownSeconds

    Suppress repeat fires of this trigger for this many seconds. A record that is being amended in a burst should notify once, not eleven times.

  • label: Optional human label, shown instead of the generated description.
  • enabled:
  • lastFiredAt:

WatchChannel

Where a watch's hits go. A watch with no channels still records hits, which show up in the in-app feed; adding channels fans them out further.

Properties as follows:

  • id:
  • watchId:
  • kind:
  • integrationId:
  • target

    Channel-specific destination: a Slack channel ID, an email address, a Jira project key, a webhook URL path.

  • enabled:
  • createdAt:

WatchHit

A trigger that matched. This is the join between the change pipeline and the notification pipeline, and it is deliberately a persisted row rather than a transient event: it is what makes "why did I get this alert" answerable months later.

Properties as follows:

  • id:
  • watchId:
  • triggerId:
  • recordId:
  • changeEventId:
  • field

    The specific field that satisfied the trigger, and the values either side of it — copied rather than referenced so the alert still reads correctly after the record moves on again.

  • previousValue:
  • newValue:
  • firedAt:

Notification

One delivery of one hit to one person over one channel. Fanning out here rather than at send time means a failed Slack post can be retried without re-sending the email that already went out.

Properties as follows:

  • id:
  • organizationId:
  • watchHitId:
  • userId: Opaque pointer to User in the auth datastore.
  • channel:
  • status:
  • attempts:
  • deliveredAt:
  • error:
  • readAt:
  • createdAt:

SavedView

A saved column layout, filter set and sort. The change feed is a dense table with a lot of knobs; this is how a configuration survives a reload and gets shared with a team.

Properties as follows:

  • id:
  • organizationId:
  • userId: Opaque pointer to User in the auth datastore.
  • teamId:
  • name:
  • config: Visible columns, their order, filters, sort, and row density.
  • isDefault:
  • createdAt:
  • updatedAt:

Integrations

ApiKey

A programmatic credential for the public API, scoped to an organization.

Only the hash is stored. prefix is the first few characters of the key, kept in the clear so the settings page can show which key is which and so an incoming request can be looked up by prefix before the hash comparison.

Properties as follows:

  • id:
  • organizationId:
  • name:
  • prefix: Displayed as gdk_live_a1b2c3….
  • keyHash:
  • scopes

    Feature keys this credential may exercise, intersected with whatever the organization's plan actually entitles it to. A key can be narrower than the plan; it can never be wider.

  • createdByUserId:
  • lastUsedAt:
  • expiresAt:
  • revokedAt:
  • createdAt:

ApiRequestLog

Per-request log for the public API. The billable rollup of this is pushed to UsageRecord in the billing datastore; the detail stays here, because operational data has no business sitting next to invoices.

Properties as follows:

  • id:
  • organizationId:
  • apiKeyId:
  • method:
  • path:
  • feature: The entitlement feature key this request was billed against.
  • statusCode:
  • latencyMs:
  • bytesOut:
  • createdAt:

Integration

A connection to an external system that GovDEETS pushes into.

config holds the non-secret half (workspace ID, default channel, Jira project key). secretRef is a pointer into the secret store, never the secret itself — so a dump of this table cannot be used to post into anyone's Slack.

Properties as follows:

  • id:
  • organizationId:
  • kind:
  • name:
  • status:
  • config:
  • secretRef:
  • createdByUserId:
  • createdAt:
  • updatedAt:
  • lastVerifiedAt:
  • lastError:

IntegrationDelivery

One outbound call to an integration, kept for the same reason PollRequest is kept: "we sent it" and "it arrived" are different claims, and only the second one matters when a customer says they never got the alert.

Properties as follows:

  • id:
  • integrationId:
  • notificationId:
  • requestBody:
  • statusCode:
  • latencyMs:
  • attempt:
  • error:
  • createdAt: