Data Models

-- One-to-Many Relationships
User (1) ----< (N) Session
User (1) ----< (N) Device
Device (1) ----< (N) Session
Device (1) ----< (N) DeviceStatus
Session (1) ----< (N) Recording
Session (1) ----< (N) Event
Recording (1) ----< (N) DataChunk
Recording (1) ----< (N) Feature
DataChunk (1) ----< (N) Sample
Feature (1) ----< (N) Classification
 
-- Many-to-Many Relationships (via junction tables)
Device <----> Session (via Recording)
User <----> Device (ownership can be transferred)

-- PostgreSQL Tables
CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL CHECK (role IN ('admin', 'researcher', 'clinician')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_login TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT true
);
 
CREATE TABLE devices (
    device_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(user_id),
    device_type VARCHAR(50) NOT NULL,
    serial_number VARCHAR(255) UNIQUE NOT NULL,
    firmware_version VARCHAR(50),
    configuration JSONB DEFAULT '{}',
    registered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_seen TIMESTAMP WITH TIME ZONE,
    INDEX idx_user_devices (user_id),
    INDEX idx_device_type (device_type)
);
 
-- Bigtable Schema
Table: neural_data
Row Key: {recording_id}#{timestamp}#{channel}
Column Families:
  - raw: raw_value, quality_score
  - meta: sample_rate, device_id
  - features: fft, psd, entropy
 
-- BigQuery Schema
Dataset: neurascale_analytics
Tables:
  - features: Partitioned by DATE(computed_at)
  - classifications: Partitioned by DATE(predicted_at)
  - aggregated_metrics: Materialized views for dashboards

patient_privacy:
  - de_identification:
      method: "Safe Harbor"
      fields: ["name", "address", "dob", "ssn"]
      retention: "hash_only"
 
  - encryption:
      at_rest: "AES-256-GCM"
      in_transit: "TLS 1.3"
      key_management: "Cloud KMS"
 
  - access_control:
      authentication: "multi_factor"
      authorization: "role_based"
      minimum_necessary: true
 
audit_trail:
  - data_access:
      who: "user_id, role, ip_address"
      what: "resource, action, fields"
      when: "timestamp_utc"
      why: "purpose_of_use"
 
  - retention:
      audit_logs: "7_years"
      clinical_data: "per_consent"
      backups: "30_days"
 
breach_notification:
  - detection: "real_time"
  - notification: "within_72_hours"
  - reporting: "automated"

class ClinicalAccessControl:
    def __init__(self):
        self.permissions = {
            "clinician": {
                "patient.read": "assigned_only",
                "patient.write": "with_consent",
                "session.create": "own_only",
                "report.sign": "authored_only"
            },
            "researcher": {
                "data.read": "deidentified_only",
                "analysis.run": "approved_protocols",
                "export.data": "with_irb_approval"
            }
        }
 
    async def check_access(
        self,
        user: User,
        resource: str,
        action: str,
        context: dict
    ) -> bool:
        # Check role-based permissions
        role_perms = self.permissions.get(user.role, {})
        permission = f"{resource}.{action}"
 
        if permission not in role_perms:
            return False
 
        # Apply contextual rules
        rule = role_perms[permission]
        return await self._evaluate_rule(
            user, resource, rule, context
        )

# Row Key Design
# Format: {shard}#{recording_id}#{timestamp}#{channel}
# Shard: 0-99 for load distribution
# Timestamp: Microseconds since epoch
# Channel: Zero-padded channel number
 
class TimeSeriesSchema:
    # Column Families
    COLUMN_FAMILIES = {
        'raw': {
            'max_versions': 1,
            'ttl_days': 30,
            'compression': 'SNAPPY'
        },
        'meta': {
            'max_versions': 5,
            'ttl_days': 90,
            'compression': 'NONE'
        },
        'derived': {
            'max_versions': 3,
            'ttl_days': 7,
            'compression': 'ZSTD'
        }
    }
 
    # Column Qualifiers
    RAW_COLUMNS = [
        'value',          # Raw sample value
        'quality',        # Quality score (0-1)
        'timestamp_us'    # Precise timestamp
    ]
 
    META_COLUMNS = [
        'device_id',
        'session_id',
        'sample_rate',
        'filter_state',
        'annotations'
    ]
 
    DERIVED_COLUMNS = [
        'fft_bins',       # Frequency domain
        'psd',            # Power spectral density
        'phase',          # Phase information
        'envelope'        # Signal envelope
    ]

class BatchWriter:
    def __init__(self, table):
        self.table = table
        self.batch_size = 1000
        self.buffer = []
 
    async def write_samples(
        self,
        recording_id: str,
        samples: np.ndarray,
        timestamp: int
    ):
        # Prepare batch mutations
        mutations = []
        shard = hash(recording_id) % 100
 
        for i, sample in enumerate(samples):
            row_key = f"{shard:02d}#{recording_id}#{timestamp+i}#00"
 
            mutations.append(
                SetCell(
                    family='raw',
                    column='value',
                    value=struct.pack('f', sample)
                )
            )
 
        # Batch write
        if len(self.buffer) >= self.batch_size:
            await self.flush()

-- Time Range Query (Optimized)
SELECT timestamp, channel, value
FROM neural_data
WHERE recording_id = ?
  AND timestamp BETWEEN ? AND ?
  AND channel IN (?)
ORDER BY timestamp;
 
-- Aggregation Query
SELECT
    DATE_TRUNC('second', timestamp) as time_bucket,
    channel,
    AVG(value) as mean_value,
    STDDEV(value) as std_value,
    COUNT(*) as sample_count
FROM neural_data
WHERE recording_id = ?
GROUP BY time_bucket, channel;
 
-- Feature Extraction Query
WITH windowed_data AS (
    SELECT
        timestamp,
        channel,
        value,
        LAG(value, 1) OVER (PARTITION BY channel ORDER BY timestamp) as prev_value,
        LEAD(value, 1) OVER (PARTITION BY channel ORDER BY timestamp) as next_value
    FROM neural_data
    WHERE recording_id = ?
)
SELECT
    timestamp,
    channel,
    value,
    (value - prev_value) as diff,
    (next_value - 2*value + prev_value) as second_diff
FROM windowed_data;