mirror of
https://github.com/andwati/telecom-platform.git
synced 2026-10-18 02:52:53 +00:00
nutcas3
e6450f15ad
- Add subscribers table with IMSI, MSISDN, authentication keys, and eSIM profile fields - Add service_plans table for data/voice/SMS limits and QoS parameters (ARPA, AMBR) - Add sessions table for tracking PDU sessions with QoS and usage metrics - Add usage_records table for billing and charging data - Add users table with role-based access control fields - Add roles, permissions, and role_permissions junction table for RBAC - Add auth
39 lines
1.5 KiB
SQL
39 lines
1.5 KiB
SQL
-- +goose Up
|
|
-- Create subscribers table
|
|
CREATE TABLE IF NOT EXISTS subscribers (
|
|
id SERIAL PRIMARY KEY,
|
|
imsi TEXT NOT NULL UNIQUE,
|
|
msisdn TEXT UNIQUE,
|
|
imei TEXT,
|
|
first_name TEXT NOT NULL,
|
|
last_name TEXT NOT NULL,
|
|
email TEXT UNIQUE,
|
|
organization_id TEXT,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
plan_id INTEGER,
|
|
balance DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
auth_key TEXT NOT NULL,
|
|
opc TEXT NOT NULL,
|
|
serving_plmn_mcc TEXT NOT NULL,
|
|
serving_plmn_mnc TEXT NOT NULL,
|
|
euicc_id TEXT,
|
|
profile_id TEXT,
|
|
profile_status TEXT NOT NULL DEFAULT 'inactive',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Create indexes
|
|
CREATE INDEX IF NOT EXISTS idx_subscribers_imsi ON subscribers(imsi);
|
|
CREATE INDEX IF NOT EXISTS idx_subscribers_msisdn ON subscribers(msisdn);
|
|
CREATE INDEX IF NOT EXISTS idx_subscribers_email ON subscribers(email);
|
|
CREATE INDEX IF NOT EXISTS idx_subscribers_status ON subscribers(status);
|
|
CREATE INDEX IF NOT EXISTS idx_subscribers_organization_id ON subscribers(organization_id);
|
|
CREATE INDEX IF NOT EXISTS idx_subscribers_plan_id ON subscribers(plan_id);
|
|
CREATE INDEX IF NOT EXISTS idx_subscribers_deleted_at ON subscribers(deleted_at);
|
|
|
|
-- +goose Down
|
|
-- Drop subscribers table
|
|
DROP TABLE IF EXISTS subscribers CASCADE;
|