supabase__supabase/apps/studio/lib/warehouse.ts

import { literal } from '@supabase/pg-meta'

import { PASSWORD_PLACEHOLDER } from '@/components/interfaces/ConnectSheet/ConnectionString.utils'
import { IS_STAGING_OR_LOCAL } from '@/lib/constants'

const WAREHOUSE_TLD = IS_STAGING_OR_LOCAL ? 'red' : 'io'

/**
 * Name of the singleton replication publication (and destination) that Warehouse manages. Its table
 * list is the source of truth for what's currently replicated.
 */
export const WAREHOUSE_PUBLICATION_NAME = 'supabase_warehouse'

/**
 * Postgres schema the managed Warehouse destination keeps its DuckLake catalog in.
 * `WAREHOUSE_METADATA_SCHEMA` in the platform repo, where it's a hardcoded constant: the schema is
 * always provisioned under this name, the destination config is always built with it, and no
 * request body accepts an override. Mirrored here so the schema picker can exclude it; the platform
 * also rejects it server-side.
 */
export const WAREHOUSE_METADATA_SCHEMA = 'ducklake'

export function getWarehouseFlightSqlEndpoint(projectRef: string): string {
  return `${projectRef}.warehouse.supabase.${WAREHOUSE_TLD}`
}

export function getWarehouseFlightSqlConnectionString(projectRef: string): string {
  const endpoint = getWarehouseFlightSqlEndpoint(projectRef)
  // The password is the project's database password. It's never fetched or displayed here --
  // mirroring how the direct-connection tab shows a placeholder instead of the real secret.
  return `flightsql://postgres:${PASSWORD_PLACEHOLDER}@${endpoint}:443?tls=enabled`
}

export function getWarehouseUsqlCommand(projectRef: string): string {
  const endpoint = getWarehouseFlightSqlEndpoint(projectRef)
  return `usql -X -W 'flightsql://postgres@${endpoint}:443?tls=enabled'`
}

/** Environment variables the DuckLake setup script reads secrets from. */
export const DUCKLAKE_S3_SECRET_ENV_VAR = 'DUCKLAKE_S3_SECRET'
export const DUCKLAKE_METADATA_PASSWORD_ENV_VAR = 'DUCKLAKE_METADATA_PASSWORD'

export interface WarehouseCatalogConnection {
  host: string
  hostaddr?: string
  port: string
  database: string
  user: string
  password: string
}

/**
 * Splits the DuckLake catalog Postgres URL into the parts DuckDB's `TYPE postgres` secret expects
 * as individual options. Returns null when the URL can't be parsed, so callers can fall back to
 * surfacing the raw value instead of emitting a broken script.
 */
export function parseWarehouseCatalogUrl(catalogUrl: string): WarehouseCatalogConnection | null {
  try {
    const url = new URL(catalogUrl)
    if (!url.hostname) return null

    const hostaddr = url.searchParams.get('hostaddr')

    return {
      host: url.hostname.replace(/^\[|\]$/g, ''),
      ...(hostaddr ? { hostaddr } : {}),
      port: url.port || '5432',
      database: url.pathname.replace(/^\//, '') || 'postgres',
      user: decodeURIComponent(url.username) || 'postgres',
      password: decodeURIComponent(url.password),
    }
  } catch {
    return null
  }
}

/**
 * Full DuckDB script for attaching the project's Warehouse: an S3 secret for the data files, a
 * Postgres secret for the metadata catalog, a DuckLake secret binding the two, then the attach.
 *
 * Both passwords are read via `getenv()` rather than inlined, so the script is safe to copy into a
 * shared file. The values themselves are surfaced separately in the UI.
 *
 * `METADATA_SCHEMA` is set explicitly because DuckLake defaults it to `main`, not to the schema the
 * platform provisions.
 */
export function getDuckLakeSetupScript({
  credentials,
  connection,
}: {
  credentials: {
    data_path: string
    metadata_schema: string
    s3_access_key_id: string
    s3_endpoint: string
    s3_region: string
  }
  connection: WarehouseCatalogConnection
}): string {
  return `-- S3 credentials for reading the Warehouse data files
CREATE OR REPLACE SECRET ducklake_s3 (
  TYPE s3,
  KEY_ID '${credentials.s3_access_key_id}',
  SECRET getenv('${DUCKLAKE_S3_SECRET_ENV_VAR}'),
  REGION '${credentials.s3_region}',
  ENDPOINT '${credentials.s3_endpoint}',
  URL_STYLE 'path'
);

-- Postgres credentials for the DuckLake metadata catalog
CREATE OR REPLACE SECRET ducklake_metadata (
  TYPE postgres,
  HOST ${literal(connection.host)},${connection.hostaddr ? `\n  HOSTADDR ${literal(connection.hostaddr)},` : ''}
  PORT ${connection.port},
  DATABASE '${connection.database}',
  USER '${connection.user}',
  PASSWORD getenv('${DUCKLAKE_METADATA_PASSWORD_ENV_VAR}')
);

-- Bind the metadata secret into a DuckLake secret configuration
CREATE OR REPLACE SECRET ducklake_warehouse (
  TYPE ducklake,
  METADATA_PATH '',
  DATA_PATH '${credentials.data_path}',
  METADATA_SCHEMA '${credentials.metadata_schema}',
  METADATA_PARAMETERS MAP {
    'TYPE': 'postgres',
    'SECRET': 'ducklake_metadata'
  }
);

-- Attach Warehouse using only the secret identifier
ATTACH 'ducklake:ducklake_warehouse' AS warehouse;`
}