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

VS-02: Security Master Data Contract

Slice: VS-02 (SynchronizeSecurityMaster)
Status: 📋 SPECIFICATION
Version: 1.0
Created: 2026-08-04

---

Schema (3NF Write Model)

security.rules (Permission Rules)

CREATE TABLE security.rules (
    id SERIAL PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL UNIQUE,
    resource VARCHAR(50) NOT NULL,  -- 'users', 'portfolios', 'trades'
    action VARCHAR(20) NOT NULL,    -- 'read', 'write', 'execute'
    description VARCHAR(255),
    
    -- Temporal & Versioning
    version INT NOT NULL DEFAULT 1,
    effective_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Audit
    created_by_user_id UUID,
    correlation_id VARCHAR(36),
    
    -- Constraints
    CONSTRAINT valid_resource CHECK (resource IN ('users', 'portfolios', 'trades', 'models')),
    CONSTRAINT valid_action CHECK (action IN ('read', 'write', 'execute', 'approve')),
    CONSTRAINT temporal_order CHECK (effective_at <= published_at),
    UNIQUE(rule_name, version)
);

CREATE INDEX idx_rules_effective_published 
    ON security.rules(effective_at, published_at);

security.role_permissions (Role-Permission Mapping)

CREATE TABLE security.role_permissions (
    id BIGSERIAL PRIMARY KEY,
    role_id INT NOT NULL REFERENCES identity.roles(id),
    rule_id INT NOT NULL REFERENCES security.rules(id),
    
    -- Temporal
    assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    removed_at TIMESTAMP,  -- Soft delete
    
    -- Audit
    correlation_id VARCHAR(36),
    
    -- Constraints
    CONSTRAINT valid_removal CHECK (removed_at IS NULL OR removed_at >= assigned_at),
    UNIQUE(role_id, rule_id) WHERE removed_at IS NULL
);

CREATE INDEX idx_role_perms_active 
    ON security.role_permissions(role_id, removed_at);

security.access_control_rules (Conditional Rules)

CREATE TABLE security.access_control_rules (
    id BIGSERIAL PRIMARY KEY,
    rule_id INT NOT NULL REFERENCES security.rules(id),
    
    -- Condition
    condition_type VARCHAR(50) NOT NULL,  -- 'time-based', 'location-based', 'mfa-required'
    condition_value JSONB NOT NULL,       -- {"startTime": "09:30", "endTime": "16:00"}
    
    -- Temporal
    effective_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT valid_condition_type CHECK (condition_type IN ('time-based', 'location-based', 'mfa-required'))
);

security.sync_checkpoint (Sync History)

CREATE TABLE security.sync_checkpoint (
    id BIGSERIAL PRIMARY KEY,
    
    -- Sync State
    sync_version INT NOT NULL UNIQUE,  -- Incremental version
    total_rules INT NOT NULL,
    synced_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Idempotency
    correlation_id VARCHAR(36) UNIQUE,
    
    -- Status
    status VARCHAR(20) DEFAULT 'success'  -- 'success', 'partial', 'failed'
        CHECK (status IN ('success', 'partial', 'failed')),
    
    -- Rollback
    previous_version INT REFERENCES security.sync_checkpoint(sync_version),
    error_message VARCHAR(500)
);

CREATE INDEX idx_sync_latest ON security.sync_checkpoint(synced_at DESC);

---

PIT (Point-in-Time) Queries

Get current permissions for role:

SELECT sr.rule_name, sr.resource, sr.action
FROM security.role_permissions rp
JOIN security.rules sr ON rp.rule_id = sr.id
WHERE rp.role_id = @roleId
  AND rp.published_at <= @cutoff
  AND rp.removed_at IS NULL
  AND sr.effective_at <= @cutoff
  AND (sr.expires_at IS NULL OR sr.expires_at > @cutoff);

Get rules active at specific time:

SELECT * FROM security.rules
WHERE published_at <= @cutoff
  AND effective_at <= @cutoff
  AND (expires_at IS NULL OR expires_at > @cutoff);

---

CDC Events

SecurityMasterSynced

{
  "eventId": "UUID",
  "eventType": "SecurityMasterSynced",
  "syncVersion": 42,
  "totalRules": 156,
  "newRules": 3,
  "modifiedRules": 5,
  "syncedAt": "2026-08-04T12:00:00Z",
  "correlationId": "sync-001"
}

PermissionRuleUpdated

{
  "eventId": "UUID",
  "eventType": "PermissionRuleUpdated",
  "ruleId": 123,
  "ruleName": "trader_execute_permission",
  "action": "execute",
  "version": 2,
  "syncVersion": 42,
  "correlationId": "sync-001"
}

---

Acceptance Criteria Checklist

• All tables created with 3NF normalization
• PIT queries tested (published_at, effective_at, expires_at)
• Append-only verified (no direct UPDATE on business keys)
• Soft-delete working (removed_at pattern)
• Sync checkpoint tracked (version-based idempotency)
• CDC events defined (SecurityMasterSynced, PermissionRuleUpdated)
• Conditional rules supported (time-based, location-based, MFA)
• Indexes created for performance

---

Status: 📋 READY FOR DOMAIN TESTS & BE IMPLEMENTATION

Next: VS-02 DOMAIN Tests (sync logic validation)