fix(sync): robustly map domain rows to subscription status

Reviewed-on: http://gitea.lan:3000/robbond/pleskSaas/pulls/9

app/api/plesk/domains/[id]/action/route.ts

@@ -0,0 +1,147 @@
+import { NextResponse } from "next/server";
+import { z } from "zod";
+
+import { assertAgencyAdmin, getRouteAgencyContextOrThrow } from "@/lib/agency";
+import { assertRateLimit } from "@/lib/api/rate-limit";
+import { assertSameOrigin } from "@/lib/api/security";
+import {
+  suspendPleskSubscription,
+  unsuspendPleskSubscription,
+} from "@/lib/plesk/client";
+import { createSupabaseRouteClient } from "@/lib/supabase/route";
+
+const actionSchema = z.object({
+  action: z.enum(["suspend", "unsuspend"]),
+});
+
+export async function POST(
+  req: Request,
+  { params }: { params: { id: string } },
+) {
+  const supabase = createSupabaseRouteClient() as any;
+  let context: Awaited<ReturnType<typeof getRouteAgencyContextOrThrow>> | null =
+    null;
+  let domain: { id: string; subscription_id: string | null } | null = null;
+  let subscription: { id: string; plesk_subscription_id: string } | null = null;
+  let requestedAction: "suspend" | "unsuspend" | null = null;
+
+  try {
+    assertSameOrigin(req);
+
+    context = await getRouteAgencyContextOrThrow();
+    assertAgencyAdmin(context);
+    assertRateLimit(`plesk-domain-action:${context.userId}`, 10, 60_000);
+
+    const { action } = actionSchema.parse(await req.json());
+    requestedAction = action;
+
+    const { data: domainRow, error: domainError } = await supabase
+      .from("plesk_domains")
+      .select("id, agency_id, plesk_instance_id, subscription_id")
+      .eq("id", params.id)
+      .eq("agency_id", context.agencyId)
+      .single();
+
+    if (domainError || !domainRow) {
+      throw new Error("Domain not found");
+    }
+
+    domain = {
+      id: String(domainRow.id),
+      subscription_id: domainRow.subscription_id
+        ? String(domainRow.subscription_id)
+        : null,
+    };
+
+    if (!domain.subscription_id) {
+      throw new Error("Domain is not linked to a subscription");
+    }
+
+    const { data: subscriptionRow, error: subscriptionError } = await supabase
+      .from("plesk_subscriptions")
+      .select("id, plesk_subscription_id, plesk_instance_id")
+      .eq("id", domain.subscription_id)
+      .eq("agency_id", context.agencyId)
+      .single();
+
+    if (subscriptionError || !subscriptionRow) {
+      throw new Error("Subscription not found");
+    }
+
+    subscription = {
+      id: String(subscriptionRow.id),
+      plesk_subscription_id: String(subscriptionRow.plesk_subscription_id),
+    };
+
+    const { data: instance, error: instanceError } = await supabase
+      .from("plesk_instances")
+      .select("id, base_url, auth_type, encrypted_secret")
+      .eq("id", subscriptionRow.plesk_instance_id)
+      .eq("agency_id", context.agencyId)
+      .single();
+
+    if (instanceError || !instance) {
+      throw new Error("Plesk instance not found");
+    }
+
+    const connection = {
+      baseUrl: instance.base_url,
+      authType: instance.auth_type,
+      encryptedSecret: instance.encrypted_secret,
+    } as const;
+
+    if (action === "suspend") {
+      await suspendPleskSubscription(
+        connection,
+        subscription.plesk_subscription_id,
+      );
+    } else {
+      await unsuspendPleskSubscription(
+        connection,
+        subscription.plesk_subscription_id,
+      );
+    }
+
+    await supabase.from("actions_log").insert({
+      agency_id: context.agencyId,
+      actor_user_id: context.userId,
+      target_type: "plesk_domain",
+      target_id: domain.id,
+      action: `domain_${action}`,
+      status: "success",
+      metadata: {
+        domain_id: domain.id,
+        subscription_id: subscription.id,
+        plesk_subscription_id: subscription.plesk_subscription_id,
+      },
+    });
+
+    return NextResponse.json({ ok: true });
+  } catch (error) {
+    if (context && domain) {
+      const message =
+        error instanceof Error ? error.message : "Domain action failed";
+      await supabase.from("actions_log").insert({
+        agency_id: context.agencyId,
+        actor_user_id: context.userId,
+        target_type: "plesk_domain",
+        target_id: domain.id,
+        action: requestedAction ? `domain_${requestedAction}` : "domain_action",
+        status: "failed",
+        error_message: message,
+        metadata: {
+          domain_id: domain.id,
+          subscription_id: subscription?.id ?? domain.subscription_id,
+          plesk_subscription_id: subscription?.plesk_subscription_id ?? null,
+        },
+      });
+    }
+
+    return NextResponse.json(
+      {
+        error: error instanceof Error ? error.message : "Domain action failed",
+      },
+      { status: 400 },
+    );
+  }
+}

