VectorDNS/app/docs/architecture/database/page.tsx

import type { Metadata } from "next";
import { Database } from "lucide-react";
import {
  Card,
  CardContent,
  CardHeader,
  CardTitle,
  CardDescription,
} from "@/components/ui/card";
import { Badge } from "@/components/ui/badge";
import { Separator } from "@/components/ui/separator";

export const metadata: Metadata = {
  title: "Database Schema",
};

type Column = {
  name: string;
  type: string;
  notes?: string;
};

type Table = {
  name: string;
  description: string;
  columns: Column[];
};

const currentTables: Table[] = [
  {
    name: "profiles",
    description:
      "Extends Supabase auth.users with app-specific user preferences and settings.",
    columns: [
      { name: "id", type: "uuid", notes: "FK → auth.users" },
      { name: "email", type: "text" },
      { name: "display_name", type: "text", notes: "nullable" },
      { name: "avatar_url", type: "text", notes: "nullable" },
      { name: "created_at", type: "timestamptz" },
      { name: "updated_at", type: "timestamptz" },
    ],
  },
  {
    name: "saved_domains",
    description:
      "Domains the user has added to their watchlist for monitoring.",
    columns: [
      { name: "id", type: "uuid" },
      { name: "user_id", type: "uuid", notes: "FK → profiles" },
      { name: "domain", type: "text" },
      { name: "tags", type: "text[]", notes: "nullable" },
      { name: "last_checked_at", type: "timestamptz", notes: "nullable" },
      { name: "created_at", type: "timestamptz" },
    ],
  },
  {
    name: "dns_history",
    description:
      "Snapshots of DNS records captured at each monitoring check. Used for change detection and history browsing.",
    columns: [
      { name: "id", type: "uuid" },
      { name: "saved_domain_id", type: "uuid", notes: "FK → saved_domains" },
      { name: "user_id", type: "uuid", notes: "FK → profiles" },
      { name: "records", type: "jsonb", notes: "Full DNS snapshot" },
      { name: "resolver", type: "text", notes: "e.g. 8.8.8.8" },
      { name: "checked_at", type: "timestamptz" },
    ],
  },
  {
    name: "availability_history",
    description:
      "Tracks domain availability (registered / available) over time.",
    columns: [
      { name: "id", type: "uuid" },
      { name: "saved_domain_id", type: "uuid", notes: "FK → saved_domains" },
      { name: "user_id", type: "uuid", notes: "FK → profiles" },
      { name: "available", type: "boolean" },
      { name: "checked_at", type: "timestamptz" },
    ],
  },
  {
    name: "notifications",
    description:
      "In-app notification feed. Written by the Go monitoring service when changes are detected.",
    columns: [
      { name: "id", type: "uuid" },
      { name: "user_id", type: "uuid", notes: "FK → profiles" },
      { name: "saved_domain_id", type: "uuid", notes: "FK → saved_domains" },
      { name: "type", type: "text", notes: "e.g. dns_change, availability" },
      { name: "message", type: "text" },
      { name: "read", type: "boolean" },
      { name: "created_at", type: "timestamptz" },
    ],
  },
];

type PlannedTable = {
  name: string;
  for: string;
  description: string;
  keyColumns: string[];
};

const plannedTables: PlannedTable[] = [
  {
    name: "teams",
    for: "Team / org accounts",
    description:
      "Supports shared watchlists and role-based access for organizations.",
    keyColumns: ["id", "name", "owner_id", "created_at"],
  },
  {
    name: "team_members",
    for: "Team membership & roles",
    description:
      "Maps users to teams with a role (admin / viewer). Enables team-aware RLS policies.",
    keyColumns: ["team_id", "user_id", "role", "joined_at"],
  },
  {
    name: "domain_folders",
    for: "Folder organization",
    description:
      "Lets users organize monitored domains into named folders. saved_domains will gain an optional folder_id FK.",
    keyColumns: ["id", "user_id", "name", "created_at"],
  },
  {
    name: "shared_snapshots",
    for: "Public shareable links",
    description:
      "Stores DNS snapshots accessible via a unique token — no auth required to view.",
    keyColumns: [
      "id",
      "saved_domain_id",
      "share_token",
      "dns_snapshot",
      "expires_at",
    ],
  },
  {
    name: "notification_channels",
    for: "Webhook / Slack / Discord",
    description:
      "User-configured alert destinations beyond in-app and email. Config stored as JSONB.",
    keyColumns: ["id", "user_id", "type", "config", "enabled"],
  },
  {
    name: "subscriptions",
    for: "Billing tier tracking",
    description:
      "Tracks the active plan (free / pro / team) per user for feature gating.",
    keyColumns: ["id", "user_id", "plan", "valid_until", "created_at"],
  },
];

