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