Data model overview
How PostgreSQL records the people, organizations, submissions, and engagements in ROUNDTABLE
The data model is the vocabulary of ROUNDTABLE. If you are new to Prisma, think of each model as a database table and each field as a column. Prisma generates a typed client from prisma/schema.prisma, so the names documented here are also the names application code uses when it reads or writes PostgreSQL.
ROUNDTABLE has two connected stories. Industry organizations and their users submit technical information; government users organize commands, portfolio nodes, reviews, and engagements. Notifications and audit logs connect those stories without giving an industry user access to internal government notes.
How to read the schema
| Schema word | Plain-English meaning |
|---|---|
String? | Optional text. PostgreSQL stores NULL when no value is supplied. |
String | Required text. The application must provide a value. |
@default(...) | Prisma supplies a value when the caller omits the field. |
@updatedAt | Prisma updates the timestamp whenever the row changes. |
@relation | A foreign-key relationship to another model. |
@@unique | The combination must not be duplicated. |
cuid() | Prisma-generated collision-resistant identifier. |
The schema uses PostgreSQL's DateTime for timestamps and dates. Engagement date and call closesAt are represented as timestamps even though the UI commonly asks for a calendar date.
Relationship map
Ownership and visibility
The foreign keys describe connections, but authorization determines who may see them. An industry session is scoped to its own Organization through session.user.organizationId. Government sessions can read the government ledger, with command restrictions applied to command leads. OrgDialogueNote, review assignments, match rationales, and audit details are government-side records; they should not be treated as public organization-profile content.
Model groups
Start with users and roles if you need identity context. Then follow commands and portfolios to understand routing, organizations and submissions for intake, and engagements for the ledger. Announcements and calls are publishing records; notifications and audit explain user-facing alerts and accountability.
Database lifecycle
pnpm db:setup generates the Prisma client, deploys migrations, and runs the seed script. Migrations change the database structure; seeding inserts demo records and is intentionally separate from production migration. The schema's relation fields are not separate database columns: for example, Submission.organization is the Prisma navigation property backed by organizationId.
Important design choices
- Technology tags are comma-separated lowercase text, not a separate tag table. This keeps the pilot simple and makes the default matching deterministic.
- Command and portfolio hierarchies use self-relations (
parentIdpluschildren). The parent-reassignment API performs cycle checks. - Optional foreign keys support partial pilot records. A submission can be text-only, an organization can register before a government review, and a notification can exist without a link.
- Audit events use a flexible
eventstring and optional JSON text indetail, allowing new operational events without a migration for every event name.
Source of truth
When this page disagrees with a UI label, the schema and route implementation win. The application currently uses email/password credentials; CAC (Common Access Card), FlankSpeed (the Department of the Navy identity environment), and Impact Level 5 hosting are roadmap work rather than fields in this schema.
How to read the model pages
Each field table separates four questions that are easy to confuse:
| Question | What to look for |
|---|---|
| What is stored? | The Prisma type, such as String, DateTime, Boolean, or an enum. |
| Must it be present? | A non-optional Prisma type is required at create time; ? means nullable. |
| What happens when omitted? | The default column shows now(), cuid(), an enum, a Boolean, or a string default where one exists. |
| What does it mean? | The plain-English meaning and route usage explain the operational purpose. |
Relation fields are not duplicate columns. For example, organizationId is the stored foreign-key value while organization is Prisma's navigation relation. A route can select either the scalar ID, the related object, or both. Nullable relation IDs are how the schema represents records that have not yet been assigned a reviewer, call, parent, or portfolio node.
Relationship reading guide
Follow arrows in the diagrams from the owning record to its related records:
- A
Usercan belong to one command and one industry organization, but a government user normally uses the command side. - An
Organizationowns many submissions and engagements; those records retain the organization ID for accountability. - A
Submissioncan point to one call and reviewer, and can have many calculated submission matches. - A
Commandowns many portfolio nodes and engagements, while parent/child links provide the command tree. - An
Engagementowns notes, but each note identifies its author. - A
Notificationbelongs to one recipient; anAuditLogis intentionally not foreign-keyed to a user so failures and pre-login events can still be recorded by email.
The schema is therefore both a data store and an authorization vocabulary. Changing a relation can change what a user is allowed to see, not just what a page displays.