Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

Roll Up Daily Usage per Tenant in Neki with Co-Located Writes

Co-locate a usage_events table and a daily_usage summary table on tenant_id in a Neki data topology, then run an INSERT ... SELECT rollup and use EXPLAIN (NEKI_PLAN) to verify that every tenant's summary write routes to exactly one shard.

What you will build

A TypeScript workflow that rolls up daily usage events per tenant into a co-located summary table on Neki and proves each write stays shard-local.

Configure data topology -> create usage_events and daily_usage tables -> insert sample events -> EXPLAIN (NEKI_PLAN) the rollup -> run INSERT ... SELECT rollup -> read back the daily summary.

AI prompt

Build a TypeScript workflow that rolls up daily usage per tenant into a summary table on Neki. Configure the data topology so both source and summary tables share a tenant_id shard index, run the rollup against a live branch, and verify with EXPLAIN (NEKI_PLAN) that each write stays on one server. Document any limits you hit.

Prerequisites

  • Node.js 22 or later
  • A Neki database branch (PlanetScale platform preview)
  • A Neki role that inherits postgres so it can run DDL and call __neki.set_data_topology
  • The PlanetScale CLI (pscale) installed if you create the role from the terminal

The topology primitives used below (shard_indexes, shard_groups, __neki.set_data_topology, __neki.wait_for_data_topology) are described in the data-topology guide; the single-shard routing behaviour that EXPLAIN (NEKI_PLAN) surfaces is covered in the shard-targeting / fan-out guide and the query-planning guide; the Node-driver connection flow follows the Node guide and the connecting guide; the table shapes echo the example schema.

Environment variables

  • NEKI_DATABASE_URL (secret): Primary connection URI for the Neki branch, from the PlanetScale dashboard or pscale role create

1. Create the project and install dependencies

Initialize a Node project, add the Postgres driver, and add tsx and TypeScript so you can run .ts files directly.

mkdir neki-tenant-rollup && cd neki-tenant-rollup
npm init -y
npm install pg@8.13.1 dotenv@16.4.7
npm install -D tsx@4.19.2 typescript@5.7.2 @types/pg@8.11.10

2. Configure package scripts

Add the run scripts and set the project type to module so TypeScript files are treated as ESM.

{
  "name": "neki-tenant-rollup-example",
  "version": "1.0.0",
  "description": "Roll up daily usage per tenant in Neki with co-located writes",
  "type": "module",
  "scripts": {
    "setup:topology": "tsx src/setup-topology.ts",
    "rollup": "tsx src/rollup.ts"
  },
  "dependencies": {
    "dotenv": "16.4.7",
    "pg": "8.13.1"
  },
  "devDependencies": {
    "@types/pg": "8.11.10",
    "tsx": "4.19.2",
    "typescript": "5.7.2"
  }
}

3. Add the topology setup script

Create src/setup-topology.ts. It reads the current data topology, adds a xxhash_tenant_id shard index, adds a tenant_data shard group that reuses an existing sharded group's key ranges, and binds usage_events and daily_usage to that group. The change is applied with __neki.set_data_topology and then waited on with __neki.wait_for_data_topology so every router sees it.

import pg from 'pg';
const { Client } = pg;

interface DataTopology {
  shard_indexes: Record<string, { type: string; columns: string[] }>;
  shard_groups: Array<{
    uid: string;
    default_shard_index?: string;
    key_ranges: Array<{ shard_uid: string; start?: string; end?: string }>;
  }>;
  databases: Record<string, {
    schemas: Record<string, {
      tables: Record<string, { shard_group: string }>;
    }>;
  }>;
  authoritative_shard_group: string;
}