app/dashboard/page.tsx

@@ -3,7 +3,6 @@ import { DashboardControls } from "@/components/dashboard/dashboard-controls";
 import { getServerAgencyContextOrRedirect } from "@/lib/agency";
 import type { Database } from "@/lib/types";
 import { createSupabaseServerClient } from "@/lib/supabase/server";
-import type { PostgrestError } from "@supabase/supabase-js";
 
 type AgencySummary = Pick<
   Database["public"]["Tables"]["agencies"]["Row"],
@@ -51,7 +50,7 @@ export default async function DashboardPage() {
     supabase
       .from("plesk_domains")
       .select(
-        "id, plesk_instance_id, domain_name, status, hosting_type, source_created_at, aliases_count, updated_at",
+        "id, plesk_instance_id, subscription_id, domain_name, status, hosting_type, source_created_at, aliases_count, updated_at",
       )
       .eq("agency_id", context.agencyId)
       .order("updated_at", { ascending: false })
@@ -101,6 +100,7 @@ export default async function DashboardPage() {
       data: Array<{
         id: string;
         plesk_instance_id: string;
+        subscription_id: string | null;
         domain_name: string;
         status: string | null;
         hosting_type: string | null;

components/dashboard/dashboard-controls.tsx

@@ -31,6 +31,7 @@ type Subscription = {
 type Domain = {
   id: string;
   plesk_instance_id: string;
+  subscription_id: string | null;
   domain_name: string;
   status: string | null;
   hosting_type: string | null;
@@ -56,6 +57,10 @@ function getDomainStatusBadge(status: string | null) {
     return "bg-rose-100 text-rose-700";
   }
 
+  if (normalized.includes("expired")) {
+    return "bg-amber-100 text-amber-700";
+  }
+
   if (normalized.includes("active") || normalized.includes("enabled")) {
     return "bg-emerald-100 text-emerald-700";
   }
@@ -67,6 +72,26 @@ function getDomainStatusBadge(status: string | null) {
   return "bg-slate-100 text-slate-600";
 }
 
+function isDomainSuspended(status: string | null) {
+  const normalized = (status ?? "unknown").toLowerCase();
+  return normalized.includes("suspend") || normalized.includes("disabled");
+}
+
+function isDomainActive(status: string | null) {
+  const normalized = (status ?? "unknown").toLowerCase();
+  return (
+    normalized.includes("active") ||
+    normalized.includes("ok") ||
+    normalized.includes("enabled")
+  );
+}
+
+function getDomainHref(domainName: string) {
+  const normalized = domainName.trim();
+  if (/^https?:\/\//i.test(normalized)) return normalized;
+  return `https://${normalized}`;
+}
+
 export function DashboardControls({
   instances,
   subscriptions,
@@ -213,6 +238,25 @@ export function DashboardControls({
     }
   }
 
+  async function onDomainAction(
+    domainId: string,
+    action: "suspend" | "unsuspend",
+  ) {
+    setLoading(true);
+    setMessage(null);
+    try {
+      await runRequest(`/api/plesk/domains/${domainId}/action`, { action });
+      setMessage("Domain action completed.");
+      window.location.reload();
+    } catch (error) {
+      setMessage(
+        error instanceof Error ? error.message : "Domain action failed",
+      );
+    } finally {
+      setLoading(false);
+    }
+  }
+
   async function onCheckout() {
     setLoading(true);
     try {
@@ -499,7 +543,7 @@ export function DashboardControls({
           {message ? <p className="text-sm text-slate-600">{message}</p> : null}
         </div>
 
-        <div className="space-y-3 rounded-xl border border-slate-200 bg-white p-4">
+        <div className="space-y-3 rounded-xl border border-slate-200 bg-white p-4 lg:col-span-2">
           <div className="flex flex-wrap items-end gap-2">
             <div className="flex-1 min-w-[220px]">
               <label className="text-sm font-medium">Search domains</label>
@@ -536,25 +580,38 @@ export function DashboardControls({
               <thead>
                 <tr className="border-b border-slate-200 text-xs uppercase text-slate-500">
                   <th className="px-2 py-2">Domain</th>
-                  <th className="px-2 py-2">Status</th>
+                  <th className="px-2 py-2">Subscription Status</th>
                   <th className="px-2 py-2">Hosting</th>
                   <th className="px-2 py-2">Created</th>
                   <th className="px-2 py-2">Aliases</th>
                   <th className="px-2 py-2">Last seen</th>
+                  <th className="px-2 py-2">Actions</th>
                 </tr>
               </thead>
               <tbody>
                 {filteredDomains.length === 0 ? (
                   <tr>
-                    <td className="px-2 py-3 text-slate-500" colSpan={6}>
+                    <td className="px-2 py-3 text-slate-500" colSpan={7}>
                       No domains found.
                     </td>
                   </tr>
                 ) : (
                   filteredDomains.map((domain) => (
                     <tr key={domain.id} className="border-b border-slate-100">
-                      <td className="font-medium text-slate-900">
+                      <td className="px-2 py-2 font-medium text-slate-900">
-                        {domain.domain_name}
+                        <a
+                          href={getDomainHref(domain.domain_name)}
+                          target="_blank"
+                          rel="noopener noreferrer"
+                          className="underline decoration-slate-300 underline-offset-2 hover:text-brand-700"
+                        >
+                          {domain.domain_name}
+                        </a>
+                        {domain.subscription_id ? (
+                          <p className="text-xs font-normal text-slate-500">
+                            Subscription: {domain.subscription_id}
+                          </p>
+                        ) : null}
                       </td>
                       <td className="px-2 py-2">
                         <span
@@ -573,6 +630,28 @@ export function DashboardControls({
                       <td className="px-2 py-2">
                         {formatDateTime(domain.updated_at)}
                       </td>
+                      <td className="px-2 py-2">
+                        <div className="flex gap-2">
+                          <button
+                            onClick={() => onDomainAction(domain.id, "suspend")}
+                            disabled={
+                              loading || isDomainSuspended(domain.status)
+                            }
+                            className="rounded border border-rose-300 px-2 py-1 text-xs text-rose-700 disabled:cursor-not-allowed disabled:opacity-60"
+                          >
+                            Suspend
+                          </button>
+                          <button
+                            onClick={() =>
+                              onDomainAction(domain.id, "unsuspend")
+                            }
+                            disabled={loading || isDomainActive(domain.status)}
+                            className="rounded border border-emerald-300 px-2 py-1 text-xs text-emerald-700 disabled:cursor-not-allowed disabled:opacity-60"
+                          >
+                            Unsuspend
+                          </button>
+                        </div>
+                      </td>
                     </tr>
                   ))
                 )}

docs/ai-project-context.md

@@ -0,0 +1,230 @@
+# Project: Plesk Agency Portal
+
+This project is a multi-tenant SaaS platform that allows agencies to manage multiple Plesk servers from a single dashboard.
+
+The application synchronizes subscriptions and domains from Plesk instances and allows administrators to manage hosting accounts.
+
+---
+
+# Technology Stack
+
+Frontend
+
+- Next.js (App Router)
+- React
+- TypeScript
+
+Backend
+
+- Next.js API routes
+- Node runtime
+
+Database
+
+- Supabase Postgres
+
+Authentication
+
+- Supabase Auth (magic link)
+
+External Integrations
+
+- Plesk REST API
+- Plesk CLI
+- Stripe (future billing integration)
+
+Development Tools
+
+- Git
+- Docker (for some services)
+- Jenkins (future CI/CD)
+
+---
+
+# Core Database Tables
+
+agencies
+
+agency_members
+
+plesk_instances
+
+plesk_subscriptions
+
+plesk_domains
+
+actions_log
+
+billing_accounts
+
+---
+
+# Multi-Tenant Model
+
+Each user belongs to an agency.
+
+Agency relationships:
+
+user  
+→ agency_members  
+→ agencies  
+→ plesk_instances  
+→ plesk_subscriptions  
+→ plesk_domains
+
+Row Level Security in Supabase ensures users can only access rows belonging to their agency.
+
+Never bypass or remove RLS policies.
+
+---
+
+# Plesk Integration
+
+The system integrates with Plesk using both:
+
+Plesk REST API  
+Plesk CLI
+
+Common CLI commands used:
+
+subscription --info  
+subscription --suspend  
+subscription --unsuspend
+
+CLI output must always be parsed defensively because formatting can vary between Plesk versions.
+
+---
+
+# Sync System
+
+The platform supports manual synchronization of Plesk instances.
+
+During sync the system:
+
+1. Connects to a Plesk instance
+2. Retrieves subscriptions and domains
+3. Updates records in Supabase
+4. Updates the dashboard
+
+Future development will include background sync workers.
+
+---
+
+# Dashboard Features
+
+Current features include:
+
+Connecting Plesk instances  
+Testing connections  
+Manual sync  
+Listing domains and subscriptions  
+Suspending subscriptions  
+Unsuspending subscriptions
+
+---
+
+# Logging
+
+Operational actions are written to:
+
+actions_log
+
+Examples include:
+
+instance_connected  
+sync_started  
+sync_completed  
+subscription_suspended  
+subscription_unsuspended
+
+Logging should never block primary workflows.
+
+---
+
+# Development Rules
+
+When implementing features:
+
+Search the repository before modifying code.
+
+Extend existing architecture rather than rewriting systems.
+
+Avoid introducing breaking database schema changes.
+
+Prefer small modular functions.
+
+Keep API routes thin.
+
+Place integration logic inside lib folders.
+
+---
+
+# Git Workflow
+
+Default branch:
+
+master
+
+All work must be done on feature branches.
+
+Example:
+
+git checkout master  
+git pull  
+git checkout -b feature/<feature-name>
+
+Never commit directly to master.
+
+---
+
+# Error Handling
+
+External integrations must always fail gracefully.
+
+If a single subscription fails during sync, the rest of the sync must continue.
+
+All failures should be logged.
+
+---
+
+# UI Safety
+
+Dashboard components must:
+
+Handle null values safely  
+Avoid crashing if API responses change  
+Use status badges instead of raw text for statuses
+
+---
+
+# Future Roadmap
+
+Upcoming development milestones include:
+
+CL4 – Subscription status ingestion via CLI
+
+CL5 – Action logging and retry queue
+
+CL6 – Automatic sync worker
+
+CL7 – Stripe billing enforcement
+
+CL8 – Instance health monitoring
+
+CL9 – Observability and audit improvements
+
+---
+
+# AI Agent Instructions
+
+Before modifying code:
+
+Search the repository for related implementations.
+
+Avoid rewriting working systems.
+
+Implement minimal safe modifications.
+
+Always verify that existing features continue working.
+
+If unsure about a change, prefer logging rather than failing.

docs/system-architecture.md

@@ -0,0 +1,179 @@
+# System Architecture
+
+This document describes the architecture of the Plesk Agency Portal SaaS platform.
+
+The platform allows agencies to connect multiple Plesk servers and manage hosting subscriptions from a single dashboard.
+
+---
+
+# High Level Components
+
+The system consists of the following components:
+
+Frontend Dashboard  
+Backend API  
+Supabase Database  
+Plesk Servers  
+Future Worker Services  
+Future Billing Integration
+
+At a high level, users interact with the Frontend Dashboard, which sends authenticated requests to the Backend API (Next.js API routes). The backend reads and writes persistent state in Supabase, and executes remote operations against connected Plesk servers via API/CLI integrations. Future worker services will run asynchronous sync and retry workflows, while future billing integration will enforce plan limits and subscription state.
+
+---
+
+# Frontend
+
+The frontend is a Next.js application using the App Router.
+
+Responsibilities include:
+
+User authentication  
+Dashboard interface  
+Displaying domains and subscriptions  
+Triggering sync operations  
+Sending management actions (suspend / unsuspend)
+
+The frontend communicates with backend API routes.
+
+---
+
+# Backend
+
+The backend uses Next.js API routes.
+
+Responsibilities include:
+
+Handling authenticated requests  
+Connecting to Supabase  
+Executing Plesk API and CLI commands  
+Processing sync operations  
+Writing operational logs
+
+Business logic should be implemented in reusable modules inside the lib directory.
+
+---
+
+# Database
+
+Supabase Postgres is used for persistent storage.
+
+Key tables include:
+
+agencies  
+agency_members  
+plesk_instances  
+plesk_subscriptions  
+plesk_domains  
+actions_log  
+billing_accounts
+
+Relationship overview:
+
+- `agencies` is the tenant root.
+- `agency_members` associates users to agencies and roles.
+- `plesk_instances` stores connected remote Plesk endpoints per agency.
+- `plesk_subscriptions` stores subscription-level records linked to instances.
+- `plesk_domains` stores domain-level records linked to subscriptions/instances.
+- `actions_log` stores operational/audit events.
+- `billing_accounts` stores plan/subscription billing state per agency.
+
+The multi-tenant model is enforced using Supabase Row Level Security policies so users can only read/write rows that belong to their agency.
+
+---
+
+# Plesk Integration
+
+The platform communicates with remote Plesk servers.
+
+Integration methods:
+
+Plesk REST API  
+Plesk CLI commands
+
+Typical operations include:
+
+Listing subscriptions  
+Retrieving domain data  
+Suspending subscriptions  
+Unsuspending subscriptions
+
+CLI parsing must be defensive due to formatting differences between Plesk versions.
+
+---
+
+# Sync System
+
+The system supports synchronization of Plesk servers.
+
+Manual sync is triggered from the dashboard.
+
+During sync:
+
+The backend connects to a Plesk instance  
+Subscription and domain data are retrieved  
+Records are updated in Supabase  
+The dashboard displays updated data
+
+Future development will add automated background sync workers.
+
+---
+
+# Logging System
+
+Operational actions are recorded in the actions_log table.
+
+Examples:
+
+instance_connected  
+sync_started  
+sync_completed  
+subscription_suspended  
+subscription_unsuspended
+
+Logging supports debugging and auditability.
+
+---
+
+# Future Worker Architecture
+
+Future versions of the system will include background workers for:
+
+Automatic instance synchronization  
+Retry queues  
+Monitoring tasks
+
+Workers may run as:
+
+Cron jobs  
+Queue processors  
+Dedicated worker services
+
+---
+
+# Billing Architecture (Future)
+
+Stripe will be integrated for SaaS billing.
+
+Billing responsibilities will include:
+
+Agency subscription plans  
+Usage limits  
+Payment handling
+
+Billing enforcement will eventually control:
+
+Number of connected Plesk instances  
+Number of managed domains
+
+---
+
+# Development Philosophy
+
+The system prioritizes:
+
+Stability  
+Security  
+Multi-tenant isolation  
+Safe integrations with external systems
+
+Developers should extend existing systems rather than rewriting them.

lib/plesk/client.ts

@@ -16,6 +16,8 @@ export type PleskSubscription = {
   external_id?: string;
   name?: string;
   status?: string;
+  status_raw?: string | null;
+  status_sync_error?: string | null;
   owner_login?: string;
   owner?: { login?: string };
   plan_name?: string;
@@ -148,16 +150,27 @@ export async function listPleskSubscriptions(connection: PleskConnection) {
   const subscriptions: PleskSubscription[] = [];
 
   for (const subscriptionName of subscriptionNames.slice(0, 500)) {
-    let status: "active" | "suspended" | "unknown" = "unknown";
+    let status: "active" | "suspended" | "disabled" | "expired" | "unknown" =
+      "unknown";
+    let statusRaw: string | null = null;
+    let statusSyncError: string | null = null;
 
     try {
       const infoResult = await pleskCliCall(connection, "subscription", [
         "--info",
         subscriptionName,
       ]);
-      status = parseSubscriptionStatusFromInfo(infoResult.stdout ?? "");
+      const parsed = parseSubscriptionStatusDetailsFromInfo(
-    } catch {
+        infoResult.stdout ?? "",
+      );
+      status = parsed.status;
+      statusRaw = parsed.statusRaw;
+    } catch (error) {
       status = "unknown";
+      statusSyncError =
+        error instanceof Error
+          ? error.message.slice(0, 1000)
+          : "status_sync_failed";
     }
 
     subscriptions.push({
@@ -165,33 +178,57 @@ export async function listPleskSubscriptions(connection: PleskConnection) {
       subscription_id: subscriptionName,
       name: subscriptionName,
       status,
+      status_raw: statusRaw,
+      status_sync_error: statusSyncError,
     });
   }
 
   return subscriptions;
 }
 
-export function parseSubscriptionStatusFromInfo(
+export function parseSubscriptionStatusDetailsFromInfo(stdout: string): {
-  stdout: string,
+  status: "active" | "suspended" | "disabled" | "expired" | "unknown";
-): "active" | "suspended" | "unknown" {
+  statusRaw: string | null;
+} {
   const line = stdout
     .split(/\r?\n/)
     .map((entry) => entry.trim())
     .find((entry) => /^domain\s+status\s*:/i.test(entry));
 
-  if (!line) return "unknown";
+  if (!line) {
-
+    return { status: "unknown", statusRaw: null };
-  const value = line
-    .replace(/^domain\s+status\s*:/i, "")
-    .trim()
-    .toLowerCase();
-
-  if (value.includes("ok")) return "active";
-  if (value.includes("suspend") || value.includes("disabled")) {
-    return "suspended";
   }
 
-  return "unknown";
+  const value = line.replace(/^domain\s+status\s*:/i, "").trim();
+  const normalized = value.toLowerCase();
+
+  if (normalized.includes("expired")) {
+    return { status: "expired", statusRaw: value };
+  }
+
+  if (normalized.includes("disabled")) {
+    return { status: "disabled", statusRaw: value };
+  }
+
+  if (normalized.includes("suspend")) {
+    return { status: "suspended", statusRaw: value };
+  }
+
+  if (
+    normalized.includes("ok") ||
+    normalized.includes("active") ||
+    normalized.includes("enabled")
+  ) {
+    return { status: "active", statusRaw: value };
+  }
+
+  return { status: "unknown", statusRaw: value };
+}
+
+export function parseSubscriptionStatusFromInfo(
+  stdout: string,
+): "active" | "suspended" | "disabled" | "expired" | "unknown" {
+  return parseSubscriptionStatusDetailsFromInfo(stdout).status;
 }
 
 export async function pleskCliCall(

lib/plesk/sync.ts

@@ -22,6 +22,13 @@ type InstanceRow = Pick<
   | "last_connected_at"
 >;
 
+type LocalSubscriptionLookupRow = {
+  id: string;
+  plesk_subscription_id: string;
+  name: string | null;
+  status: string | null;
+};
+
 const MIN_INTERVAL_MS = 3 * 60 * 1000;
 
 export async function syncPleskInstance(
@@ -32,6 +39,31 @@ export async function syncPleskInstance(
 ): Promise<SyncResult> {
   const startedAtIso = new Date().toISOString();
 
+  const logStatusSyncEvent = async (
+    eventAction:
+      | "subscription_status_sync_started"
+      | "subscription_status_sync_completed"
+      | "subscription_status_sync_failed",
+    metadata?: Record<string, unknown>,
+    status: "success" | "failed" = "success",
+    errorMessage?: string | null,
+  ) => {
+    try {
+      await supabase.from("actions_log").insert({
+        agency_id: instance.agency_id,
+        actor_user_id: actorUserId ?? null,
+        target_type: "plesk_instance",
+        target_id: instance.id,
+        action: eventAction,
+        status,
+        error_message: errorMessage ?? null,
+        metadata: metadata ?? {},
+      });
+    } catch {
+      // Logging should never block sync flow.
+    }
+  };
+
   await supabase
     .from("plesk_instances")
     .update({
@@ -67,6 +99,10 @@ export async function syncPleskInstance(
   }
 
   try {
+    await logStatusSyncEvent("subscription_status_sync_started", {
+      started_at: startedAtIso,
+    });
+
     const [subscriptionsRaw, domainsRaw] = await Promise.all([
       listPleskSubscriptions({
         baseUrl: instance.base_url,
@@ -95,6 +131,8 @@ export async function syncPleskInstance(
           plesk_subscription_id: String(sourceId),
           name: item?.name ? String(item.name) : String(sourceId),
           status: item?.status ? String(item.status) : "unknown",
+          status_raw: item?.status_raw ? String(item.status_raw) : null,
+          last_status_sync_at: startedAtIso,
           owner_login: item?.owner_login
             ? String(item.owner_login)
             : item?.owner?.login
@@ -109,6 +147,10 @@ export async function syncPleskInstance(
       })
       .filter(Boolean) as Array<Record<string, unknown>>;
 
+    const subscriptionStatusSyncFailures = subscriptionsRaw.filter(
+      (item: any) => Boolean(item?.status_sync_error),
+    );
+
     if (subscriptionsForUpsert.length > 0) {
       const { error } = await supabase
         .from("plesk_subscriptions")
@@ -121,7 +163,7 @@ export async function syncPleskInstance(
     const { data: localSubscriptions, error: localSubscriptionsError } =
       await supabase
         .from("plesk_subscriptions")
-        .select("id, plesk_subscription_id, status")
+        .select("id, plesk_subscription_id, name, status")
         .eq("agency_id", instance.agency_id)
         .eq("plesk_instance_id", instance.id);
 
@@ -129,19 +171,49 @@ export async function syncPleskInstance(
       throw new Error(localSubscriptionsError.message);
     }
 
+    const subscriptionsIndexed = ((localSubscriptions ?? []) as Array<any>)
+      .filter((row): row is LocalSubscriptionLookupRow =>
+        Boolean(row?.plesk_subscription_id && row?.id),
+      )
+      .map((row) => ({
+        id: String(row.id),
+        pleskSubscriptionId: String(row.plesk_subscription_id),
+        status: row?.status ? String(row.status) : null,
+        name: row?.name ? String(row.name) : null,
+      }));
+
     const subscriptionByExternalId = new Map<
       string,
-      { id: string; status: string | null }
+      {
+        id: string;
+        pleskSubscriptionId: string;
+        status: string | null;
+        name: string | null;
+      }
+    >(subscriptionsIndexed.map((row) => [row.pleskSubscriptionId, row]));
+
+    const subscriptionByLocalId = new Map<
+      string,
+      {
+        id: string;
+        pleskSubscriptionId: string;
+        status: string | null;
+        name: string | null;
+      }
+    >(subscriptionsIndexed.map((row) => [row.id, row]));
+
+    const subscriptionByName = new Map<
+      string,
+      {
+        id: string;
+        pleskSubscriptionId: string;
+        status: string | null;
+        name: string | null;
+      }
     >(
-      (localSubscriptions ?? [])
+      subscriptionsIndexed
-        .filter((row: any) => row?.plesk_subscription_id && row?.id)
+        .filter((row) => row.name)
-        .map((row: any) => [
+        .map((row) => [String(row.name).trim().toLowerCase(), row]),
-          String(row.plesk_subscription_id),
-          {
-            id: String(row.id),
-            status: row?.status ? String(row.status) : null,
-          },
-        ]),
     );
 
     const domainsForUpsert = domainsRaw
@@ -150,12 +222,37 @@ export async function syncPleskInstance(
         const domainName = item?.name ?? item?.domain_name ?? item?.domain;
         const subExternalId =
           item?.subscription?.id ?? item?.subscription_id ?? item?.webspace_id;
-        const externalSubscriptionId = subExternalId
+        const rawExternalSubscriptionId = subExternalId
           ? String(subExternalId)
           : null;
-        const localSubscription = externalSubscriptionId
+        const subscriptionNameHint =
-          ? subscriptionByExternalId.get(externalSubscriptionId)
+          item?.subscription?.name ??
+          item?.subscription_name ??
+          item?.webspace_name ??
+          item?.webspace ??
+          null;
+
+        const localSubscriptionByExternal = rawExternalSubscriptionId
+          ? subscriptionByExternalId.get(rawExternalSubscriptionId)
           : undefined;
+        const localSubscriptionByLocalId = rawExternalSubscriptionId
+          ? subscriptionByLocalId.get(rawExternalSubscriptionId)
+          : undefined;
+        const localSubscriptionByName = subscriptionNameHint
+          ? subscriptionByName.get(
+              String(subscriptionNameHint).trim().toLowerCase(),
+            )
+          : undefined;
+        const localSubscription =
+          localSubscriptionByExternal ??
+          localSubscriptionByLocalId ??
+          localSubscriptionByName;
+
+        const externalSubscriptionId = localSubscription
+          ? localSubscription.pleskSubscriptionId
+          : subscriptionNameHint
+            ? String(subscriptionNameHint)
+            : rawExternalSubscriptionId;
         const rawCreated = item?.created_at ?? item?.created;
         const sourceCreatedAt = rawCreated
           ? new Date(String(rawCreated)).toISOString()
@@ -221,6 +318,40 @@ export async function syncPleskInstance(
       },
     });
 
+    await logStatusSyncEvent("subscription_status_sync_completed", {
+      synced_subscriptions: subscriptionsRaw.length,
+      failed_subscriptions: subscriptionStatusSyncFailures.length,
+      failed_subscription_ids: subscriptionStatusSyncFailures
+        .slice(0, 50)
+        .map((item: any) =>
+          String(
+            item?.plesk_subscription_id ?? item?.subscription_id ?? "unknown",
+          ),
+        ),
+      completed_at: new Date().toISOString(),
+    });
+
+    if (subscriptionStatusSyncFailures.length > 0) {
+      await logStatusSyncEvent(
+        "subscription_status_sync_failed",
+        {
+          failed_subscriptions: subscriptionStatusSyncFailures.length,
+          sample_errors: subscriptionStatusSyncFailures
+            .slice(0, 20)
+            .map((item: any) => ({
+              subscription: String(
+                item?.plesk_subscription_id ??
+                  item?.subscription_id ??
+                  "unknown",
+              ),
+              error: String(item?.status_sync_error ?? "status_sync_failed"),
+            })),
+        },
+        "failed",
+        `${subscriptionStatusSyncFailures.length} subscription status sync failures`,
+      );
+    }
+
     return {
       instanceId: instance.id,
       agencyId: instance.agency_id,
@@ -232,6 +363,15 @@ export async function syncPleskInstance(
     const message =
       error instanceof Error ? error.message.slice(0, 1000) : "Sync failed";
 
+    await logStatusSyncEvent(
+      "subscription_status_sync_failed",
+      {
+        failed_at: new Date().toISOString(),
+      },
+      "failed",
+      message,
+    );
+
     await supabase
       .from("plesk_instances")
       .update({

supabase/migrations/CL4_subscription_status_fields.sql

@@ -0,0 +1,8 @@
+alter table public.plesk_subscriptions
+add column if not exists status text default 'unknown',
+add column if not exists status_raw text,
+add column if not exists last_status_sync_at timestamptz;
+
+update public.plesk_subscriptions
+set status = 'unknown'
+where status is null;