Skip to main content

Database Schema

The following documentation represents the core tables, structures, and relationships in the Jet Admin PostgreSQL database, verified directly from schema.prisma.

Entity Relationship Diagram (ERD)​

Table Definitions​

1. User & Tenant Administration​

  • tblTenants: Root multi-tenant entity. Tracks logo URL, disabled state, and metadata.
  • tblUsers: Core user profiles synchronized from Firebase Auth (firebaseID).
  • tblUsersTenantsRelationship: Junction table assigning users to specific tenants (with role string defaults like MEMBER).
  • tblUserTenantConfigMap: Store of user preferences per tenant.

2. Authorization & RBAC​

  • tblRoles: Tenant-scoped role titles.
  • tblPermissions: Permission actions (such as workflow:create).
  • tblRolePermissionMappings: Links permission keys to specific roles.
  • tblUserTenantRoleMappings: Links users within a tenant to multiple custom roles.
  • tblAPIKeys: API keys representing services or integration accounts. Keys are stored as SHA-256 hashes (apiKey).
  • tblAPIKeyRoleMappings: Assigns roles to API Keys for granular service execution.

3. Application Pages & UI Widgets​

  • tblAppPages: Stores page screens and layouts configured as JSON arrays (appPageConfig).
  • tblWidgets: Defines component configurations (widgetConfig), types, and automatic refresh intervals.

4. Data Sources & Queries​

  • tblDatasources: Stores connection settings (datasourceOptions is AES encrypted at rest).
  • tblDataQueries: Stores dynamic query configurations (dataQueryOptions JSON) with schema details. Runs on load if enabled.

5. Workflow Execution Engine​

  • tblWorkflows: Parent metadata table for workflows.
  • tblWorkflowNodes: Configures specific workflow nodes (nodeConfig parameters, timeoutSeconds, and retryLimit).
  • tblWorkflowEdge: Declares DAG connection paths between nodes, defining custom condition mapping (edgeConfig and target handlers).
  • tblWorkflowInstances: Tracks active workflow executions, including a version field for optimistic lock concurrency guards.
  • tblWorkflowInstanceLogs: Append-only log trail recording node status transitions (INPUT_SET, NODE_COMPLETED, etc.) to fold execution contexts.
  • tblWorkflowDataCollectionRequests: Logs manual interaction requests (e.g., human-in-the-loop forms).

6. Event Listening & Scheduled Jobs​

  • tblListeners: Connection metadata for background datasource events (e.g., PostgreSQL CDC listener endpoint path and active status).
  • tblListenerActions: Action mapping routines triggering specific steps (like workflows) on data events.
  • tblListenerEvents: Ring buffer storing transformed event records (eventData JSON).
  • tblEventDLQ: Dead-letter queue tracking event dispatching errors, metadata, and retry counts.
  • tblCronJobs: Stores cron scheduler parameters (cronJobSchedule) executing target workflows.
  • tblCronJobHistory: Traces execution runs, durations, and output statuses from the cron daemon.