Database Schema

Overview

FaceKom uses PostgreSQL as its primary database with Sequelize ORM (custom @techteamer/sequelize fork v6.30.1). vuer_oss defines 55+ models and esign defines 27 models in separate databases.

vuer_oss Models (55+)

Entity Relationship Diagram

erDiagram
    User ||--o{ Room : creates
    User ||--o{ AuditLog : generates
    User ||--o{ EmailLog : sends
    User ||--o{ SmsLog : sends
    User ||--o{ Download : requests

    Customer ||--o{ Room : participates
    Customer ||--o{ SelfServiceRoom : starts
    Customer ||--o{ CustomerHistory : has
    Customer ||--o{ CallbackRequest : makes
    Customer ||--o{ Document : owns
    Customer ||--o{ Encryption : has
    Customer ||--o{ CustomerValidation : has
    Customer ||--o{ CustomerDataChange : tracks

    Room ||--o{ Activity : logs
    Room ||--o{ Feedback : receives
    Room ||--o{ MediaFile : records
    Room ||--o{ Document : contains
    Room ||--o{ CommunicationLog : logs
    Room ||--o{ Encryption : uses
    Room ||--o{ ClientErrorLog : logs
    Room ||--o{ Flow : executes

    SelfServiceRoom ||--o{ Activity : logs
    SelfServiceRoom ||--o{ Flow : executes
    SelfServiceRoom ||--o{ MediaFile : records
    SelfServiceRoom ||--o{ Document : contains

    Flow ||--o{ Task : contains
    Flow }o--|| FlowProto : "instance of"
    Flow }o--o| Flow : "parent"
    Task }o--|| TaskProto : "instance of"

    MediaFile }o--|| Encryption : "encrypted by"
    Attachment }o--|| Encryption : "encrypted by"
    Document }o--|| Attachment : has

Identity & Access

Model	Purpose	Key Relationships
User	Operator users	has many: Room, EmailLog, SmsLog, ClientErrorLog, Download, AuditLog
Customer	End customers	has many: Room, SelfServiceRoom, CustomerHistory, CallbackRequest, EmailLog, SmsLog, ClientErrorLog, Document, Encryption, Download
Session	Session storage	connect-session-sequelize
PasswordHistory	Password rotation policies	belongs to User
WebAuthnCredential	FIDO2/WebAuthn credentials	belongs to User
TotpKey	TOTP two-factor auth keys	belongs to User
OneTimeLogin	One-time login tokens	belongs to User

Verification Sessions

Model	Purpose	Key Relationships
Room	Video call rooms	belongs to Customer, User; has many: Activity, Feedback, MediaFile, Document, CommunicationLog, Encryption, ClientErrorLog, Flow
SelfServiceRoom	Self-service sessions	belongs to Customer; same relations as Room

Workflow Engine

Model	Purpose	Key Relationships
Flow	Workflow instances	belongs to FlowProto, Customer, Room, SelfServiceRoom, parentFlow; created/started/updated/finishedBy User
FlowProto	Workflow templates	has many Flow
Task	Individual workflow tasks	belongs to Flow, TaskProto
TaskProto	Task templates	has many Task
FlowActivity	Workflow activity log	belongs to Flow
FlowScope	Workflow variable scopes	belongs to Flow
FlowResult	Workflow results	belongs to Flow
FlowTranslation	Workflow i18n	belongs to FlowProto

Documents & Media

Model	Purpose	Key Relationships
Attachment	File attachments	belongs to Encryption, TimestampToken
MediaFile	Media recordings	belongs to Encryption, Room, SelfServiceRoom, ImportedRoom, TimestampToken
Document	Customer documents	belongs to Customer, Room, SelfServiceRoom, Attachment
DocumentVersion	Document version history	belongs to Document
SystemDocument	System-level documents	—
Encryption	Encryption metadata for files	belongs to Customer

Recognition & CV

Model	Purpose	Key Relationships
FaceRecognition	Face recognition records	—
FaceComparison	Face comparison records	—
RecognitionAttempt	Recognition attempt logs	—
EmrtdInfo	eMRTD (electronic passport) data	—

Communication

Model	Purpose	Key Relationships
CommunicationLog	WebRTC communication logs	belongs to Room
ClientErrorLog	Client-side error logs	belongs to User, Customer, Room, SelfServiceRoom
EmailLog	Email send logs	belongs to User, Customer
SmsLog	SMS send logs	belongs to User, Customer
Feedback	Customer/operator feedback	belongs to Room

Admin & Audit

Model	Purpose	Key Relationships
AuditLog	Audit trail	belongs to User, targetUser
Activity	System activity log	belongs to Room, SelfServiceRoom, User, Customer, encryptionCustomer
UserActivity	User-specific activity log	belongs to User
IntegrationLog	API integration audit logs	—
JanusEvent	Janus WebRTC gateway events	—

Configuration

Model	Purpose	Key Relationships
Setting	System settings	—
OpenHourStandard	Standard business hours	—
OpenHourException	Business hour exceptions	—
Cert	TLS certificates	—
Shortener	URL shortener	—
TimestampToken	Timestamp tokens (RFC 3161)	—

Business

Model	Purpose	Key Relationships
Job	Background jobs	—
BackgroundProcess	Background processing	belongs to 18 different models (polymorphic)
BackgroundProcessLog	Background process logs	belongs to BackgroundProcess
CallbackRequest	Callback scheduling	belongs to Customer
CallbackRequestActivity	Callback activity log	belongs to CallbackRequest
Partner	Partner organizations	has many PartnerService
PartnerService	Partner service definitions	has many PartnerServiceRequest
PartnerServiceRequest	Partner service requests	belongs to PartnerService
CustomerHistory	Customer event history	belongs to Customer
CustomerValidation	Customer data validation	belongs to Customer
CustomerDataChange	Customer data change tracking	belongs to Customer, with User scope
Download	Download tracking	belongs to User, Customer
Storage	Storage location records	—

Data Import

Model	Purpose
ImportedCustomer	Imported customer records
ImportedRoom	Imported room/session records
ImportedFlow	Imported workflow records
ImportedFlowTranslation	Imported flow translations

BackgroundProcess Polymorphism

BackgroundProcess has belongsTo associations with 18 different models — it acts as a polymorphic catch-all for tracking long-running operations across the entire system.

esign_oss Models (27)

Entity Relationship Diagram

erDiagram
    Customer ||--o{ Contract : has
    Customer ||--o{ Offer : has
    Customer ||--o{ Activity : logs
    Customer ||--o{ CustomerData : has
    Customer ||--o{ VerificationToken : has
    Customer ||--o{ App : owns

    Contract ||--o{ Document : contains
    Offer ||--o{ Signature : has

    Signature ||--o{ SignatureData : contains
    Signature ||--o| SignatureValidation : validates

    App ||--o| Device : "installed on"
    App ||--o| FcmInfo : "push config"

Core Models

Model	Purpose
Customer	End customers
Contract	Signed contracts
Document	Contract documents
Offer	Offers pending signature
Signature	Digital signatures
SignatureData	Signature binary data
SignatureValidation	Signature verification results
CustomerData	Customer personal data
VerificationToken	2FA tokens

Supporting Models

Model	Purpose
User	Admin/operator users
AuditLog	Audit trail
EmailLog	Email delivery logs
SmsLog	SMS delivery logs
Activity	Customer activity log
Setting	System settings
Shortener	URL shortener
ClientErrorLog	Client-side errors
Cert	Certificate storage
BackgroundProcess	Background jobs
BackgroundProcessLog	Background job logs
TimestampToken	RFC 3161 timestamps
Attachment	File attachments
Session	Web sessions
App	Mobile app registrations
Device	Customer devices
FcmInfo	Firebase push notification config

SmsLog Foreign Key Hack

SmsLog.belongsTo(CustomerData, { as: 'customerData', foreignKey: 'customerId', sourceKey: 'customerId' }) — uses customerId as a cross-reference instead of a proper foreign key association.

Database Configuration

Property	Value
ORM	Sequelize (custom @techteamer/sequelize fork v6.30.1)
Primary DB	PostgreSQL 16.6
Also supported	MySQL (utf8mb4), MSSQL (tedious), Oracle 12g
vuer_oss database	vuer_oss
esign_oss database	esign_oss
Test database	vuer_oss_test
Migrations	db/migrate/ using Umzug, timestamp-numbered
Config files	.sequelize-config.js, .sequelizerc, .sequelizercBefore

Key Relationships

User 1--* Room *--1 Customer
Customer 1--* SelfServiceRoom
Room/SelfServiceRoom 1--* Activity
Room/SelfServiceRoom 1--* Flow 1--* Task
Room/SelfServiceRoom 1--* MediaFile *--1 Encryption
Room/SelfServiceRoom 1--* Document *--1 Attachment *--1 Encryption
Customer 1--* CustomerHistory
Customer 1--* CustomerValidation
Customer 1--* CustomerDataChange
Flow *--1 FlowProto
Task *--1 TaskProto
Flow *--1 parentFlow (self-referencing)

Related

• FaceKom — Platform overview
• vuer_oss — Primary database service (55+ models)
• esign — eSign database (27 models)
• security-audit — Database-related security findings
• tech-debt — Database-related tech debt