MAGIK-932: Database Schema & Migration for i18n Fields

[MAGIKBIT]

Story 11 — Database Schema & Migration for i18n Fields

Epic: EPIC-010 — Global Internationalization & Multi-Language Support (GitHub #100)
Priority: P1

User Story

As a developer, I want the database schema updated to support internationalization fields (locale, currency, country code, E.164 phone), so all i18n features have proper backing storage.

Requirements

Schema Changes

name_info table

ALTER TABLE name_info
  ADD COLUMN locale CHAR(2) NOT NULL DEFAULT 'en' AFTER user_type,
  ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'USD' AFTER locale,
  ADD COLUMN country_code CHAR(2) DEFAULT NULL AFTER currency;

currency_rates table (new)

CREATE TABLE currency_rates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  base_currency CHAR(3) NOT NULL DEFAULT 'USD',
  target_currency CHAR(3) NOT NULL,
  rate DECIMAL(12,6) NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_currency_pair (base_currency, target_currency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

address_info table adjustments

ALTER TABLE address_info
  MODIFY COLUMN postal_code VARCHAR(20) DEFAULT NULL,
  ADD COLUMN country_code CHAR(2) DEFAULT 'US' AFTER country;

Phone number migration

-- Add column for E.164 format
ALTER TABLE name_info
  ADD COLUMN phone_e164 VARCHAR(20) DEFAULT NULL AFTER phone;
-- Migrate existing US phones (10-digit) to E.164
UPDATE name_info
  SET phone_e164 = CONCAT('+1', REGEXP_REPLACE(phone, '[^0-9]', ''))
  WHERE phone IS NOT NULL AND phone != '';

platform_settings table (new or extend)

CREATE TABLE IF NOT EXISTS platform_settings (
  setting_key VARCHAR(50) PRIMARY KEY,
  setting_value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO platform_settings (setting_key, setting_value) VALUES
  ('default_locale', 'en'),
  ('supported_locales', 'en,es,ar,am,ti'),
  ('default_currency', 'USD'),
  ('supported_currencies', 'USD,EUR,ETB,ERN,MXN,SAR,AED,GBP');

Seed Data — Currency Rates

INSERT INTO currency_rates (base_currency, target_currency, rate) VALUES
  ('USD', 'EUR', 0.920000),
  ('USD', 'ETB', 56.500000),
  ('USD', 'ERN', 15.000000),
  ('USD', 'MXN', 17.150000),
  ('USD', 'SAR', 3.750000),
  ('USD', 'AED', 3.672500),
  ('USD', 'GBP', 0.790000);

Migration Safety

• All ALTERs use ADD COLUMN ... AFTER to avoid column reordering
• Default values ensure backward compatibility
• Phone migration is non-destructive (new column alongside existing)
• Rollback scripts included for each migration

Acceptance Criteria

#	Criteria	Priority
1	name_info has locale, currency, country_code columns	P0
2	currency_rates table created with seed data	P0
3	address_info.postal_code is VARCHAR	P0
4	Phone E.164 column added and existing data migrated	P1
5	platform_settings table created with defaults	P1
6	All migrations are idempotent (safe to run multiple times)	P0
7	Rollback scripts provided	P2

Files to Create

• sql/migration_i18n_schema.sql — all schema changes
• sql/migration_i18n_seed.sql — seed data (rates, settings)
• sql/rollback_i18n_schema.sql — rollback scripts