SECTION 14.0 : ENTITY RELATIONSHIP DIAGRAM
Relational Schema Visualization
An interactive, high-fidelity mapping of the PostgreSQL schema tables and indexes. Hover over nodes to highlight path linkages. Click any table node to focus its detail manifest below.
Click nodes to scroll to fields; Hover to trace foreign keys. PK FK
users
idUUID
emailVARCHAR
password_hashVARCHAR
full_nameVARCHAR
+8 columns
Identityschema
webauthn_credentials
idUUID
user_idUUID
credential_idTEXT
public_keyTEXT
+2 columns
Identityschema
files
idUUID
parent_idUUID
owner_idUUID
nameVARCHAR
+11 columns
Assetsschema
file_versions
idUUID
file_idUUID
version_numberINTEGER
size_bytesBIGINT
+3 columns
Assetsschema
file_shares
idUUID
file_idUUID
user_idUUID
roleVARCHAR
+2 columns
Assetsschema
favorites
user_idUUID
file_idUUID
created_atTIMESTAMPTZ
Assetsschema
governance_requests
idUUID
typeVARCHAR
titleVARCHAR
descriptionTEXT
+8 columns
Governanceschema
audit_logs
idUUID
user_idUUID
actionVARCHAR
target_resourceVARCHAR
+2 columns
Governanceschema
system_config
keyVARCHAR
valueTEXT
updated_atTIMESTAMPTZ
Systemschema
Field Specifications & Metadata
users
Identity Schema Model
12 columns defined
Primary user credentials and identity metadata. Manages user access clearances (Terbuka, Terhad, Sulit, Rahsia) and organization hierarchies.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| VARCHAR(320) | NOT NULL | - | Unique email username. | |
| password_hash | VARCHAR(512) | NOT NULL | - | Argon2id password hash. |
| full_name | VARCHAR(255) | NOT NULL | - | User's full display name. |
| role | VARCHAR(16) | NOT NULL | 'staff' | Access tier clearance level: chief, director, officer, staff. |
| active | BOOLEAN | NOT NULL | TRUE | Boolean deactivation toggle for employee lifecycle management. |
| storage_quota_bytes | BIGINT | NULLABLE | 107374182400 (100GB) | Allocated user filesystem quota limit in bytes. |
| avatar_data | TEXT | NULLABLE | - | Base64 avatar image string. |
| department | VARCHAR(255) | NULLABLE | - | Assigned corporate division. |
| FKsupervisor_id | UUID | NULLABLE | - | Self-referencing foreign key mapping supervisors. ↳ References: users.id |
| notification_prefs | JSONB | NULLABLE | '{}' | Client notifications JSON payload preferences. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp of user account creation. |
webauthn_credentials
Identity Schema Model
6 columns defined
Cryptographic credentials stored for WebAuthn/Passkey authentication. Prevents reliance on static password hashes.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| FKuser_id | UUID | NOT NULL | - | Foreign key linking credential to user owner. ↳ References: users.id |
| credential_id | TEXT | NOT NULL | - | Public key credential lookup ID. |
| public_key | TEXT | NOT NULL | - | COSE public key encoding format. |
| sign_count | BIGINT | NOT NULL | 0 | Credential validation tracking counter to prevent reuse attacks. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp credential was registered. |
files
Assets Schema Model
15 columns defined
Hierarchical filesystem storage nodes, mapping directory tree layers recursively. Integrates document locks and security classification rules.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| FKparent_id | UUID | NULLABLE | - | Recursive parent directory foreign key. NULL if root. ↳ References: files.id |
| FKowner_id | UUID | NOT NULL | - | Asset creator/owner. Restricts transfer permissions. ↳ References: users.id |
| name | VARCHAR(512) | NOT NULL | - | Name of folder or file asset. |
| is_folder | BOOLEAN | NOT NULL | FALSE | Flag declaring folder or binary block asset. |
| size_bytes | BIGINT | NOT NULL | 0 | Size of file in bytes. Set to 0 if directory. |
| mime_type | VARCHAR(255) | NULLABLE | - | Standard Internet Media Type. |
| classification | VARCHAR(16) | NOT NULL | 'TERBUKA' | Clearance requirement tier: RAHSIA, SULIT, TERHAD, TERBUKA. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Created date timestamp. |
| updated_at | TIMESTAMPTZ | NOT NULL | NOW() | Last modified date timestamp. |
| deleted_at | TIMESTAMPTZ | NULLABLE | - | Soft-delete timestamp. Enables file trashing restore options. |
| FKlocked_by | UUID | NULLABLE | - | Foreign key of user holding lock. ↳ References: users.id |
| locked_at | TIMESTAMPTZ | NULLABLE | - | Timestamp when file lock was initiated. |
| lock_reason | TEXT | NULLABLE | - | Governance explanation string justifying locked asset status. |
| search_vector | tsvector | NULLABLE | - | Full-text indexing search vector for low-latency lookups. |
file_versions
Assets Schema Model
7 columns defined
Tracks history version instances of files. Permits rolling back modification changes securely.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| FKfile_id | UUID | NOT NULL | - | Foreign key linking version to base files. ↳ References: files.id |
| version_number | INTEGER | NOT NULL | - | Monotonically increasing version counter. |
| size_bytes | BIGINT | NOT NULL | 0 | Version payload storage size in bytes. |
| storage_path | VARCHAR(1024) | NOT NULL | - | Absolute disk path to the encrypted file block. |
| FKuploaded_by | UUID | NULLABLE | - | User uploading version instance. ↳ References: users.id |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Date version uploaded. |
file_shares
Assets Schema Model
6 columns defined
Many-to-many lookup relationship defining targeted document sharing records with editor/viewer permission tiers.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| FKfile_id | UUID | NOT NULL | - | Shared file foreign key. ↳ References: files.id |
| FKuser_id | UUID | NOT NULL | - | Recipient user foreign key. ↳ References: users.id |
| role | VARCHAR(16) | NOT NULL | 'viewer' | Granted permission level: owner, editor, viewer. |
| FKshared_by | UUID | NOT NULL | - | Granter user foreign key. ↳ References: users.id |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp share was established. |
favorites
Assets Schema Model
3 columns defined
Join table mapping starred files for users. Optimizes navigation speed lists for frequently visited assets.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKFKuser_id | UUID | NOT NULL | - | User identifier. ↳ References: users.id |
| PKFKfile_id | UUID | NOT NULL | - | Starred file identifier. ↳ References: files.id |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Date/time starred. |
governance_requests
Governance Schema Model
12 columns defined
Workflow approval requests queue. Restricts dangerous actions (classification downgrades, manual lock releases) without senior authorization.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| type | VARCHAR(32) | NOT NULL | - | Request actions: FILE_LOCK, FILE_UNLOCK, CLASSIFICATION_UPGRADE, CLASSIFICATION_DOWNGRADE. |
| title | VARCHAR(255) | NOT NULL | - | Brief title/summary of action. |
| description | TEXT | NULLABLE | - | Context justifying action. |
| status | VARCHAR(16) | NOT NULL | 'PENDING' | State: PENDING, APPROVED, REJECTED. |
| FKrequested_by | UUID | NOT NULL | - | Initiator employee id. ↳ References: users.id |
| FKreviewed_by | UUID | NULLABLE | - | Authorizing supervisor/director id. ↳ References: users.id |
| FKtarget_file_id | UUID | NULLABLE | - | Target asset identifier. ↳ References: files.id |
| metadata | JSONB | NULLABLE | - | Extra payload data (e.g. target classification string). |
| review_note | TEXT | NULLABLE | - | Explanation notes submitted during review approval/rejection. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Request date timestamp. |
| updated_at | TIMESTAMPTZ | NOT NULL | NOW() | Last state modified timestamp. |
audit_logs
Governance Schema Model
6 columns defined
Append-only database journal tracking security actions, compliance upgrades, and endpoint usage patterns. Modifying entries is programmatically blocked.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| FKuser_id | UUID | NULLABLE | - | Responsible user id. NULL if system process. ↳ References: users.id |
| action | VARCHAR(64) | NOT NULL | - | Action identifier keyword (e.g. AUTH_LOGIN, FILE_UPLOAD). |
| target_resource | VARCHAR(512) | NULLABLE | - | Details of affected asset/resource path. |
| ip_address | VARCHAR(45) | NULLABLE | - | Client network address. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Log timestamp. |
system_config
System Schema Model
3 columns defined
Global system configuration keys and overrides. Admin changes values to control global limits without restarts.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKkey | VARCHAR(128) | NULLABLE | - | Setting lookup key. |
| value | TEXT | NOT NULL | - | Configuration value settings string. |
| updated_at | TIMESTAMPTZ | NOT NULL | NOW() | Last configuration updated timestamp. |
permissions
System Schema Model
3 columns defined
System-wide granular permission keys. Used to enforce access controls on specific actions and assets.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| key | VARCHAR(64) | NOT NULL | - | Unique permission key string (e.g. 'files:read', 'users:manage'). |
| description | TEXT | NOT NULL | - | Human-readable explanation of the action this permission authorizes. |
role_groups
System Schema Model
6 columns defined
Group mappings (custom roles) configured by administrators. Serves as container collections of permission rules.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| name | VARCHAR(128) | NOT NULL | - | Name of the custom role group. |
| description | TEXT | NOT NULL | '' | Description detailing group role scope. |
| created_by | VARCHAR(128) | NOT NULL | - | Account identifying the creator. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp when the role group was created. |
| updated_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp of last update. |
role_group_permissions
System Schema Model
2 columns defined
Junction table mapping permission grants to role groups.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKFKrole_group_id | UUID | NOT NULL | - | Reference to role group. ↳ References: role_groups.id |
| PKFKpermission_id | UUID | NOT NULL | - | Reference to permission. ↳ References: permissions.id |
user_role_groups
System Schema Model
2 columns defined
Junction table mapping users to role groups.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKFKuser_id | UUID | NOT NULL | - | Reference to user. ↳ References: users.id |
| PKFKrole_group_id | UUID | NOT NULL | - | Reference to role group. ↳ References: role_groups.id |
user_permissions
System Schema Model
2 columns defined
Junction table for direct user permission grants, bypassing group configuration.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKFKuser_id | UUID | NOT NULL | - | Reference to user. ↳ References: users.id |
| PKFKpermission_id | UUID | NOT NULL | - | Reference to permission. ↳ References: permissions.id |
custom_roles
System Schema Model
3 columns defined
Supported system clearance roles mapping to numeric clearance levels (e.g. staff, officer, director, chief).
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKrole_key | VARCHAR(32) | NULLABLE | - | Clearance key identifier (e.g. 'chief', 'director', 'officer', 'staff'). |
| label | VARCHAR(64) | NOT NULL | - | Human-readable name of the role. |
| level | SMALLINT | NOT NULL | 1 | Clearance level integer (1=Staff, 2=Officer, 3=Director, 4=Chief). |
role_implicit_permissions
System Schema Model
2 columns defined
Implicit permissions automatically granted to specific roles, such as admin panel access or audit logging logs.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKFKrole_key | VARCHAR(32) | NOT NULL | - | Reference to custom role. ↳ References: custom_roles.role_key |
| PKFKpermission_id | UUID | NOT NULL | - | Reference to permission. ↳ References: permissions.id |
magic_links
Identity Schema Model
6 columns defined
Temporary security tokens generated for user passwordless authentication.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| FKuser_id | UUID | NOT NULL | - | Reference to recipient user. ↳ References: users.id |
| token | VARCHAR(128) | NOT NULL | - | Secure random magic login token. |
| expires_at | TIMESTAMPTZ | NOT NULL | - | Link expiration timestamp. |
| used | BOOLEAN | NOT NULL | FALSE | Flag indicating if token has been verified. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp token was issued. |
password_resets
Identity Schema Model
6 columns defined
Temporary secure tokens for credential reset workflow sequences.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| FKuser_id | UUID | NOT NULL | - | Reference to user. ↳ References: users.id |
| token | VARCHAR(128) | NOT NULL | - | Secure random reset token. |
| expires_at | TIMESTAMPTZ | NOT NULL | - | Token expiration timestamp. |
| used | BOOLEAN | NOT NULL | FALSE | Flag indicating if token has been verified. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp token was issued. |
user_sessions
Identity Schema Model
7 columns defined
Stores active login sessions and device/IP context details for token validation.
| Column Name | SQL Data Type | Constraints | Default Value | Utility Description |
|---|---|---|---|---|
| PKid | UUID | NULLABLE | uuid_generate_v4() | Primary key identifier. |
| FKuser_id | UUID | NOT NULL | - | Reference to user. ↳ References: users.id |
| token_prefix | VARCHAR(16) | NOT NULL | - | Session tracking prefix identifier. |
| device | VARCHAR(255) | NOT NULL | '' | Client browser user-agent info. |
| ip | VARCHAR(45) | NOT NULL | '' | Client IP address used at creation. |
| created_at | TIMESTAMPTZ | NOT NULL | NOW() | Timestamp when session started. |
| last_seen_at | TIMESTAMPTZ | NOT NULL | NOW() | Last observed activity timestamp. |