async function main() {
  const databaseUrl = process.env.NEKI_DATABASE_URL;
  if (!databaseUrl) {
    throw new Error('NEKI_DATABASE_URL is required');
  }

  const client = new Client({ connectionString: databaseUrl });
  await client.connect();

  try {
    const current = await client.query<{ data_topology_json: string }>(
      'SELECT * FROM __neki.get_data_topology()'
    );
    const topology: DataTopology = JSON.parse(current.rows[0].data_topology_json);

    if (!topology.shard_indexes.xxhash_tenant_id) {
      topology.shard_indexes.xxhash_tenant_id = {
        type: 'xxhash',
        columns: ['tenant_id'],
      };
    }

    const donorGroup = topology.shard_groups.find(
      (g) => g.uid !== topology.authoritative_shard_group && g.key_ranges.length > 1
    );
    if (!donorGroup) {
      throw new Error('No existing sharded group found to reuse shard ranges');
    }

    if (!topology.shard_groups.some((g) => g.uid === 'tenant_data')) {
      topology.shard_groups.push({
        uid: 'tenant_data',
        default_shard_index: 'xxhash_tenant_id',
        key_ranges: donorGroup.key_ranges.map((r) => ({ ...r })),
      });
    }

    const postgresDb = topology.databases.postgres ?? Object.values(topology.databases)[0];
    postgresDb.schemas.public.tables.usage_events = { shard_group: 'tenant_data' };
    postgresDb.schemas.public.tables.daily_usage = { shard_group: 'tenant_data' };

    const result = await client.query<{ success: boolean; revision: string }>(
      'SELECT * FROM __neki.set_data_topology($1, true, $2)',
      [
        JSON.stringify(topology),
        JSON.stringify({ comment: 'Co-locate usage_events and daily_usage on tenant_id' }),
      ]
    );
    console.log('Topology updated:', result.rows[0]);

    await client.query('SELECT __neki.wait_for_data_topology($1)', [result.rows[0].revision]);
    console.log('Topology visible on all routers');
  } finally {
    await client.end();
  }
}

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

4. Add the rollup script

Create src/rollup.ts. It creates the source and summary tables (both keyed by tenant_id), seeds sample events, explains the rollup plan, runs the rollup, and reads back the summary table.

import pg from 'pg';
const { Client } = pg;

interface DailyUsage {
  tenant_id: string;
  usage_date: Date;
  total_units: string;
  event_count: string;
}

async function main() {
  const databaseUrl = process.env.NEKI_DATABASE_URL;
  if (!databaseUrl) {
    throw new Error('NEKI_DATABASE_URL is required');
  }

  const client = new Client({ connectionString: databaseUrl });
  await client.connect();

  try {
    await client.query(`
      CREATE TABLE IF NOT EXISTS public.usage_events (
        event_id BIGSERIAL PRIMARY KEY,
        tenant_id BIGINT NOT NULL,
        event_time TIMESTAMP NOT NULL DEFAULT NOW(),
        units BIGINT NOT NULL
      )
    `);
    await client.query(`
      CREATE TABLE IF NOT EXISTS public.daily_usage (
        tenant_id BIGINT NOT NULL,
        usage_date DATE NOT NULL,
        total_units BIGINT NOT NULL,
        event_count BIGINT NOT NULL,
        PRIMARY KEY (tenant_id, usage_date)
      )
    `);
    await client.query(`
      CREATE INDEX IF NOT EXISTS usage_events_tenant_time_idx
      ON public.usage_events (tenant_id, event_time)
    `);
    console.log('Tables ready');

    await client.query('DELETE FROM public.usage_events');
    await client.query('DELETE FROM public.daily_usage');
    await client.query(`
      INSERT INTO public.usage_events (tenant_id, event_time, units)
      VALUES
        (1, '2026-11-01 08:00:00', 10),
        (1, '2026-11-01 12:00:00', 20),
        (1, '2026-11-02 09:00:00', 15),
        (2, '2026-11-01 10:00:00', 5),
        (2, '2026-11-01 14:00:00', 7),
        (2, '2026-11-02 11:00:00', 3),
        (3, '2026-11-01 07:00:00', 100),
        (3, '2026-11-02 13:00:00', 200)
    `);
    console.log('Sample events loaded');

    const rollupSql = `
      INSERT INTO public.daily_usage (tenant_id, usage_date, total_units, event_count)
      SELECT tenant_id, DATE(event_time), SUM(units), COUNT(*)
      FROM public.usage_events
      WHERE event_time >= '2026-11-01' AND event_time < '2026-11-02'
      GROUP BY tenant_id, DATE(event_time)
      RETURNING tenant_id, usage_date, total_units, event_count
    `;

    const explain = await client.query<{ 'QUERY PLAN': unknown }>(
      `EXPLAIN (NEKI_PLAN, FORMAT JSON) ${rollupSql}`
    );
    console.log('\nNeki plan for the rollup:');
    console.log(JSON.stringify(explain.rows[0]['QUERY PLAN'], null, 2));

    const result = await client.query<DailyUsage>(rollupSql);
    console.log('\nRollup rows written:');
    console.log(result.rows);

    const summary = await client.query<DailyUsage>(
      'SELECT tenant_id, usage_date, total_units, event_count FROM public.daily_usage ORDER BY tenant_id, usage_date'
    );
    console.log('\nDaily usage summary:');
    console.log(summary.rows);
  } finally {
    await client.end();
  }
}

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

