mirror of
https://github.com/andwati/telecom-platform.git
synced 2026-10-18 11:02:54 +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
41 lines
1.4 KiB
SQL
41 lines
1.4 KiB
SQL
-- +goose Up
|
|
-- Create auth_sessions table
|
|
CREATE TABLE IF NOT EXISTS auth_sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token TEXT NOT NULL,
|
|
refresh_token TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
ip_address TEXT,
|
|
user_agent TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Create api_keys table
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
key TEXT NOT NULL UNIQUE,
|
|
secret TEXT NOT NULL,
|
|
permissions JSONB,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
expires_at TIMESTAMPTZ,
|
|
last_used TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Create indexes
|
|
CREATE INDEX IF NOT EXISTS idx_auth_sessions_user_id ON auth_sessions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_sessions_token ON auth_sessions(token);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_sessions_expires_at ON auth_sessions(expires_at);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_key ON api_keys(key);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_is_active ON api_keys(is_active);
|
|
|
|
-- +goose Down
|
|
-- Drop auth_sessions and api_keys tables
|
|
DROP TABLE IF EXISTS auth_sessions CASCADE;
|
|
DROP TABLE IF EXISTS api_keys CASCADE;
|