function TableCard({ table }: { table: Table }) {
  return (
    <Card>
      <CardHeader className="pb-3">
        <div className="flex items-center justify-between gap-2">
          <CardTitle className="font-mono text-base">{table.name}</CardTitle>
          <Badge variant="secondary" className="text-xs">
            current
          </Badge>
        </div>
        <CardDescription>{table.description}</CardDescription>
      </CardHeader>
      <CardContent>
        <div className="overflow-x-auto">
          <table className="w-full text-sm">
            <thead>
              <tr className="border-b">
                <th className="pb-2 pr-4 text-left font-medium text-muted-foreground">
                  Column
                </th>
                <th className="pb-2 pr-4 text-left font-medium text-muted-foreground">
                  Type
                </th>
                <th className="pb-2 text-left font-medium text-muted-foreground">
                  Notes
                </th>
              </tr>
            </thead>
            <tbody className="divide-y">
              {table.columns.map((col) => (
                <tr key={col.name}>
                  <td className="py-2 pr-4 font-mono text-xs text-foreground">
                    {col.name}
                  </td>
                  <td className="py-2 pr-4 font-mono text-xs text-primary">
                    {col.type}
                  </td>
                  <td className="py-2 text-xs text-muted-foreground">
                    {col.notes ?? "—"}
                  </td>
                </tr>
              ))}
            </tbody>
          </table>
        </div>
      </CardContent>
    </Card>
  );
}

export default function DatabaseSchemaPage() {
  return (
    <div className="space-y-10">
      {/* Page header */}
      <div className="space-y-3">
        <div className="flex items-center gap-2">
          <Database className="size-6 text-primary" />
          <h1 className="text-3xl font-bold tracking-tight">Database Schema</h1>
        </div>
        <p className="text-lg text-muted-foreground">
          VectorDNS uses Supabase (managed Postgres) with Row Level Security.
          There are currently 5 tables in production, with 6 more planned as
          features are built out.
        </p>
      </div>

      <Separator />

      {/* Current tables */}
      <div className="space-y-4">
        <div className="flex items-center justify-between">
          <h2 className="text-xl font-semibold tracking-tight">
            Current Tables
          </h2>
          <Badge>{currentTables.length} tables</Badge>
        </div>
        <div className="space-y-4">
          {currentTables.map((table) => (
            <TableCard key={table.name} table={table} />
          ))}
        </div>
      </div>

      <Separator />

      {/* Planned tables */}
      <div className="space-y-4">
        <div className="flex items-center justify-between">
          <h2 className="text-xl font-semibold tracking-tight">
            Planned Tables
          </h2>
          <Badge variant="outline">{plannedTables.length} planned</Badge>
        </div>
        <p className="text-sm text-muted-foreground">
          These tables will be added as the corresponding features are
          implemented. Schema details may evolve.
        </p>
        <div className="grid gap-4 sm:grid-cols-2">
          {plannedTables.map((table) => (
            <Card key={table.name}>
              <CardHeader className="pb-3">
                <div className="flex items-start justify-between gap-2">
                  <CardTitle className="font-mono text-base">
                    {table.name}
                  </CardTitle>
                  <Badge variant="outline" className="shrink-0 text-xs">
                    planned
                  </Badge>
                </div>
                <Badge
                  variant="secondary"
                  className="w-fit text-xs font-normal"
                >
                  {table.for}
                </Badge>
                <CardDescription className="pt-1">
                  {table.description}
                </CardDescription>
              </CardHeader>
              <CardContent>
                <div className="flex flex-wrap gap-1.5">
                  {table.keyColumns.map((col) => (
                    <code
                      key={col}
                      className="rounded bg-muted px-1.5 py-0.5 font-mono text-xs text-muted-foreground"
                    >
                      {col}
                    </code>
                  ))}
                </div>
              </CardContent>
            </Card>
          ))}
        </div>
      </div>

      <Separator />

      {/* RLS note */}
      <div className="space-y-3">
        <h2 className="text-xl font-semibold tracking-tight">
          Row Level Security
        </h2>
        <Card className="border-primary/20 bg-primary/5">
          <CardContent className="pt-6 text-sm text-muted-foreground">
            <p>
              All tables have RLS enabled. Policies enforce that users can only
              read and write their own rows (
              <code className="rounded bg-muted px-1.5 py-0.5 font-mono text-xs">
                user_id = auth.uid()
              </code>
              ). Future team-aware policies will extend this to allow team
              members access to shared resources based on their role.
            </p>
          </CardContent>
        </Card>
      </div>
    </div>
  );
}