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"