Sahib Data Model

Entities schema without translation table

acts, adhkar, user_acts

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

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;
}

Query with Fallback Handling

// Get act with fallback: requested → record default → global default
async function getActWithFallback(
  actId: string,
  requestedLanguage: string,
  globalDefault: string = 'en-US'
) {
  const act = await db
    .select()
    .from(acts)
    .where(eq(acts.id, actId))
    .limit(1);
  
  if (!act[0]) return null;
  
  const recordDefault = act[0].defaultLanguage;
  
  // Try requested language, then record default, then global default
  const languages = [
    requestedLanguage,
    recordDefault,
    globalDefault,
  ].filter((lang, index, arr) => arr.indexOf(lang) === index); // Remove duplicates
  
  const translations = await db
    .select()
    .from(translations)
    .where(and(
      eq(translations.entityType, 'acts'),
      eq(translations.entityId, actId),
      inArray(translations.languageCode, languages)
    ));
  
  // Group by field_name and pick first available language
  const result: Record<string, string> = {};
  const fields = ['title', 'description'];
  
  for (const field of fields) {
    for (const lang of languages) {
      const translation = translations.find(
        t => t.fieldName === field && t.languageCode === lang
      );
      if (translation) {
        result[field] = translation.translatedValue;
        break;
      }
    }
  }
  
  return {
    ...act[0],
    ...result,
  };
}

Optimized Single Query with Fallback

// Single query with COALESCE-like fallback using SQL
async function getActTitleWithFallback(
  actId: string,
  requestedLanguage: string,
  globalDefault: string = 'en-US'
) {
  const act = await db
    .select({ defaultLanguage: acts.defaultLanguage })
    .from(acts)
    .where(eq(acts.id, actId))
    .limit(1);
  
  if (!act[0]) return null;
  
  const recordDefault = act[0].defaultLanguage;
  
  // Build fallback priority: requested → record default → global default
  const result = await db
    .select({
      title: sql<string>`COALESCE(
        MAX(CASE WHEN ${translations.languageCode} = ${requestedLanguage} THEN ${translations.translatedValue} END),
        MAX(CASE WHEN ${translations.languageCode} = ${recordDefault} THEN ${translations.translatedValue} END),
        MAX(CASE WHEN ${translations.languageCode} = ${globalDefault} THEN ${translations.translatedValue} END)
      )`.as('title'),
    })
    .from(acts)
    .leftJoin(translations, and(
      eq(translations.entityType, 'acts'),
      eq(translations.entityId, acts.id),
      eq(translations.fieldName, 'title'),
      inArray(translations.languageCode, [requestedLanguage, recordDefault, globalDefault])
    ))
    .where(eq(acts.id, actId))
    .groupBy(acts.id);
  
  return result[0]?.title || null;
}

Get Multiple Fields with Fallback

// Get multiple translatable fields with fallback
async function getActTranslations(
  actId: string,
  requestedLanguage: string,
  fields: string[] = ['title', 'description'],
  globalDefault: string = 'en-US'
) {
  const act = await db
    .select({ defaultLanguage: acts.defaultLanguage })
    .from(acts)
    .where(eq(acts.id, actId))
    .limit(1);
  
  if (!act[0]) return null;
  
  const recordDefault = act[0].defaultLanguage;
  const languages = [requestedLanguage, recordDefault, globalDefault];
  
  // Get all translations for this act and requested fields
  const allTranslations = await db
    .select()
    .from(translations)
    .where(and(
      eq(translations.entityType, 'acts'),
      eq(translations.entityId, actId),
      inArray(translations.fieldName, fields),
      inArray(translations.languageCode, languages)
    ));
  
  // Build result with fallback logic
  const result: Record<string, string> = {};
  
  for (const field of fields) {
    for (const lang of languages) {
      const translation = allTranslations.find(
        t => t.fieldName === field && t.languageCode === lang
      );
      if (translation) {
        result[field] = translation.translatedValue;
        break;
      }
    }
  }
  
  return result;
}

Helper Function: Reusable Translation Getter

// 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<string | null> {
  // 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;
  }
  
  // Fallback to global default
  if (globalDefaultLanguage !== requestedLanguage && globalDefaultLanguage !== recordDefaultLanguage) {
    translation = await db
      .select()
      .from(translations)
      .where(and(
        eq(translations.entityType, entityType),
        eq(translations.entityId, entityId),
        eq(translations.fieldName, fieldName),
        eq(translations.languageCode, globalDefaultLanguage)
      ))
      .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

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
}

// 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
]

// Example: Schedule on January 1st every year
schedule_yearly: {
  schedule_id: 3,
  month: 1,  // January
  day: 1
}

schedule_runs {
  schedule_id: integer
  scheduled_for: integer      // When it was supposed to run (timestamp)
  executed_at: integer        // When it actually ran (null if not yet)
  status: 'pending' | 'success' | 'failed' | 'skipped' | 'cancelled'
  error_message: string       // If failed
}

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<ScheduleExecution[]>

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);
  }
}

// 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