نموذج البيانات
PostgreSQL 16 database schema with 45+ SQLModel entities.
Entity Relationship نظرة عامة
Core Models
Organization
| Column | Type | Description |
|---|---|---|
| id | int (PK) | Organization ID |
| name | varchar | Organization name |
| subscription_tier | varchar | essentials/professional/enterprise/sovereign |
| deployment_model | varchar | shared_saas/private_tenant/on_premise |
| active_modules | json | List of enabled modules |
| max_users | int | Tier user limit |
| max_storage_gb | int | Tier storage limit |
| ai_tokens_monthly | int | Tier AI token limit |
| license_key | varchar | RS256 JWT license |
| license_expiry | varchar | License expiration |
| is_active | bool | Active status |
| retention_days | int | Data retention period |
User (via Keycloak)
Managed through Keycloak OIDC. AIIA stores:
| Column | Type | Description |
|---|---|---|
| id | int (PK) | User ID |
| username | varchar | Keycloak username |
| varchar | Email address | |
| org_id | int (FK) | Organization |
| role_id | int (FK) | Assigned role |
| is_active | bool | Active status |
Engagement
| Column | Type | Description |
|---|---|---|
| id | int (PK) | Engagement ID |
| org_id | int (FK) | Organization |
| title | varchar | Engagement title |
| type | varchar | Compliance/Operational/Financial/IT/Special |
| status | varchar | draft/planned/fieldwork/reporting/review/final/closed |
| lead_auditor_id | int (FK) | Lead auditor |
| start_date / end_date | date | Timeline |
| methodology_id | int (FK) | Selected methodology |
Workpaper
| Column | Type | Description |
|---|---|---|
| id | int (PK) | Workpaper ID |
| org_id | int (FK) | Organization |
| engagement_id | int (FK) | Parent engagement |
| title | varchar | Workpaper title |
| content | text | Markdown content |
| status | varchar | draft/in_review/reviewed |
| preparer_id | int | Preparer user |
| reviewer_id | int | Assigned reviewer |
| version | int | Current version number |
Finding
| Column | Type | Description |
|---|---|---|
| id | int (PK) | Finding ID |
| org_id | int (FK) | Organization |
| engagement_id | int (FK) | Parent engagement |
| title | varchar | Finding title |
| condition | text | What is happening |
| criteria | text | What should happen |
| cause | text | Why the gap exists |
| effect | text | Risk/impact |
| recommendation | text | How to fix |
| severity | varchar | critical/high/medium/low |
| status | varchar | draft/management_response/final/closed |
Evidence
| Column | Type | Description |
|---|---|---|
| id | int (PK) | Evidence ID |
| org_id | int (FK) | Organization |
| filename | varchar | Original filename |
| file_hash | varchar | SHA-256 hash |
| file_size | int | Size in bytes |
| mime_type | varchar | File type |
| storage_path | varchar | MinIO object path |
| scan_status | varchar | clean/infected/pending |
| uploader_id | int | Who uploaded |
| uploaded_at | timestamp | When uploaded |
KRI / Monitoring
| Column | Type | Description |
|---|---|---|
| id | int (PK) | KRI ID |
| org_id | int (FK) | Organization |
| name | varchar | KRI name |
| data_source | varchar | Source table/connector |
| calculation | text | SQL/formula |
| schedule | varchar | Cron expression |
| green_max | float | Green zone upper bound |
| amber_max | float | Amber zone upper bound |
| current_value | float | Latest calculated value |
| status | varchar | green/amber/red |
AuditLogEvent
| Column | Type | Description |
|---|---|---|
| id | int (PK) | Event ID |
| org_id | int (FK) | Organization |
| user_id | int | Acting user |
| username | varchar | Username |
| action | varchar | CREATE/UPDATE/DELETE/EXPORT/AI_ACTION |
| resource_type | varchar | Entity type |
| resource_id | varchar | Entity ID |
| details | json | Action details |
| ip_address | varchar | Client IP |
| created_at | timestamp | Event time |
Multi-Tenancy
All tenant-scoped tables include org_id with RLS policies:
- 19 RLS policies enforce data isolation at database level
- Session variable
app.current_org_idset per request - OrgScopedDB dependency injects the variable automatically
Full Model List
45+ models across:
| Category | Models |
|---|---|
| Core | Organization, User, Role, Permission, AuditLogEvent |
| Engagement | Engagement, Phase, Milestone, ReviewPoint, Signoff |
| Fieldwork | Workpaper, TestProcedure, Evidence, Sampling |
| Findings | Finding, FindingVersion, ActionPlan, RemediationEvidence |
| Planning | AnnualPlan, PlanItem, PlanVersion |
| Universe | AuditUniverseItem, Risk, Control, RiskControlMapping |
| Monitoring | KRI, KRIThreshold, KRIDataSource, MonitoringTest, Alert, Exception |
| Reporting | Report, ReportVersion, ReportTemplate |
| PBC | PBCRequest, PBCRequestItem |
| QA | QAReview, QATemplate, QAChecklistItem |
| Compliance | ComplianceFramework, NCAControl, PDPLRequirement |
| AI | AIModelConfig, AIPrompt, AIPromptVersion, AIRequest, AIResult |
| Integration | IntegrationConnector, Webhook, DataRefresh, APIKey |
| Billing | BillingAccount, UsageRecord, Invoice, TenantInfrastructure |
| System | Notification, NotificationPreference, SSO, Escalation |