5. Run the verified workflow

Make sure NEKI_DATABASE_URL is exported in your shell, then run the topology setup once and the rollup workflow.

npm run setup:topology
npm run rollup

Verify the result

Run against Neki platform preview on 2026-11-05.

npm run setup:topology && npm run rollup

Observed output:

> neki-tenant-rollup-example@1.0.0 setup:topology
> tsx src/setup-topology.ts

Topology updated: { success: true, revision: '147302' }
Topology visible on all routers

> neki-tenant-rollup-example@1.0.0 rollup
> tsx src/rollup.ts

Tables ready
Sample events loaded

Neki plan for the rollup:
[
  {
    "Plan": {
      "Node Type": "Collapse",
      "Startup Cost": 0,
      "Total Cost": 13133,
      "Plan Rows": 11,
      "Plan Width": 28,
      "Cost": {
        "Subtree": {
          "NetworkRows": 22,
          "RemoteCalls": 13,
          "RowsProcessed": 11,
          "Total": 13133
        }
      },
      "Plans": [
        {
          "Node Type": "Join",
          "Variant": "NestedLoop",
          "Join Type": "Inner",
          "Startup Cost": 0,
          "Total Cost": 13133,
          "Plan Rows": 11,
          "Plan Width": 28,
          "JoinBindParams": "[0, 1, 2, 3]",
          "OutputColumns": "[R:0,R:1,R:2,R:3]",
          "Cost": {
            "Self": {
              "RowsProcessed": 11,
              "Total": 0
            },
            "Subtree": {
              "NetworkRows": 22,
              "RemoteCalls": 13,
              "RowsProcessed": 11,
              "Total": 13133
            }
          },
          "Plans": [
            {
              "Node Type": "Route",
              "Parent Relationship": "Outer",
              "Variant": "Scatter",
              "Startup Cost": 0,
              "Total Cost": 2011,
              "Plan Rows": 11,
              "Plan Width": 52,
              "ColumnOutputMapping": "[-1, -1, -1, -1]",
              "OuterParams": "[0, 1]",
              "ParamTypes": "[text, text]",
              "Query": "SELECT usage_events.tenant_id, DATE(usage_events.event_time), SUM(units), COUNT(*) FROM public.usage_events WHERE event_time >= $1::timestamp without time zone AND event_time < $2::timestamp without time zone GROUP BY 1, 2",
              "ShardGroupUid": "tenant_data",
              "Cost": {
                "Subtree": {
                  "NetworkRows": 11,
                  "RemoteCalls": 2,
                  "Total": 2011
                }
              }
            },
            {
              "Node Type": "Insert",
              "Parent Relationship": "Inner",
              "Variant": "Values",
              "Startup Cost": 0,
              "Total Cost": 1001,
              "Plan Rows": 1,
              "Plan Width": 28,
              "ColumnOutputMapping": "[0, 1, 2, 3]",
              "Indexes": [
                "xxhash_tenant_id on [tenant_id]"
              ],
              "InnerParams": "[0, 1, 2, 3]",
              "OpCode": "Sharded",
              "ParamTypes": "[bigint, date, numeric, bigint]",
              "Query": "INSERT INTO public.daily_usage (tenant_id, usage_date, total_units, event_count) VALUES ($1, $2, $3, $4) RETURNING tenant_id, usage_date, total_units, event_count",
              "ShardGroupUid": "tenant_data",
              "Values": "[[$1]]",
              "Cost": {
                "Subtree": {
                  "NetworkRows": 1,
                  "RemoteCalls": 1,
                  "Total": 1001
                }
              }
            }
          ]
        }
      ]
    }
  }
]

Rollup rows written:
[
  {
    tenant_id: '1',
    usage_date: 2026-11-01T00:00:00.000Z,
    total_units: '30',
    event_count: '2'
  },
  {
    tenant_id: '3',
    usage_date: 2026-11-01T00:00:00.000Z,
    total_units: '100',
    event_count: '1'
  },
  {
    tenant_id: '2',
    usage_date: 2026-11-01T00:00:00.000Z,
    total_units: '12',
    event_count: '2'
  }
]

