KArtSell.Aegis/docs/contracts/data/VS-01_DATA_CONTRACT.md

VS-01: Identity and Roles Data Contract

Slice: VS-01 (ManageIdentityAndRoles)
Status: 📋 SPECIFICATION
Version: 1.0
Created: 2026-08-04

---

Schema (3NF Write Model)

identity.users (User Accounts)

Purpose: Immutable user record (append-only, PIT envelope)

CREATE TABLE identity.users (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Business Keys (immutable)
    email VARCHAR(255) NOT NULL UNIQUE,
    email_hash VARCHAR(64) NOT NULL UNIQUE,  -- SHA-256 of email
    
    -- Authentication (write-once)
    password_hash VARCHAR(255) NOT NULL,  -- bcrypt, never changed after creation
    
    -- State
    status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'inactive', 'suspended')),
    
    -- Temporal (PIT)
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Revision Tracking
    revision INT NOT NULL DEFAULT 1,
    content_hash VARCHAR(64) NOT NULL,  -- SHA-256 of (email, status, updated_at)
    
    -- Audit
    created_by_user_id UUID REFERENCES identity.users(id),
    correlation_id VARCHAR(36) NOT NULL,
    
    -- Indexing
    CONSTRAINT email_lowercase CHECK (email = LOWER(email)),
    CONSTRAINT valid_email CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
);

CREATE INDEX idx_users_email ON identity.users(email);
CREATE INDEX idx_users_status ON identity.users(status);
CREATE INDEX idx_users_published_at ON identity.users(published_at);
CREATE INDEX idx_users_created_by ON identity.users(created_by_user_id);

Constraints:

• ✅ email UNIQUE: Only one account per email per environment
• ✅ status IN ('active', 'inactive', 'suspended'): Enum validation
• ✅ published_at ≤ CURRENT_TIMESTAMP: Never future-dated
• ✅ created_at ≤ updated_at: Temporal order

PIT (Point-in-Time) Query:

SELECT * FROM identity.users
WHERE published_at <= @cutoff
  AND status = 'active'
ORDER BY created_at DESC;

---

identity.roles (Role Definitions)

Purpose: Immutable, predefined roles (reference data)

CREATE TABLE identity.roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO identity.roles (name, description) VALUES
    ('Admin', 'Full system access'),
    ('Analyst', 'Read-only analysis'),
    ('Trader', 'Execute trades'),
    ('Viewer', 'Dashboard read-only');

-- Prevent deletion (immutable reference data)
CREATE TRIGGER prevent_role_deletion
BEFORE DELETE ON identity.roles
FOR EACH ROW
EXECUTE FUNCTION raise_immutability_error();

Constraints:

• ✅ name UNIQUE: One role per name
• ✅ Immutable: No INSERT/UPDATE/DELETE after initial load
• ✅ Predefined: Only 4 roles (Admin, Analyst, Trader, Viewer)

---

identity.user_roles (User-Role Assignment)

Purpose: Many-to-many junction table (append-only)

CREATE TABLE identity.user_roles (
    id BIGSERIAL PRIMARY KEY,
    
    -- Foreign Keys
    user_id UUID NOT NULL REFERENCES identity.users(id),
    role_id INT NOT NULL REFERENCES identity.roles(id),
    
    -- Temporal
    assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    removed_at TIMESTAMP,  -- NULL = still assigned, NOT NULL = removed
    
    -- Audit
    assigned_by_user_id UUID REFERENCES identity.users(id),
    correlation_id VARCHAR(36) NOT NULL,
    
    -- Versioning (for CDC)
    revision INT NOT NULL DEFAULT 1,
    
    -- Constraints
    CONSTRAINT active_assignment CHECK (assigned_at <= published_at),
    CONSTRAINT valid_removal CHECK (removed_at IS NULL OR removed_at >= assigned_at),
    CONSTRAINT unique_active_role UNIQUE (user_id, role_id) WHERE removed_at IS NULL
);

CREATE INDEX idx_user_roles_user ON identity.user_roles(user_id);
CREATE INDEX idx_user_roles_role ON identity.user_roles(role_id);
CREATE INDEX idx_user_roles_active ON identity.user_roles(user_id, removed_at);
CREATE INDEX idx_user_roles_published ON identity.user_roles(published_at);

Constraints:

• ✅ UNIQUE (user_id, role_id) WHERE removed_at IS NULL: No duplicate active roles
• ✅ assigned_at ≤ published_at: Temporal ordering
• ✅ removed_at IS NULL: Active assignment tracking

PIT Query (Get current roles for user):

SELECT ur.user_id, r.name AS role
FROM identity.user_roles ur
JOIN identity.roles r ON ur.role_id = r.id
WHERE ur.user_id = @userId
  AND ur.published_at <= @cutoff
  AND ur.removed_at IS NULL;

---

identity.user_permissions (Permission Grant)

Purpose: Fine-grained permission model (append-only)

CREATE TABLE identity.user_permissions (
    id BIGSERIAL PRIMARY KEY,
    
    -- Foreign Keys
    user_id UUID NOT NULL REFERENCES identity.users(id),
    
    -- Permission (domain-scoped)
    resource VARCHAR(50) NOT NULL,  -- e.g., 'users', 'portfolios', 'trades'
    action VARCHAR(20) NOT NULL,    -- 'read', 'write', 'approve', 'execute'
    
    -- Temporal
    granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at TIMESTAMP,  -- NULL = active, NOT NULL = revoked
    
    -- Audit
    granted_by_user_id UUID REFERENCES identity.users(id),
    correlation_id VARCHAR(36) NOT NULL,
    
    -- Constraints
    CONSTRAINT valid_resource CHECK (resource IN ('users', 'portfolios', 'trades', 'models', 'signals')),
    CONSTRAINT valid_action CHECK (action IN ('read', 'write', 'approve', 'execute')),
    CONSTRAINT unique_active_permission UNIQUE (user_id, resource, action) WHERE revoked_at IS NULL
);

