# Sahib Data Model Saheb Mobile App - Database Schema --- ## 1. Entity Schema (Main Tables - No Translation Table) **All main tables with their columns. Translation relationships shown in diagram 2.**
--- ## 2. Translation Schema (Translation Relationships Only) **Shows how the global `translations` table relates to content tables.**
--- ## Translation Strategy - **Location tables** (`regions`, `countries`, `subregions`): Use JSON `translations` column - **Content tables** (`acts`, `sermons`, `resources`, `qas`, `adhkars`, `sermon_paragraphs`): Use global `translations` table - **Fallback chain**: Requested language → Record's `default_language` → Global default (`en-US`) --- ## Drizzle Query Examples ### Basic Translation Query ```typescript import { db } from '@/src/db'; import { acts, translations } from '@/src/db/schemas'; import { eq, and, sql, inArray } from 'drizzle-orm'; // Get act title in specific language async function getActTitle(actId: string, languageCode: string) { const result = await db .select({ id: acts.id, title: translations.translatedValue, }) .from(acts) .innerJoin(translations, and( eq(translations.entityType, 'acts'), eq(translations.entityId, acts.id), eq(translations.fieldName, 'title'), eq(translations.languageCode, languageCode) )) .where(eq(acts.id, actId)) .limit(1); return result[0]?.title || null; } ``` ### Helper Function: Reusable Translation Getter ```typescript // Generic helper for any entity type async function getTranslatedValue( entityType: 'acts' | 'sermons' | 'resources' | 'qas' | 'adhkars' | 'sermon_paragraphs', entityId: string, fieldName: string, requestedLanguage: string, recordDefaultLanguage: string, globalDefaultLanguage: string = 'en-US' ): Promise { // Try requested language first let translation = await db .select() .from(translations) .where(and( eq(translations.entityType, entityType), eq(translations.entityId, entityId), eq(translations.fieldName, fieldName), eq(translations.languageCode, requestedLanguage) )) .limit(1); if (translation[0]) return translation[0].translatedValue; // Fallback to record's default language if (recordDefaultLanguage && recordDefaultLanguage !== requestedLanguage) { translation = await db .select() .from(translations) .where(and( eq(translations.entityType, entityType), eq(translations.entityId, entityId), eq(translations.fieldName, fieldName), eq(translations.languageCode, recordDefaultLanguage) )) .limit(1); if (translation[0]) return translation[0].translatedValue; } return null; } // Usage example const act = await db.select().from(acts).where(eq(acts.id, 'act-1')).limit(1); const title = await getTranslatedValue( 'acts', 'act-1', 'title', 'fr-FR', // User's current language act[0].defaultLanguage, // Record's default (e.g., 'ar-MA') 'en-US' // Global default ); ``` --- ## Design Notes - **Pure Translation Approach**: All languages stored in `translations` table (including English) - **Per-Record Fallback**: Each record has `default_language` for fallback when requested translation missing - **Location Tables**: Use JSON `translations` column (prepopulated data) - **Content Tables**: Use global `translations` table (dynamic content) --- ## 3. Scheduling & Event Bus Schema
## Scheduling Design Pattern Explanation
The scheduling system uses a polymorphic, flexible pattern that supports multiple recurrence types and targets.
### Core Design: Polymorphic Scheduling
The schedules table is the central entity that can schedule any type of target:
```typescript schedules { target_type: 'act' | 'notification' | 'reminder' | 'other' // What to schedule target_id: integer // ID of the target recurrence_type: 'daily' | 'weekly' | 'monthly' | 'yearly' // How often interval: integer // Every N periods start_at: integer // When to start timezone: string // Timezone context is_active: boolean // Enable/disable } ```
```typescript // Example: Schedule on Monday, Wednesday, Friday schedule_weekdays: [ { schedule_id: 1, weekday: 1 }, // Monday { schedule_id: 1, weekday: 3 }, // Wednesday { schedule_id: 1, weekday: 5 }, // Friday ] ```
## Facade Method Signature ```typescript interface ScheduleExecution { schedule: Schedule; executionTime: number; // Unix timestamp in seconds targetType: 'act' | 'notification' | 'reminder' | 'other'; targetId: number; } /** * Get all schedules that need to be executed within a time range * * @param rangeStart - Start of the time range (Unix timestamp in seconds) * @param rangeEnd - End of the time range (Unix timestamp in seconds) * @param timezone - Optional timezone for calculations (defaults to UTC) * @returns Array of schedule execution records with calculated execution times */ async function getSchedulesInRange( rangeStart: number, rangeEnd: number, timezone: string = 'UTC' ): Promise ``` #### Example : Get schedules for a specific time window (e.g., next 15 minutes) ```typescript const now = Math.floor(Date.now() / 1000); const in15Minutes = now + (15 * 60); // 15 minutes later const schedules = await getSchedulesInRange(now, in15Minutes); // Execute immediately if any schedules found if (schedules.length > 0) { console.log(`Executing ${schedules.length} schedules now...`); for (const execution of schedules) { await executeSchedule(execution); } } ``` #### Example : Database structure - Prayers with schedule\_id Here's how prayers are linked to schedules: ```typescript // Schedule for Isha prayer (daily at 8:15 PM) const ishaSchedule = { id: 5, target_type: 'reminder', // Prayers use 'reminder' type target_id: 1, // ID of the prayer reminder/notification recurrence_type: 'daily', interval: 1, start_at: 1234567890, timezone: 'Africa/Casablanca', is_active: true, }; // Schedule times: Isha at 8:15 PM (73500 seconds = 20:15:00) const ishaTimes = [ { schedule_id: 5, time_seconds: 73500 }, ]; // Act with schedule (daily Quran reading at 8:00 PM) const quranActSchedule = { id: 8, target_type: 'act', target_id: 15, // Act ID recurrence_type: 'daily', interval: 1, start_at: 1234567890, timezone: 'Africa/Casablanca', is_active: true, }; const quranTimes = [ { schedule_id: 8, time_seconds: 72000 }, // 20:00:00 ]; // Usage: Get schedules for next hour (7:30 PM - 8:30 PM) const now = Math.floor(Date.now() / 1000); const oneHourLater = now + 3600; const schedules = await getSchedulesInRange(now, oneHourLater); // Results: // - Isha prayer (schedule_id: 5) at 8:15 PM // - Quran act (act_id: 15) at 8:00 PM ```