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
38 lines
1.3 KiB
SQL
38 lines
1.3 KiB
SQL
-- +goose Up
|
|
-- Create roles table
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Create permissions table
|
|
CREATE TABLE IF NOT EXISTS permissions (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
resource TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Create role_permissions junction table
|
|
CREATE TABLE IF NOT EXISTS role_permissions (
|
|
role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
|
|
permission_id INTEGER NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (role_id, permission_id)
|
|
);
|
|
|
|
-- Create indexes
|
|
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id);
|
|
CREATE INDEX IF NOT EXISTS idx_role_permissions_permission_id ON role_permissions(permission_id);
|
|
|
|
-- +goose Down
|
|
-- Drop role_permissions, permissions, and roles tables
|
|
DROP TABLE IF EXISTS role_permissions CASCADE;
|
|
DROP TABLE IF EXISTS permissions CASCADE;
|
|
DROP TABLE IF EXISTS roles CASCADE;
|