CREATE INDEX idx_permissions_user ON identity.user_permissions(user_id);
CREATE INDEX idx_permissions_resource ON identity.user_permissions(resource, action);

---

Data Integrity Rules

Rule 1: Email Immutability

Constraint: email CANNOT be updated after creation
Verification:

-- Test: Email update should fail
UPDATE identity.users SET email = 'newemail@example.com' 
WHERE id = @userId;
-- Expected: CONSTRAINT VIOLATION (or trigger prevents update)

Rule 2: Password Hash Never Logged

Constraint: password_hash column exists but NEVER appears in SELECT without WHERE
Verification:

-- Bad (never do this):
SELECT * FROM identity.users;  -- ❌ Exposes password_hash

-- Good (always explicit):
SELECT id, email, status FROM identity.users;  -- ✅ No password

Rule 3: PIT (Point-in-Time) Queries Must Include Cutoff

Constraint: All reads include WHERE published_at <= @cutoff
Verification:

-- Correct:
SELECT * FROM identity.users WHERE published_at <= @cutoff AND status = 'active';

-- Wrong (time-machine unsafe):
SELECT * FROM identity.users WHERE status = 'active';  -- ❌ No cutoff

Rule 4: No Direct Email Mutations

Constraint: Email cannot be part of UPDATE statement
Verification (trigger):

CREATE TRIGGER prevent_email_update
BEFORE UPDATE ON identity.users
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email)
EXECUTE FUNCTION raise_immutability_error('email');

Rule 5: Role Removal via Soft Delete

Constraint: Set removed_at timestamp instead of DELETE
Verification:

-- Correct:
UPDATE identity.user_roles SET removed_at = CURRENT_TIMESTAMP 
WHERE user_id = @userId AND role_id = @roleId;

-- Wrong (no DELETE):
DELETE FROM identity.user_roles WHERE user_id = @userId;  -- ❌ Banned

---

Event Contracts (CDC)

UserCreated Event

{
  "eventId": "UUID",
  "eventType": "UserCreated",
  "userId": "UUID",
  "email": "user@example.com",
  "roles": ["Admin", "Analyst"],
  "createdAt": "2026-08-04T12:00:00Z",
  "correlationId": "req-001"
}

When: INSERT into identity.users
Consumer: ApprovalQueue (if user requires approval)

RoleAssigned Event

{
  "eventId": "UUID",
  "eventType": "RoleAssigned",
  "userId": "UUID",
  "roleName": "Analyst",
  "assignedAt": "2026-08-04T12:00:00Z",
  "correlationId": "req-001"
}

When: INSERT into identity.user_roles with removed_at IS NULL
Consumer: PermissionCache (invalidate user's permission set)

RoleRevoked Event

{
  "eventId": "UUID",
  "eventType": "RoleRevoked",
  "userId": "UUID",
  "roleName": "Analyst",
  "revokedAt": "2026-08-04T12:00:00Z",
  "correlationId": "req-001"
}

When: UPDATE identity.user_roles SET removed_at = now()
Consumer: PermissionCache (invalidate user's permission set)

---

Idempotency & Replay Safety

Create User Idempotency

Input: IdempotencyKey = create-user-alice-20260804
First Run:

INSERT INTO identity.users (email, password_hash, correlation_id)
VALUES ('alice@example.com', 'bcrypt(...)', 'req-001')
RETURNING id;
-- Result: UUID = 12345678-1234-1234-1234-123456789012

Replay (same IdempotencyKey):

-- Check if already created
SELECT id FROM identity.users WHERE email = 'alice@example.com';
-- Result: 12345678-1234-1234-1234-123456789012 (same)
-- Action: Return existing record (no duplicate INSERT)

Assign Role Idempotency

Input: IdempotencyKey = assign-alice-analyst-20260804
First Run:

INSERT INTO identity.user_roles (user_id, role_id, assigned_by_user_id)
VALUES (uuid-alice, 2, admin-user-id)
RETURNING id;
-- Result: ID = 1001

Replay:

-- Check if already assigned
SELECT id FROM identity.user_roles 
WHERE user_id = uuid-alice AND role_id = 2 AND removed_at IS NULL;
-- Result: 1001 (same)
-- Action: Return existing record (no duplicate)

---

Acceptance Criteria Checklist

• All tables created with 3NF normalization
• PIT queries tested (published_at ≤ cutoff)
• Append-only verified (no direct UPDATE on business keys)
• Immutability enforced (email, roles)
• Soft-delete working (removed_at pattern)
• Idempotency verified (replay tests passing)
• CDC events defined (UserCreated, RoleAssigned, RoleRevoked)
• Indexes created for performance
• Constraints enforced (CHECK, UNIQUE, FK)

---

Sign-Off

Role	Approval	Date
Data Architect	✅ Draft	2026-08-04
DBA	⏳ Review	TBD
Security	⏳ Review	TBD

---

Status: 📋 READY FOR DOMAIN TESTS & BE IMPLEMENTATION

Next: DomainPolicyTests (identity rules validation)