From sqlproof
Write property-based tests for PostgreSQL SQL functions and Supabase RPC functions using sqlproof. Use whenever the user asks to test a `CREATE FUNCTION`, a `LANGUAGE sql` or `LANGUAGE plpgsql` function, a Supabase RPC, an RPC endpoint, or any callable database function. Also use when the task mentions function invariants, aggregation correctness, `db.scalar`, `db.query`, "this function should never return X", or comparing DB output to a Python reference model. This skill covers two test shapes: pure-function property tests (invariants like "never negative", "monotonic in argument N") and aggregation tests that compare the DB result to a Python recomputation across generated datasets. Pairs with the core `sqlproof` skill. Without this skill, generated RPC tests tend to assert specific values against hand-crafted fixtures (the pgTAP style), missing the edge cases — NULLs, decimal precision, empty groups, tied window values — that property-based generation surfaces.
How this skill is triggered — by the user, by Claude, or both
Slash command
/sqlproof:sqlproof-rpc-testingThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Any time the user adds or modifies a `CREATE FUNCTION` in `public.*`,
Any time the user adds or modifies a CREATE FUNCTION in public.*,
or when an existing function's behavior is in question.
For pure-ish functions (no schema state), use a property test that asserts an invariant.
"""Property tests for `compute_order_total`."""
from decimal import Decimal
from hypothesis import HealthCheck, given, settings
from hypothesis import strategies as st
from sqlproof.client import SqlProofClient
PROOF_KW = settings(
max_examples=100,
deadline=None,
suppress_health_check=[HealthCheck.function_scoped_fixture],
)
@PROOF_KW
@given(
subtotal=st.decimals(min_value=Decimal("0"), max_value=Decimal("9999.99"), places=2),
tier=st.sampled_from(["standard", "silver", "gold", "platinum"]),
)
def test_invoice_total_is_never_negative(
db: SqlProofClient, subtotal: Decimal, tier: str,
) -> None:
result = db.scalar(
"SELECT compute_order_total(%s::numeric, %s)",
subtotal, tier,
)
assert result >= 0
@PROOF_KW
@given(
subtotal=st.decimals(min_value=Decimal("1"), max_value=Decimal("1000"), places=2),
)
def test_higher_tier_never_costs_more_than_lower_tier(
db: SqlProofClient, subtotal: Decimal,
) -> None:
standard = db.scalar("SELECT compute_order_total(%s, 'standard')", subtotal)
platinum = db.scalar("SELECT compute_order_total(%s, 'platinum')", subtotal)
assert platinum <= standard, (
f"platinum ({platinum}) costs more than standard ({standard})"
)
Use db (not supabase_db) if the function doesn't depend on
auth state.
For functions that aggregate over rows (SUM, COUNT,
get_dashboard_summary, etc.), generate the dataset and reconcile
the DB result against a Python recomputation.
@given(data=st.data(), event_count=st.integers(min_value=0, max_value=20))
def test_dashboard_summary_event_count_matches_inserted_count(
supabase_proof, data, event_count: int,
):
dataset = data.draw(supabase_proof.dataset_strategy(
sizes={"projects": 1, "events": event_count},
))
with supabase_proof.client_for_dataset(dataset) as db:
project_id = dataset["projects"][0]["id"]
payload = db.scalar(
"SELECT get_dashboard_summary(%s::uuid)", project_id
)
assert payload["event_count"] == event_count
The Python recomputation is your oracle — the independent source of truth the DB result is compared against. If you can't articulate an oracle in one sentence, you don't have a property test; write an example test instead.
For "function returns sensible zeros for unknown input", use a SINGLE example test with a literal unknown UUID:
def test_dashboard_summary_returns_zero_shape_for_unknown_project(db):
payload = db.scalar(
"SELECT get_dashboard_summary(%s::uuid)",
"00000000-0000-0000-0000-000000000000",
)
assert payload["totalUsers"] == 0
assert payload["recentEvents"] == []
Don't generate for this — there's only one shape to test.
"X is never negative." "Higher tier never costs more." "Sum of X equals sum of Y." If you can't articulate it in one sentence, write an example test.
db.scalar(...) for single-value returns; db.query(...) for RETURNS TABLEresult = db.scalar("SELECT compute_order_total(...)") # one value
rows = db.query("SELECT * FROM get_orders_for_user(...)") # multi-row
db.scalar("SELECT my_function(%s::uuid, %s::numeric, %s::text[])", ...)
Postgres function overload resolution is strict on parameter types.
If you can't articulate the invariant in one sentence, write an example test instead. False properties (ones the function doesn't actually satisfy) waste shrinking time and produce confusing counterexamples.
Even when you "just need one row." Use dataset_strategy; that one
row should still respect every constraint.
numeric after a refactor.ROW_NUMBER() OVER (ORDER BY ...) that's nondeterministic
on tied values.PARTITION BY that leak across
groups.COALESCE shadowing that hides NULL semantics until you hit
the case.Property-based tests catch these because they generate hundreds of valid datasets, including the one that hits the edge case.
npx claudepluginhub alialavia/sqlproof-skills --plugin sqlproofProvides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Searches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.