Claude
85a3359186
Co-Authored-By: Claude Sonnet 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_01JXVi2GMQ7bR5EmbUFdDj7N
76 lines
2.9 KiB
SQL
76 lines
2.9 KiB
SQL
-- Run once by scripts/pg-up.sh as the superuser. Deterministic data: no random().
|
|
CREATE TABLE customers (
|
|
id bigint PRIMARY KEY,
|
|
name text NOT NULL,
|
|
email text NOT NULL,
|
|
country text NOT NULL,
|
|
created_at date NOT NULL
|
|
);
|
|
CREATE TABLE products (
|
|
id bigint PRIMARY KEY,
|
|
name text NOT NULL,
|
|
category text NOT NULL,
|
|
price numeric(10,2) NOT NULL
|
|
);
|
|
CREATE TABLE orders (
|
|
id bigint PRIMARY KEY,
|
|
customer_id bigint NOT NULL REFERENCES customers(id),
|
|
status text NOT NULL CHECK (status IN ('paid','shipped','refunded','cancelled')),
|
|
ordered_at date NOT NULL
|
|
);
|
|
CREATE TABLE order_items (
|
|
order_id bigint NOT NULL REFERENCES orders(id),
|
|
product_id bigint NOT NULL REFERENCES products(id),
|
|
quantity int NOT NULL,
|
|
PRIMARY KEY (order_id, product_id)
|
|
);
|
|
-- A table the assistant must never see.
|
|
CREATE TABLE api_keys (
|
|
id serial PRIMARY KEY,
|
|
owner text NOT NULL,
|
|
secret text NOT NULL
|
|
);
|
|
|
|
COMMENT ON TABLE customers IS 'one row per customer';
|
|
COMMENT ON COLUMN customers.country IS 'ISO country code: US, DE, IN, GB or FR';
|
|
COMMENT ON TABLE products IS 'one row per product';
|
|
COMMENT ON COLUMN products.category IS 'one of: coffee, tea, gear';
|
|
COMMENT ON COLUMN products.price IS 'unit price in USD';
|
|
COMMENT ON TABLE orders IS 'one row per order; money lives in order_items x products.price';
|
|
COMMENT ON COLUMN orders.status IS 'one of: paid, shipped, refunded, cancelled';
|
|
COMMENT ON TABLE order_items IS 'one row per product on an order';
|
|
|
|
INSERT INTO customers
|
|
SELECT i, 'Customer ' || i, 'c' || i || '@example.test',
|
|
(ARRAY['US','DE','IN','GB','FR'])[1 + i % 5], DATE '2024-01-01' + (i % 300)
|
|
FROM generate_series(1, 200) i;
|
|
|
|
INSERT INTO products
|
|
SELECT i, 'Product ' || i, (ARRAY['coffee','tea','gear'])[1 + i % 3], 5 + i
|
|
FROM generate_series(1, 20) i;
|
|
|
|
-- customers 191..200 never order, so "customers with no orders" has an answer
|
|
INSERT INTO orders
|
|
SELECT i, 1 + (i * 7) % 190, (ARRAY['paid','shipped','refunded','cancelled'])[1 + i % 4],
|
|
DATE '2025-01-01' + (i % 365)
|
|
FROM generate_series(1, 1000) i;
|
|
|
|
INSERT INTO order_items
|
|
SELECT i, 1 + (i * 3) % 20, 1 + i % 3 FROM generate_series(1, 1000) i;
|
|
INSERT INTO order_items
|
|
SELECT i, 1 + (i * 3 + 7) % 20, 1 + (i + 1) % 3 FROM generate_series(1, 1000) i;
|
|
INSERT INTO order_items
|
|
SELECT i, 1 + (i * 3 + 13) % 20, 2 FROM generate_series(1, 1000) i WHERE i % 2 = 0;
|
|
|
|
INSERT INTO api_keys (owner, secret) VALUES ('billing', 'sk-live-0000-not-a-real-key');
|
|
|
|
-- The role the assistant connects as.
|
|
CREATE ROLE t2s_reader LOGIN PASSWORD 'reader' NOSUPERUSER NOCREATEDB NOCREATEROLE;
|
|
GRANT CONNECT ON DATABASE t2s TO t2s_reader;
|
|
GRANT USAGE ON SCHEMA public TO t2s_reader;
|
|
-- customers.email is personal data: the assistant gets column-level access without it
|
|
GRANT SELECT (id, name, country, created_at) ON customers TO t2s_reader;
|
|
GRANT SELECT ON products, orders, order_items TO t2s_reader;
|
|
ALTER ROLE t2s_reader SET default_transaction_read_only = on;
|
|
ALTER ROLE t2s_reader SET statement_timeout = '2s';
|