hidden11/scripts/setup-metabase-dashboard.mjs

#!/usr/bin/env node
// Creates the hidden11 funnel dashboard in Metabase.
//
// Registers the hidden11 Postgres database (if it is not there yet), creates one
// native question per view in deploy/sql/hidden11-events.sql, and puts them on a
// dashboard. Re-running is safe: existing items are reused, not duplicated.
//
// Usage:
//   MB_URL=https://metabase.alexandre-vazquez.cloud \
//   MB_USER=you@example.com MB_PASSWORD="$(pass show ...)" \
//   PG_PASSWORD="$(pass show hidden11/postgres-password)" \
//   node scripts/setup-metabase-dashboard.mjs

const MB_URL = (process.env.MB_URL ?? "https://metabase.alexandre-vazquez.cloud").replace(/\/$/, "");
const MB_USER = process.env.MB_USER;
const MB_PASSWORD = process.env.MB_PASSWORD;
const PG_HOST = process.env.PG_HOST ?? "192.168.1.29";
const PG_PORT = Number(process.env.PG_PORT ?? 5432);
const PG_DB = process.env.PG_DB ?? "hidden11";
const PG_USER = process.env.PG_USER ?? "hidden11";
const PG_PASSWORD = process.env.PG_PASSWORD;

const DB_NAME = "hidden11";
const DASHBOARD_NAME = "hidden11 — growth funnel";

if (!MB_USER || !MB_PASSWORD) {
  console.error("MB_USER and MB_PASSWORD are required");
  process.exit(1);
}

let sessionToken = "";

async function api(path, { method = "GET", body } = {}) {
  const response = await fetch(`${MB_URL}/api${path}`, {
    method,
    headers: {
      "Content-Type": "application/json",
      ...(sessionToken ? { "X-Metabase-Session": sessionToken } : {}),
    },
    body: body === undefined ? undefined : JSON.stringify(body),
  });

  const text = await response.text();
  if (!response.ok) {
    throw new Error(`${method} ${path} -> ${response.status}: ${text.slice(0, 400)}`);
  }
  return text ? JSON.parse(text) : null;
}

const CARDS = [
  {
    name: "Funnel by day",
    description: "Visitors → started → guessed → completed → shared → challenge accepted.",
    sql: "SELECT * FROM hidden11_funnel_daily ORDER BY day DESC LIMIT 60",
    display: "table",
    size: { col: 0, row: 0, size_x: 24, size_y: 7 },
  },
  {
    name: "Completion and share rate",
    description: "Share of players who finish the day, and of finishers who share.",
    sql: "SELECT day, completion_rate, share_rate FROM hidden11_funnel_daily ORDER BY day DESC LIMIT 60",
    display: "line",
    size: { col: 0, row: 7, size_x: 12, size_y: 6 },
  },
  {
    name: "D1 retention",
    description: "Of the visitors seen on a day, how many came back the next day.",
    sql: "SELECT day, visitors, returned_next_day, d1_retention FROM hidden11_retention_d1 ORDER BY day DESC LIMIT 60",
    display: "line",
    size: { col: 12, row: 7, size_x: 12, size_y: 6 },
  },
  {
    name: "Share channels",
    description: "Which channel players actually use to share.",
    sql: "SELECT channel, sum(shares) AS shares FROM hidden11_share_channels GROUP BY channel ORDER BY shares DESC",
    display: "bar",
    size: { col: 0, row: 13, size_x: 12, size_y: 6 },
  },
  {
    name: "Reminders and installs",
    description: "Opt-in rate for the daily push reminder.",
    sql: "SELECT day, enabled_reminder, visitors FROM hidden11_funnel_daily ORDER BY day DESC LIMIT 60",
    display: "line",
    size: { col: 12, row: 13, size_x: 12, size_y: 6 },
  },
];

async function ensureDatabase() {
  const { data: databases } = await api("/database");
  const existing = databases.find((database) => database.name === DB_NAME);
  if (existing) {
    console.log(`database "${DB_NAME}" already registered (id ${existing.id})`);
    return existing.id;
  }

  if (!PG_PASSWORD) {
    throw new Error("PG_PASSWORD is required to register the database for the first time");
  }

  const created = await api("/database", {
    method: "POST",
    body: {
      name: DB_NAME,
      engine: "postgres",
      details: {
        host: PG_HOST,
        port: PG_PORT,
        dbname: PG_DB,
        user: PG_USER,
        password: PG_PASSWORD,
        ssl: false,
      },
    },
  });
  console.log(`registered database "${DB_NAME}" (id ${created.id})`);
  return created.id;
}

async function ensureCard(databaseId, card) {
  const existing = await api(`/search?q=${encodeURIComponent(card.name)}&models=card`)
    .then((result) => result.data?.find((item) => item.name === card.name))
    .catch(() => null);

  if (existing) {
    console.log(`card "${card.name}" already exists (id ${existing.id})`);
    return existing.id;
  }

  const created = await api("/card", {
    method: "POST",
    body: {
      name: card.name,
      description: card.description,
      display: card.display,
      visualization_settings: {},
      dataset_query: {
        type: "native",
        database: databaseId,
        native: { query: card.sql },
      },
    },
  });
  console.log(`created card "${card.name}" (id ${created.id})`);
  return created.id;
}

async function ensureDashboard() {
  const found = await api(`/search?q=${encodeURIComponent(DASHBOARD_NAME)}&models=dashboard`)
    .then((result) => result.data?.find((item) => item.name === DASHBOARD_NAME))
    .catch(() => null);

  if (found) {
    console.log(`dashboard already exists (id ${found.id})`);
    return found.id;
  }

  const created = await api("/dashboard", {
    method: "POST",
    body: {
      name: DASHBOARD_NAME,
      description: "Visits → game started → completed → shared → challenge accepted, from first-party events.",
    },
  });
  console.log(`created dashboard (id ${created.id})`);
  return created.id;
}

async function main() {
  const session = await api("/session", {
    method: "POST",
    body: { username: MB_USER, password: MB_PASSWORD },
  });
  sessionToken = session.id;
  console.log("authenticated");

  const databaseId = await ensureDatabase();

  const cardIds = [];
  for (const card of CARDS) {
    cardIds.push({ id: await ensureCard(databaseId, card), card });
  }

  const dashboardId = await ensureDashboard();
  const dashboard = await api(`/dashboard/${dashboardId}`);
  const placed = new Set((dashboard.dashcards ?? []).map((dashcard) => dashcard.card_id));

  const dashcards = [
    ...(dashboard.dashcards ?? []),
    ...cardIds
      .filter(({ id }) => !placed.has(id))
      .map(({ id, card }, index) => ({
        id: -(index + 1), // negative ids mark newly added cards
        card_id: id,
        ...card.size,
      })),
  ];

  await api(`/dashboard/${dashboardId}`, { method: "PUT", body: { dashcards } });
  console.log(`\ndone: ${MB_URL}/dashboard/${dashboardId}`);
}

main().catch((error) => {
  console.error(error.message);
  process.exit(1);
});