Devices Schema

CREATE TABLE public.devices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  organization_id UUID REFERENCES public.organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  device_type TEXT NOT NULL DEFAULT 'laptop',
  os TEXT,
  fingerprint TEXT UNIQUE,
  enrollment_token TEXT UNIQUE,
  enrolled_at TIMESTAMP WITH TIME ZONE,
  last_seen TIMESTAMP WITH TIME ZONE DEFAULT now(),
  location TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  trust_level TEXT NOT NULL DEFAULT 'low',
  tailscale_auth_key TEXT,
  tailscale_hostname TEXT,
  tailscale_device_id TEXT,
  tailscale_ip TEXT,
  enrollment_expires_at TIMESTAMP WITH TIME ZONE,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

CREATE INDEX idx_devices_enrollment_token 
  ON public.devices(enrollment_token) 
  WHERE enrollment_token IS NOT NULL;

CREATE INDEX idx_devices_tailscale_device_id 
  ON public.devices(tailscale_device_id) 
  WHERE tailscale_device_id IS NOT NULL;

{
  "created_by": "user-uuid",
  "tailscale_tags": ["tag:prod", "tag:user"],
  "tailscale_group": "engineering",
  "tailscale_online": true,
  "tailscale_last_seen": "2024-01-15T10:30:00Z",
  "token_validated_at": "2024-01-15T09:00:00Z",
  "netbird_peer_id": "peer-123",
  "device_model": "MacBook Pro 16-inch",
  "serial_number": "C02XY123456"
}

CREATE TABLE public.device_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  device_id UUID REFERENCES public.devices(id) ON DELETE CASCADE NOT NULL,
  event_type TEXT NOT NULL,
  details JSONB DEFAULT '{}'::jsonb,
  ip_address TEXT,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

{
  "method": "qr_code",
  "device_type": "laptop",
  "os": "macOS 14.0"
}

{
  "tailscale_device_id": "device-123",
  "hostname": "johns-macbook",
  "ip": "100.64.0.1",
  "tags": ["tag:prod"],
  "group": "engineering"
}

CREATE TABLE public.organization_tailscale_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  tailscale_auth_key TEXT NOT NULL,
  tags TEXT[] DEFAULT ARRAY['tag:prod'],
  description TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  expires_at TIMESTAMP WITH TIME ZONE,
  created_by UUID REFERENCES auth.users(id),
  UNIQUE(organization_id, tailscale_auth_key)
);

INSERT INTO devices (
  user_id,
  organization_id,
  name,
  device_type,
  os,
  enrollment_token,
  enrollment_expires_at,
  status,
  trust_level
) VALUES (
  'user-uuid',
  'org-uuid',
  'MacBook Pro',
  'laptop',
  'macOS 14.0',
  gen_random_uuid()::text,
  now() + interval '24 hours',
  'pending',
  'low'
)
RETURNING id, enrollment_token;

SELECT 
  id,
  name,
  device_type,
  os,
  status,
  trust_level,
  tailscale_ip,
  last_seen,
  enrolled_at
FROM devices
WHERE user_id = 'user-uuid'
ORDER BY last_seen DESC;

UPDATE devices
SET 
  status = 'active',
  enrolled_at = now(),
  trust_level = 'high',
  enrollment_token = NULL,
  tailscale_auth_key = NULL
WHERE id = 'device-uuid'
RETURNING *;

SELECT 
  event_type,
  details,
  ip_address,
  created_at
FROM device_events
WHERE device_id = 'device-uuid'
ORDER BY created_at DESC;

SELECT 
  d.*,
  p.full_name,
  o.name as organization_name
FROM devices d
JOIN profiles p ON p.id = d.user_id
JOIN organizations o ON o.id = d.organization_id
WHERE d.tailscale_device_id = 'ts-device-id';

-- Users can view their own devices
CREATE POLICY "Users can view their own devices"
ON public.devices FOR SELECT
USING (user_id = auth.uid());

-- Users can insert their own devices
CREATE POLICY "Users can insert their own devices"
ON public.devices FOR INSERT
WITH CHECK (user_id = auth.uid());

-- Users can update their own devices
CREATE POLICY "Users can update their own devices"
ON public.devices FOR UPDATE
USING (user_id = auth.uid());

-- Users can delete their own devices
CREATE POLICY "Users can delete their own devices"
ON public.devices FOR DELETE
USING (user_id = auth.uid());

-- Admins can view all devices in their org
CREATE POLICY "Admins can view devices in their org"
ON public.devices FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id FROM profiles
    WHERE id = auth.uid() AND role IN ('org_admin', 'global_admin')
  )
);

-- Users can view events for their devices
CREATE POLICY "Users can view their device events"
ON public.device_events FOR SELECT
USING (
  device_id IN (
    SELECT id FROM devices WHERE user_id = auth.uid()
  )
);

-- System can insert device events
CREATE POLICY "System can insert device events"
ON public.device_events FOR INSERT
WITH CHECK (true);

-- Admins can manage Tailscale config
CREATE POLICY "Admins can manage tailscale config"
ON public.organization_tailscale_config FOR ALL
USING (
  organization_id = get_user_org_id(auth.uid()) 
  AND get_user_role(auth.uid()) IN ('org_admin', 'global_admin')
);