On-chain SQL functions

KuraSQL functions have an execution target. The default, EXECUTION GATEWAY, is ordinary SQL RPC: it is the right choice for relational queries, RLS, aggregates and other work whose EVM gas cannot be bounded. It is not the only function model.

EXECUTION ON CHAIN compiles a deliberately bounded SQL subset into immutable KuraIR bytecode inside the instance contract. Anyone can then call the declared EVM ABI selector directly; no gateway RPC is involved at execution time.

CREATE TABLE token_balances (
  account address PRIMARY KEY,
  balance uint256 NOT NULL
);

INSERT INTO token_balances VALUES
  ('0x0000000000000000000000000000000000000001', 100);

CREATE FUNCTION public.get_balance(owner address)
RETURNS uint256
LANGUAGE sql
EXECUTION ON CHAIN
READS SQL DATA
EXTERNAL NAME 'balanceOf(address)'
AS $$
  SELECT balance FROM token_balances WHERE account = owner
$$;

Run it through the normal migration path:

pnpm kura migration up --dir supabase/migrations

The privileged migration transaction records the SQL catalog update, publishes the selector and KuraIR program, initializes the relation, and seals it as one EVM transaction. A gateway cannot publish a function without its matching SQL catalog record, or change a sealed relation afterwards.

The example is externally callable at the instance address:

import { Contract, JsonRpcProvider } from 'ethers'

const abi = ['function balanceOf(address) view returns (uint256)']
const token = new Contract(instanceAddress, abi, new JsonRpcProvider(rpcUrl))
const balance = await token.balanceOf(account)

Current bounded profile

The first profile accepts one scalar point lookup exactly of the form SELECT value FROM relation WHERE key = function_parameter. The relation must contain exactly an address primary key and one uint256 value column. It has no loops, scans, joins, aggregates, dynamic ABI values, or table-returning results. The EXTERNAL NAME ABI signature and SQL parameter types must match.

When the function is installed, its existing SQL rows are copied into the contract and sealed. Subsequent ordinary REST/SQL writes to that relation are rejected: the published ABI function is the write authority. Gateway projections replay the contract's OnchainValueChanged events, so normal REST and SQL reads reflect externally invoked functions.

The first mutation profile supports a caller debit and address-parameter credit in the same relation. kura.caller() is lowered to EVM msg.sender, and the compiler emits the balance assertion before subtraction:

CREATE FUNCTION public.move_balance(to_address address, amount uint256)
RETURNS bool
LANGUAGE sql
EXECUTION ON CHAIN
MODIFIES SQL DATA
EXTERNAL NAME 'moveBalance(address,uint256)'
AS $$
  UPDATE token_balances
  SET balance = balance - amount
  WHERE account = kura.caller();
  UPDATE token_balances
  SET balance = balance + amount
  WHERE account = to_address;
  SELECT true
$$;

This profile is useful for custom application assets; its selector does not need an extension.

ERC-20 extension

kura_erc20 is an optional ERC-165 capability package. It does not create a gateway-side token implementation and it does not restrict the platform to standard interfaces: any separately declared EXECUTION ON CHAIN function is still directly callable at the instance ABI. The extension only advertises a complete, independently useful ERC-20 selector set.

Declare the three sealed relations and all six functions in the same migration (or bind a previously published compatible set), then name the functions explicitly:

CREATE TABLE token_supply (
  anchor address PRIMARY KEY,
  total uint256 NOT NULL
);
CREATE TABLE token_balances (
  account address PRIMARY KEY,
  balance uint256 NOT NULL
);
CREATE TABLE token_allowances (
  owner address NOT NULL,
  spender address NOT NULL,
  allowance uint256 NOT NULL,
  PRIMARY KEY (owner, spender)
);

CREATE FUNCTION public.total_supply() RETURNS uint256 LANGUAGE sql
EXECUTION ON CHAIN READS SQL DATA EXTERNAL NAME 'totalSupply()' AS $$
  SELECT total FROM token_supply
  WHERE anchor = '0x0000000000000000000000000000000000000000'
