37 lines
1.6 KiB
SQL
37 lines
1.6 KiB
SQL
-- 025: Distributed deployment configuration tables
|
|
-- Supports multi-region, horizontal scaling via Citus distribution keys
|
|
|
|
-- Region configuration for multi-region deployment
|
|
CREATE TABLE IF NOT EXISTS regions (
|
|
id VARCHAR(20) PRIMARY KEY, -- e.g. 'us-east', 'ap-southeast', 'hk'
|
|
name VARCHAR(100) NOT NULL,
|
|
endpoint VARCHAR(500),
|
|
role VARCHAR(20) NOT NULL DEFAULT 'secondary' CHECK (role IN ('primary', 'secondary', 'regulatory')),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Insert default regions per deployment guide
|
|
INSERT INTO regions (id, name, role) VALUES
|
|
('us-east', 'AWS US East (Primary)', 'primary'),
|
|
('ap-southeast', 'AWS Singapore (APAC)', 'secondary'),
|
|
('hk', 'Hong Kong (Regulatory)', 'regulatory')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
-- Distributed table distribution (Citus)
|
|
-- In production with Citus, these would distribute the high-volume tables:
|
|
-- SELECT create_distributed_table('transactions', 'user_id');
|
|
-- SELECT create_distributed_table('orders', 'user_id');
|
|
-- SELECT create_distributed_table('trades', 'coupon_id');
|
|
-- SELECT create_distributed_table('audit_logs', 'actor_id');
|
|
-- SELECT create_distributed_table('outbox', 'aggregate_id');
|
|
-- SELECT create_distributed_table('processed_events', 'event_id');
|
|
--
|
|
-- Reference tables (small, replicated to all nodes):
|
|
-- SELECT create_reference_table('regions');
|
|
-- SELECT create_reference_table('issuers');
|
|
-- SELECT create_reference_table('coupons');
|
|
--
|
|
-- Note: In dev environment (single-node), these are regular tables.
|
|
-- Citus commands are only run in production deployment scripts.
|