Daily usage summary:
[
  {
    tenant_id: '1',
    usage_date: 2026-11-01T00:00:00.000Z,
    total_units: '30',
    event_count: '2'
  },
  {
    tenant_id: '2',
    usage_date: 2026-11-01T00:00:00.000Z,
    total_units: '12',
    event_count: '2'
  },
  {
    tenant_id: '3',
    usage_date: 2026-11-01T00:00:00.000Z,
    total_units: '100',
    event_count: '1'
  }
]

Expected: The rollup writes three tenant summary rows, and EXPLAIN (NEKI_PLAN) shows the INSERT is OpCode Sharded using xxhash_tenant_id so each tenant's write routes to exactly one shard.

How it works

Neki routes rows by a shard key. By binding both usage_events and daily_usage to the same tenant_data shard group and the same xxhash_tenant_id index, every row for a tenant lives on the same Postgres shard. When the rollup runs an INSERT ... SELECT grouped by tenant_id, the SELECT scatters to all shards to read each tenant's events, but the INSERT plan is Sharded and uses xxhash_tenant_id to route each resulting summary row back to the single shard that owns that tenant. EXPLAIN (NEKI_PLAN) exposes this routing explicitly.

What the run corrected

These are beliefs the documentation supported and the run disproved.

  • Assumed: INSERT ... SELECT with ON CONFLICT DO UPDATE is the right idempotent rollup pattern in Neki. Observed: Neki rejects INSERT ... SELECT ... ON CONFLICT DO UPDATE with NK013 code 117 ('not yet supported'). Evidence: The live run failed with 'not implemented: [117] INSERT ... SELECT ... ON CONFLICT DO UPDATE is not yet supported'; a plain INSERT ... SELECT succeeded.

  • Assumed: To keep every rollup write on one server, the SELECT part of the query must also be single-shard. Observed: The SELECT can scatter across the tenant_data group, but the INSERT is still OpCode Sharded and routes each aggregate row to exactly one shard based on tenant_id. Evidence: EXPLAIN (NEKI_PLAN) showed Route Variant Scatter for the aggregate read and Insert OpCode Sharded with Indexes ['xxhash_tenant_id on [tenant_id]'] for the write.

  • Assumed: pscale connect can create a local proxy for Neki the same way it does for PlanetScale MySQL. Observed: pscale connect is only supported for Vitess databases; Neki credentials are created with pscale role create or the dashboard. Evidence: pscale password create returned 'This endpoint is only available for Vitess databases. Use pscale role to create credentials for a Postgres database.'

Common issues

INSERT ... SELECT ... ON CONFLICT DO UPDATE fails with NK013 code 117

Cause: Neki does not yet support ON CONFLICT DO UPDATE inside an INSERT ... SELECT statement.

Fix: Use plain INSERT ... SELECT and remove previous summaries for the same window first, or split the work into per-tenant statements.

pscale connect returns an error for a Neki database

Cause: pscale connect is only implemented for Vitess databases.

Fix: Use pscale role create or the PlanetScale dashboard Connect page to get a Neki Postgres URI.

Rollup writes are routed to more than one shard per tenant

Cause: The source and summary tables are not in the same shard group or do not share the same shard index on tenant_id.

Fix: Bind both tables to the same shard group and use the same xxhash_tenant_id index, then confirm with EXPLAIN (NEKI_PLAN).

Topology change succeeds but later queries still use the old placement

Cause: Other routers have not loaded the new topology yet.

Fix: Call __neki.wait_for_data_topology(<revision>) after __neki.set_data_topology returns.

Next steps

  • Add a tenant_id = $1 predicate to make both the read and the write single-shard for per-tenant rollup jobs
  • Make the rollup idempotent by deleting the previous day's summary for the window before inserting, since INSERT ... SELECT ... ON CONFLICT DO UPDATE is not yet supported
  • Run the rollup on a schedule and pass the date window as query parameters instead of hard-coding dates
  • Set __neki.fanout = 'single' for request paths that must never scatter, catching any topology drift that would break the single-server guarantee
verification:
  status: verified
  tested_at: "2026-11-05"
  product_version: "Neki platform preview"
  command: "apply the tenant_id shard topology, insert sample usage_events rows for three tenants, run the INSERT ... SELECT rollup into daily_usage, and inspect EXPLAIN (NEKI_PLAN) for the rollup statement"
  expected_result: "rollup writes three per-tenant summary rows; EXPLAIN (NEKI_PLAN) reports the INSERT as OpCode Sharded using xxhash_tenant_id so each tenant's write routes to exactly one shard"