$$;
CREATE FUNCTION public.balance_of(owner address) RETURNS uint256 LANGUAGE sql
EXECUTION ON CHAIN READS SQL DATA EXTERNAL NAME 'balanceOf(address)' AS $$
  SELECT balance FROM token_balances WHERE account = owner
$$;
CREATE FUNCTION public.transfer(to_address address, amount uint256) RETURNS bool LANGUAGE sql
EXECUTION ON CHAIN MODIFIES SQL DATA EXTERNAL NAME 'transfer(address,uint256)' AS $$
  UPDATE token_balances SET balance = balance - amount WHERE account = kura.caller();
  UPDATE token_balances SET balance = balance + amount WHERE account = to_address;
  SELECT true
$$;
CREATE FUNCTION public.allowance(owner_address address, spender_address address) RETURNS uint256 LANGUAGE sql
EXECUTION ON CHAIN READS SQL DATA EXTERNAL NAME 'allowance(address,address)' AS $$
  SELECT allowance FROM token_allowances
  WHERE owner = owner_address AND spender = spender_address
$$;
CREATE FUNCTION public.approve(spender_address address, amount uint256) RETURNS bool LANGUAGE sql
EXECUTION ON CHAIN MODIFIES SQL DATA EXTERNAL NAME 'approve(address,uint256)' AS $$
  UPDATE token_allowances SET allowance = amount
  WHERE owner = kura.caller() AND spender = spender_address;
  SELECT true
$$;
CREATE FUNCTION public.transfer_from(from_address address, to_address address, amount uint256) RETURNS bool LANGUAGE sql
EXECUTION ON CHAIN MODIFIES SQL DATA EXTERNAL NAME 'transferFrom(address,address,uint256)' AS $$
  UPDATE token_allowances SET allowance = allowance - amount
  WHERE owner = from_address AND spender = kura.caller();
  UPDATE token_balances SET balance = balance - amount WHERE account = from_address;
  UPDATE token_balances SET balance = balance + amount WHERE account = to_address;
  SELECT true
$$;

CREATE EXTENSION kura_erc20;
ALTER EXTENSION kura_erc20 SET total_supply_function = 'public.total_supply';
ALTER EXTENSION kura_erc20 SET balance_of_function = 'public.balance_of';
ALTER EXTENSION kura_erc20 SET transfer_function = 'public.transfer';
ALTER EXTENSION kura_erc20 SET allowance_function = 'public.allowance';
ALTER EXTENSION kura_erc20 SET approve_function = 'public.approve';
ALTER EXTENSION kura_erc20 SET transfer_from_function = 'public.transfer_from';

The publisher checks each exact ABI selector, return type, and SQL execution mode. If any binding is absent or incompatible, the catalog entry remains incomplete and the instance does not advertise ERC-165 support. Once complete, the catalog update, all newly published programs, three relation seeds/seals, and the interface capability install happen in one privileged transaction.

allowance maps the (owner, spender) primary-key pair to keccak256(abi.encode(owner, spender)) in the contract. approve emits the standard Approval event. transfer and transferFrom validate their debit before updating state and emit the standard Transfer event. A successful transferFrom decrements allowance and moves both balances in one immutable program; a failed allowance or balance assertion reverts the entire call.

The bounded compiler deliberately accepts the shown shapes only. It rejects unbounded queries, arbitrary expressions, fee logic, mint/burn, dynamic ABI values, and external calls rather than falling back to gateway execution.

Dashboard > Functions is a read-only capability view. It reads the canonical catalog to show each published direct ABI signature and whether an extension has every required binding. It never holds a signer or creates a second function registry; authoring remains a reviewed SQL migration.

V2 requirement

On-chain functions require a KurabaseSchemaV2 instance. Fresh kura start projects deploy V2. A saved V1 local instance keeps working for its existing features, but cannot accept this migration; back up any needed local data and run pnpm kura db reset --yes, then pnpm kura start. Remote deployment must use the default V2 artifact or explicitly point KURA_ARTIFACT_FILE to a V2 artifact.