Use AnalyticDB PostgreSQL Query Skill for database query analysis

Method 1: Install through npx (recommended)

In an Agent environment that supports npx, such as Claude Desktop or Qoder, run:

npx skills add https://github.com/aliyun/alibabacloud-aiops-skills/tree/alibabacloud-analyticdb-postgresql-query-0.0.1-beta.1 --skill alibabacloud-analyticdb-postgresql-query

Method 2: Send a link to your Agent

Send any of the following links directly to an Agent that supports Skill installation. The Agent automatically detects and completes the installation:

• GitHub link: https://github.com/aliyun/alibabacloud-aiops-skills/tree/alibabacloud-analyticdb-postgresql-query-0.0.1-beta.1/skills/database/gpdb/alibabacloud-analyticdb-postgresql-query

• Skills.sh link: https://www.skills.sh/aliyun/alibabacloud-aiops-skills/alibabacloud-analyticdb-postgresql-query

• ClawHub link: https://clawhub.ai/sdk-team/skills/alibabacloud-analyticdb-postgresql-query

1. Use a read-only account

Do not use a superuser account to connect. Have your DBA create a dedicated read-only account:

-- DBA executes
CREATE ROLE readonly_agent LOGIN PASSWORD '<strong_password>';
GRANT CONNECT ON DATABASE mydb TO readonly_agent;
GRANT USAGE ON SCHEMA public TO readonly_agent;
-- Grant SELECT only on tables the business requires (do not use GRANT SELECT ON ALL TABLES)
GRANT SELECT ON public.orders, public.customers, public.products TO readonly_agent;

2. Enforce read-only connections

The Skill enforces read-only mode (default_transaction_read_only=on) on every connection, ensuring that the database rejects any write operations even if they appear in the SQL. We recommend configuring this through ~/.pg_service.conf:

[adbpg]
host=gp-xxx.gpdb.rds.aliyuncs.com
port=5432
dbname=mydb
user=readonly_agent
options=-c default_transaction_read_only=on

3. Credential security — never expose credentials in plain text

Correct approach: Use the ~/.pgpass file to manage passwords, and set the file permission to chmod 600.

4. Configure a dedicated resource group

To prevent Agent queries from competing with production workloads for resources, you must configure a dedicated resource group for the read-only account in AnalyticDB PostgreSQL:

CREATE RESOURCE GROUP agent_readonly_rg WITH (concurrency=5, cpu_max_percent=10, memory_limit=10);
ALTER ROLE readonly_agent RESOURCE GROUP agent_readonly_rg;

If no dedicated resource group is configured, the Agent issues a warning at session startup and pauses for your confirmation. For detailed resource group configuration, see Use resource groups for workload management.

5. Semantic model as the access boundary

The Agent cannot bypass the semantic model to query arbitrary tables. The _agent_meta schema serves as a hard access boundary:

• Only tables and columns registered in the semantic model can be queried

• Requests for unregistered tables or columns are rejected with a prompt to contact the DBA

• Any bypass attempts, such as "it's urgent" or "just this once", are explicitly prohibited

6. Human-in-the-loop (HITL) confirmation

The Agent must pause and wait for your explicit confirmation before executing any query:

• After generating SQL, the Agent displays it and waits for you to reply "confirm" or "execute"

• Results exceeding 100,000 rows require a second confirmation

• If a security risk is detected, the Agent pauses immediately and does not execute first and report later

7. Network access control

Make sure the whitelist of your AnalyticDB PostgreSQL instance includes the egress IP of the Agent runtime environment:

• Log on to the AnalyticDB PostgreSQL console on Alibaba Cloud

• Go to the instance, choose Data Security, and then choose Whitelist Settings

• Add the public or VPC IP address of the machine where the Agent runs

Step 1: Install the Skill

Complete the Skill installation using any of the methods described above.

Step 2: Configure the database connection

Recommended method (macOS/Linux): Use pg_service.conf + pgpass

# 1. Create ~/.pg_service.conf
cat > ~/.pg_service.conf << 'EOF'
[adbpg]
host=gp-xxx.gpdb.rds.aliyuncs.com
port=5432
dbname=mydb
user=readonly_agent
options=-c default_transaction_read_only=on
EOF

# 2. Create ~/.pgpass
echo "gp-xxx.gpdb.rds.aliyuncs.com:5432:mydb:readonly_agent:<your_password>" > ~/.pgpass
chmod 600 ~/.pgpass

# 3. Verify the connection
psql "service=adbpg" -c "SELECT 1;"

Step 3: Configure the semantic model

Contact your DBA to create the _agent_meta schema and register the queryable tables and columns. The Skill includes a guided setup for semantic model initialization, and the Agent helps generate the DDL statements for the DBA to execute.

Step 4: Start querying

Describe your query requirements in natural language in the conversation:

You: Show me the total order amount for the last 7 days, grouped by day
Agent: [Generates SQL and displays it] → Waits for confirmation → Executes → Returns results

Step 1: Verify your environment

Before you begin, verify that the following conditions are met:

If psql is not installed:

Recommended: Use the official AnalyticDB PostgreSQL client package to ensure compatibility with the server version. For detailed installation steps, see psql.

Step 2: Create a read-only account (DBA operation)

Why a separate account? Using a superuser account is prohibited. A dedicated read-only account ensures that the Agent can only read data and cannot modify or delete it.

Have the DBA run the following SQL on the AnalyticDB PostgreSQL instance:

-- 1. Create a read-only account
CREATE ROLE readonly_agent LOGIN PASSWORD '<YOUR_PASSWORD_HERE>';

-- 2. Grant connection permissions
GRANT CONNECT ON DATABASE mydb TO readonly_agent;
GRANT USAGE ON SCHEMA public TO readonly_agent;

-- 3. Create a dedicated resource group (prevents Agent queries from impacting production workloads)
CREATE RESOURCE GROUP agent_readonly_rg WITH (concurrency=5, cpu_max_percent=10, memory_limit=10);
ALTER ROLE readonly_agent RESOURCE GROUP agent_readonly_rg;

You do not need to grant SELECT permissions on specific tables at this point. A later step handles this. For detailed resource group configuration, see Use resource groups for workload management.

Step 3: Configure local connection credentials

Configure connection credentials on your local machine, which is the environment where the Agent runs.

cat > ~/.pg_service.conf << 'EOF'
[adbpg]
host=gp-xxxxxxxxx.gpdb.rds.aliyuncs.com
port=5432
dbname=mydb
user=readonly_agent
options=-c default_transaction_read_only=on
EOF

echo "gp-xxxxxxxxx.gpdb.rds.aliyuncs.com:5432:mydb:readonly_agent:<YOUR_PASSWORD_HERE>" > ~/.pgpass
chmod 600 ~/.pgpass

New-Item -ItemType Directory -Force -Path "$env:APPDATA\postgresql"
@"
[adbpg]
host=gp-xxxxxxxxx.gpdb.rds.aliyuncs.com
port=5432
dbname=mydb
user=readonly_agent
options=-c default_transaction_read_only=on
"@ | Out-File -Encoding UTF8 "$env:APPDATA\postgresql\pg_service.conf"

Step 4: Create business tables (DBA operation)

In real-world use, your business tables already exist in the database. Skip this step if applicable. This step is included only for the purpose of completing the full walkthrough.

All SQL in Steps 4, 5, and 6 must be executed by the DBA using an administrator account. Do not use service=adbpg for these operations because that connection is configured as read-only.

The following example creates 5 core business tables for an e-commerce scenario:

4.1 Customer master table

CREATE TABLE IF NOT EXISTS customers (
    customer_id     BIGINT      NOT NULL,
    customer_name   VARCHAR(100),
    gender          VARCHAR(10),
    age_group       VARCHAR(20),
    city            VARCHAR(50),
    province        VARCHAR(50),
    channel         VARCHAR(30),
    register_date   DATE,
    is_member       BOOLEAN,
    member_level    VARCHAR(20),
    total_orders    INT,
    total_amount    NUMERIC(12,2),
    last_order_date DATE
) DISTRIBUTED BY (customer_id);

4.2 Product master table

CREATE TABLE IF NOT EXISTS products (
    product_id      BIGINT      NOT NULL,
    product_name    VARCHAR(200),
    category_l1     VARCHAR(50),
    category_l2     VARCHAR(50),
    brand           VARCHAR(100),
    unit_price      NUMERIC(10,2),
    cost_price      NUMERIC(10,2),
    stock_qty       INT,
    status          VARCHAR(20),
    launch_date     DATE,
    supplier        VARCHAR(100)
) DISTRIBUTED BY (product_id);

4.3 Order master table

CREATE TABLE IF NOT EXISTS orders (
    order_id        BIGINT      NOT NULL,
    customer_id     BIGINT,
    order_date      DATE,
    order_time      TIMESTAMP,
    order_status    VARCHAR(20),
    payment_method  VARCHAR(30),
    channel         VARCHAR(30),
    city            VARCHAR(50),
    province        VARCHAR(50),
    total_amount    NUMERIC(12,2),
    discount_amount NUMERIC(10,2),
    actual_amount   NUMERIC(12,2),
    shipping_fee    NUMERIC(8,2),
    is_refunded     BOOLEAN,
    refund_amount   NUMERIC(12,2)
) DISTRIBUTED BY (order_id);

4.4 Order line items table

CREATE TABLE IF NOT EXISTS order_items (
    item_id         BIGINT      NOT NULL,
    order_id        BIGINT,
    product_id      BIGINT,
    qty             INT,
    unit_price      NUMERIC(10,2),
    discount_rate   NUMERIC(5,4),
    subtotal        NUMERIC(12,2)
) DISTRIBUTED BY (item_id);

4.5 Marketing campaigns table

CREATE TABLE IF NOT EXISTS campaigns (
    campaign_id     BIGINT      NOT NULL,
    campaign_name   VARCHAR(100),
    channel         VARCHAR(30),
    start_date      DATE,
    end_date        DATE,
    budget          NUMERIC(12,2),
    actual_cost     NUMERIC(12,2),
    impressions     INT,
    clicks          INT,
    conversions     INT
) DISTRIBUTED BY (campaign_id);

These 5 tables are the ones you will expose to the Agent through the semantic model. In real-world scenarios, the database typically contains many other business tables. These sensitive tables are not registered in the semantic model, so the Agent cannot access them.

4.6 Insert sample data

After creating the tables, run the following SQL to generate sample data using PostgreSQL's generate_series:

-- Customer data: 200 rows
INSERT INTO customers
SELECT i, 'Customer_' || i,
    CASE WHEN i % 2 = 0 THEN 'Male' ELSE 'Female' END,
    CASE (i % 4) WHEN 0 THEN '18-25' WHEN 1 THEN '26-35' WHEN 2 THEN '36-45' ELSE '46+' END,
    (ARRAY['Beijing','Shanghai','Guangzhou','Shenzhen','Hangzhou','Chengdu','Wuhan','Nanjing'])[1 + (i % 8)],
    (ARRAY['Beijing','Shanghai','Guangdong','Guangdong','Zhejiang','Sichuan','Hubei','Jiangsu'])[1 + (i % 8)],
    (ARRAY['Organic','Paid Ads','Referral','Campaign'])[1 + (i % 4)],
    CURRENT_DATE - (i * 3 || ' days')::INTERVAL,
    (i % 3 != 0),
    CASE (i % 4) WHEN 0 THEN 'Basic' WHEN 1 THEN 'Silver' WHEN 2 THEN 'Gold' ELSE 'Diamond' END,
    5 + (i % 20), (1000 + i * 37.5)::NUMERIC(12,2),
    CURRENT_DATE - (i % 30 || ' days')::INTERVAL
FROM generate_series(1, 200) i;

-- Product data: 50 rows
INSERT INTO products
SELECT i,
    (ARRAY['Wireless Earbuds','Smart Watch','Laptop','Tablet','Phone Case','Power Bank','Bluetooth Speaker','Mechanical Keyboard','Mouse','Monitor','Router','Camera','Desk Lamp','Air Purifier','Coffee Maker','Treadmill','Yoga Mat','Fitness Band'])[1 + (i % 18)] || '_' || i,
    (ARRAY['Electronics','Electronics','Electronics','Electronics','Electronics','Electronics','Electronics','Electronics','Electronics','Electronics','Electronics','Electronics','Home Living','Home Living','Home Living','Sports Health','Sports Health','Sports Health'])[1 + (i % 18)],
    (ARRAY['Audio','Wearable','Computer','Computer','Accessory','Accessory','Audio','Peripheral','Peripheral','Display','Network','Security','Lighting','Purification','Kitchen','Fitness','Fitness','Wearable'])[1 + (i % 18)],
    (ARRAY['Brand_A','Brand_B','Brand_C','Brand_D','Brand_E'])[1 + (i % 5)],
    (99 + i * 23.5)::NUMERIC(10,2), (60 + i * 14.2)::NUMERIC(10,2),
    100 + (i * 7 % 500),
    CASE WHEN i % 10 = 0 THEN 'Discontinued' WHEN i % 15 = 0 THEN 'Pre-order' ELSE 'Active' END,
    CURRENT_DATE - (i * 10 || ' days')::INTERVAL, 'Supplier_' || (1 + i % 10)
FROM generate_series(1, 50) i;

-- Order data: 1000 rows
INSERT INTO orders
SELECT i, 1 + (i % 200),
    CURRENT_DATE - (i % 365 || ' days')::INTERVAL,
    NOW() - (i % 365 || ' days')::INTERVAL,
    (ARRAY['Paid','Paid','Completed','Completed','Completed','Refunded','Shipped','Pending'])[1 + (i % 8)],
    (ARRAY['Alipay','WeChat Pay','WeChat Pay','Bank Card','Huabei'])[1 + (i % 5)],
    (ARRAY['APP','APP','Mini Program','PC','H5'])[1 + (i % 5)],
    (ARRAY['Beijing','Shanghai','Guangzhou','Shenzhen','Hangzhou','Chengdu','Wuhan','Nanjing'])[1 + (i % 8)],
    (ARRAY['Beijing','Shanghai','Guangdong','Guangdong','Zhejiang','Sichuan','Hubei','Jiangsu'])[1 + (i % 8)],
    (200 + i * 13.7)::NUMERIC(12,2), (10 + i % 50)::NUMERIC(10,2),
    (190 + i * 13.2)::NUMERIC(12,2),
    CASE WHEN i % 5 = 0 THEN 0 ELSE 10 END::NUMERIC(8,2),
    (i % 8 = 5),
    CASE WHEN i % 8 = 5 THEN (190 + i * 13.2)::NUMERIC(12,2) ELSE 0 END
FROM generate_series(1, 1000) i;

-- Order line items: 2000 rows
INSERT INTO order_items
SELECT i, 1 + (i % 1000), 1 + (i % 50), 1 + (i % 3),
    (99 + (i % 50) * 23.5)::NUMERIC(10,2),
    CASE WHEN i % 5 = 0 THEN 0.9 ELSE 1.0 END::NUMERIC(5,4),
    ((1 + i % 3) * (99 + (i % 50) * 23.5) * CASE WHEN i % 5 = 0 THEN 0.9 ELSE 1.0 END)::NUMERIC(12,2)
FROM generate_series(1, 2000) i;

-- Marketing campaigns: 15 rows
INSERT INTO campaigns
SELECT i,
    (ARRAY['Spring Festival Sale','Women''s Day','618 Mid-Year Sale','Double 11','Double 12','New Year Goods','Back to School','Summer Sale','Mid-Autumn Festival','National Day Sale','Member Day','Brand Day','Clearance Sale','New Product Launch','Anniversary Sale'])[i],
    (ARRAY['APP','Mini Program','PC','APP','All Channels','APP','Mini Program','APP','All Channels','PC','APP','Mini Program','APP','All Channels','APP'])[i],
    CURRENT_DATE - ((200 - i * 13) || ' days')::INTERVAL,
    CURRENT_DATE - ((190 - i * 13) || ' days')::INTERVAL,
    (50000 + i * 10000)::NUMERIC(12,2), (45000 + i * 9500)::NUMERIC(12,2),
    100000 + i * 20000, 5000 + i * 1000, 200 + i * 50
FROM generate_series(1, 15) i;

4.7 Create tables not registered in the semantic model (12 tables)

The following 12 tables exist in the database but are not registered in the semantic model. The Agent cannot see them. These tables are used to verify the security boundary of the semantic layer.

CREATE TABLE IF NOT EXISTS payments (
    payment_id BIGINT NOT NULL, order_id BIGINT, payment_method VARCHAR(30),
    amount NUMERIC(12,2), payment_time TIMESTAMP, status VARCHAR(20), transaction_no VARCHAR(64)
) DISTRIBUTED BY (payment_id);

CREATE TABLE IF NOT EXISTS coupons (
    coupon_id BIGINT NOT NULL, coupon_name VARCHAR(100), coupon_type VARCHAR(30),
    discount_value NUMERIC(10,2), min_amount NUMERIC(10,2), valid_from DATE, valid_to DATE,
    usage_limit INT, used_count INT
) DISTRIBUTED BY (coupon_id);

CREATE TABLE IF NOT EXISTS warehouses (
    warehouse_id BIGINT NOT NULL, warehouse_name VARCHAR(100), city VARCHAR(50),
    province VARCHAR(50), is_active BOOLEAN, capacity INT
) DISTRIBUTED BY (warehouse_id);

CREATE TABLE IF NOT EXISTS inventory (
    inventory_id BIGINT NOT NULL, product_id BIGINT, warehouse_id BIGINT,
    stock_qty INT, safety_stock INT, last_restock DATE
) DISTRIBUTED BY (inventory_id);

CREATE TABLE IF NOT EXISTS shipments (
    shipment_id BIGINT NOT NULL, order_id BIGINT, carrier VARCHAR(50),
    tracking_no VARCHAR(64), ship_time TIMESTAMP, deliver_time TIMESTAMP,
    status VARCHAR(20), receiver_city VARCHAR(50)
) DISTRIBUTED BY (shipment_id);

CREATE TABLE IF NOT EXISTS returns (
    return_id BIGINT NOT NULL, order_id BIGINT, product_id BIGINT, qty INT,
    reason VARCHAR(200), return_status VARCHAR(20), refund_amount NUMERIC(12,2),
    apply_date DATE, process_date DATE
) DISTRIBUTED BY (return_id);

CREATE TABLE IF NOT EXISTS reviews (
    review_id BIGINT NOT NULL, product_id BIGINT, customer_id BIGINT,
    rating INT, content VARCHAR(500), review_date DATE, is_verified BOOLEAN
) DISTRIBUTED BY (review_id);

CREATE TABLE IF NOT EXISTS employee_salary (
    emp_id BIGINT NOT NULL, emp_name VARCHAR(100), department VARCHAR(50),
    position_level VARCHAR(30), base_salary NUMERIC(12,2), bonus NUMERIC(12,2),
    bank_account VARCHAR(30), id_card VARCHAR(20)
) DISTRIBUTED BY (emp_id);

CREATE TABLE IF NOT EXISTS supplier_contracts (
    contract_id BIGINT NOT NULL, supplier_name VARCHAR(100), product_line VARCHAR(50),
    contract_amount NUMERIC(14,2), discount_rate NUMERIC(5,4), payment_terms VARCHAR(100),
    start_date DATE, end_date DATE
) DISTRIBUTED BY (contract_id);

CREATE TABLE IF NOT EXISTS pricing_strategy (
    strategy_id BIGINT NOT NULL, category_l1 VARCHAR(50), cost_markup NUMERIC(5,4),
    min_price NUMERIC(10,2), promo_floor NUMERIC(10,2), effective_date DATE
) DISTRIBUTED BY (strategy_id);

CREATE TABLE IF NOT EXISTS employee_performance (
    perf_id BIGINT NOT NULL, emp_id BIGINT, review_period VARCHAR(20),
    rating VARCHAR(10), kpi_score NUMERIC(5,2), salary_adj_pct NUMERIC(5,4),
    manager_comment TEXT
) DISTRIBUTED BY (perf_id);

CREATE TABLE IF NOT EXISTS chat_history (
    chat_id BIGINT NOT NULL, customer_id BIGINT, agent_name VARCHAR(50),
    message TEXT, chat_time TIMESTAMP, tags VARCHAR(100)
) DISTRIBUTED BY (chat_id);

Security boundary verification: The 12 tables above contain highly sensitive information such as employee salaries, bank account numbers, ID card numbers, and customer service chat logs. Because they are not registered in the semantic model, the Agent cannot discover or query them.

4.8 Insert sample data for unregistered tables

INSERT INTO payments
SELECT i, 1 + (i % 1000),
    (ARRAY['Alipay','WeChat Pay','WeChat Pay','Bank Card','Huabei'])[1 + (i % 5)],
    (190 + i * 8.3)::NUMERIC(12,2),
    NOW() - (i % 365 || ' days')::INTERVAL,
    (ARRAY['Success','Success','Success','Success','Failed','Refunded'])[1 + (i % 6)],
    'TXN' || md5(i::text)
FROM generate_series(1, 800) i;

-- Coupons: 20 rows
INSERT INTO coupons
SELECT i,
    (ARRAY['Welcome Pack','Spend 200 Save 30','Category Coupon - Electronics','Category Coupon - Home','Member Exclusive 10% Off',
           'Double 11 Discount','New Year Goods Coupon','Free Shipping','Flash Sale Coupon','Win-back Coupon',
           'Spend 500 Save 80','Brand Partnership Coupon','Birthday Privilege','Group Buy Discount','New Product Trial',
           'Spend 1000 Save 150','PLUS Member Coupon','Livestream Exclusive','7-Day Check-in','Review Reward'])[i],
    (ARRAY['Threshold','Threshold','Category','Category','Discount','Threshold','Threshold','Shipping','Direct','Direct',
           'Threshold','Category','Discount','Direct','Direct','Threshold','Discount','Direct','Direct','Direct'])[i],
    (ARRAY[30,30,50,30,0,50,40,0,20,15,80,30,0,10,5,150,0,8,5,3])[i]::NUMERIC(10,2),
    (ARRAY[0,200,100,50,0,300,200,0,0,0,500,100,0,0,0,1000,0,0,0,0])[i]::NUMERIC(10,2),
    CURRENT_DATE - (i * 5 || ' days')::INTERVAL,
    CURRENT_DATE + ((30 - i) || ' days')::INTERVAL,
    500 + i * 100,
    (i * 23) % (500 + i * 100)
FROM generate_series(1, 20) i;

-- Warehouses: 6 rows
INSERT INTO warehouses
SELECT i,
    (ARRAY['East China - Shanghai','North China - Beijing','South China - Guangzhou','Central China - Wuhan','Southwest - Chengdu','Northeast - Shenyang'])[i],
    (ARRAY['Shanghai','Beijing','Guangzhou','Wuhan','Chengdu','Shenyang'])[i],
    (ARRAY['Shanghai','Beijing','Guangdong','Hubei','Sichuan','Liaoning'])[i],
    TRUE, 50000 + i * 10000
FROM generate_series(1, 6) i;

-- Inventory: 300 rows
INSERT INTO inventory
SELECT i, 1 + (i % 50), 1 + (i % 6),
    50 + (i * 7 % 500), 20 + (i % 30),
    CURRENT_DATE - (i % 60 || ' days')::INTERVAL
FROM generate_series(1, 300) i;

-- Shipments: 600 rows
INSERT INTO shipments
SELECT i, 1 + (i % 1000),
    (ARRAY['SF Express','ZTO Express','YTO Express','Yunda Express','J&T Express'])[1 + (i % 5)],
    'SF' || (1000000000 + i),
    NOW() - (i % 30 || ' days')::INTERVAL,
    CASE WHEN i % 4 != 0 THEN NOW() - ((i % 30) - 2 || ' days')::INTERVAL ELSE NULL END,
    (ARRAY['Delivered','Delivered','Delivered','In Transit','Picked Up','Exception'])[1 + (i % 6)],
    (ARRAY['Beijing','Shanghai','Guangzhou','Shenzhen','Hangzhou','Chengdu','Wuhan','Nanjing'])[1 + (i % 8)]
FROM generate_series(1, 600) i;

-- Returns: 100 rows
INSERT INTO returns
SELECT i, 1 + (i % 1000), 1 + (i % 50), 1 + (i % 2),
    (ARRAY['Quality issue','Wrong size','Not satisfied','Wrong item','Other'])[1 + (i % 5)],
    (ARRAY['Refunded','Processing','Rejected','Refunded','Refunded'])[1 + (i % 5)],
    (50 + i * 3.7)::NUMERIC(12,2),
    CURRENT_DATE - (i % 90 || ' days')::INTERVAL,
    CASE WHEN i % 5 != 2 THEN CURRENT_DATE - ((i % 90) - 3 || ' days')::INTERVAL ELSE NULL END
FROM generate_series(1, 100) i;

-- Product reviews: 500 rows
INSERT INTO reviews
SELECT i, 1 + (i % 50), 1 + (i % 200),
    CASE WHEN i % 5 = 0 THEN 1 WHEN i % 5 = 1 THEN 3 ELSE 5 END,
    CASE WHEN i % 3 = 0 THEN 'Great value for money!' WHEN i % 3 = 1 THEN 'Average' ELSE 'Very satisfied, highly recommended' END,
    CURRENT_DATE - (i % 180 || ' days')::INTERVAL,
    (i % 4 != 0)
FROM generate_series(1, 500) i;

-- Employee salary: 20 rows
INSERT INTO employee_salary
SELECT i, 'Employee_' || i,
    (ARRAY['Engineering','Finance','Operations','Marketing'])[1 + (i % 4)],
    (ARRAY['P5','P6','P7','P8'])[1 + (i % 4)],
    (15000 + i * 2000)::NUMERIC(12,2),
    (3000 + i * 500)::NUMERIC(12,2),
    '6222****' || (1000 + i),
    '110***' || (19700000 + i * 10000)
FROM generate_series(1, 20) i;

-- Supplier contracts: 15 rows
INSERT INTO supplier_contracts
SELECT i, 'Supplier_' || i,
    (ARRAY['Electronics','Home Living','Sports Health','Electronics','Home Living'])[1 + (i % 5)],
    (500000 + i * 200000)::NUMERIC(14,2),
    (0.85 + (i % 5) * 0.02)::NUMERIC(5,4),
    (ARRAY['Net30','Net60','Net90','30% Upfront','Monthly'])[1 + (i % 5)],
    CURRENT_DATE - (i * 30 || ' days')::INTERVAL,
    CURRENT_DATE + ((365 - i * 20) || ' days')::INTERVAL
FROM generate_series(1, 15) i;

-- Pricing strategy: 10 rows
INSERT INTO pricing_strategy
SELECT i,
    (ARRAY['Electronics','Home Living','Sports Health','Electronics','Home Living',
           'Sports Health','Electronics','Home Living','Sports Health','Electronics'])[i],
    (1.2 + (i % 5) * 0.1)::NUMERIC(5,4),
    (50 + i * 20)::NUMERIC(10,2),
    (30 + i * 10)::NUMERIC(10,2),
    CURRENT_DATE - (i * 60 || ' days')::INTERVAL
FROM generate_series(1, 10) i;

-- Employee performance: 20 rows
INSERT INTO employee_performance
SELECT i, i,
    (ARRAY['2024-H1','2024-H2','2025-H1'])[1 + (i % 3)],
    (ARRAY['S','A','B','C'])[1 + (i % 4)],
    (3.0 + (i % 5) * 0.5)::NUMERIC(5,2),
    (0.0 + (i % 5) * 0.05)::NUMERIC(5,4),
    CASE WHEN i % 4 = 0 THEN 'Outstanding performance, recommend promotion' WHEN i % 4 = 1 THEN 'Meets expectations'
         WHEN i % 4 = 2 THEN 'Needs improvement' ELSE 'New hire, observation period' END
FROM generate_series(1, 20) i;

-- Customer service chat logs: 100 rows
INSERT INTO chat_history
SELECT i, 1 + (i % 200),
    (ARRAY['Agent Wang','Agent Li','Agent Zhang'])[1 + (i % 3)],
    CASE WHEN i % 3 = 0 THEN 'My delivery address is Building ' || i || ', Sample District'
         WHEN i % 3 = 1 THEN 'My phone number is 138****' || (1000 + i)
         ELSE 'Refund to my bank card 6222****' || (2000 + i) END,
    NOW() - (i % 60 || ' days')::INTERVAL,
    (ARRAY['Refund','Shipping','Product Inquiry','Complaint'])[1 + (i % 4)]
FROM generate_series(1, 100) i;

Step 5: Create the semantic model (DBA operation)

What is a semantic model? The semantic model is the Agent's "data dictionary". Only tables and columns registered in the semantic model can be queried by the Agent. Unregistered tables are completely invisible to the Agent — this is the core security mechanism.

5.1 Create the metadata schema

CREATE SCHEMA IF NOT EXISTS _agent_meta;

5.2 Create the five metadata tables

CREATE TABLE IF NOT EXISTS _agent_meta.tables (
    table_schema VARCHAR(64) NOT NULL, table_name VARCHAR(128) NOT NULL,
    display_name VARCHAR(128), description TEXT, business_domain VARCHAR(64),
    PRIMARY KEY (table_schema, table_name)
);

CREATE TABLE IF NOT EXISTS _agent_meta.columns (
    table_schema VARCHAR(64) NOT NULL, table_name VARCHAR(128) NOT NULL,
    column_name VARCHAR(128) NOT NULL, display_name VARCHAR(128),
    column_role VARCHAR(16), data_type VARCHAR(64), column_description TEXT,
    synonyms TEXT[], agg_default VARCHAR(16), example_values TEXT[],
    PRIMARY KEY (table_schema, table_name, column_name)
);

CREATE TABLE IF NOT EXISTS _agent_meta.joins (
    join_id SERIAL, left_schema VARCHAR(64), left_table VARCHAR(128),
    left_column VARCHAR(128), right_schema VARCHAR(64), right_table VARCHAR(128),
    right_column VARCHAR(128), join_type VARCHAR(10) DEFAULT 'LEFT',
    relationship_type VARCHAR(20) DEFAULT 'many_to_one', description TEXT
);

CREATE TABLE IF NOT EXISTS _agent_meta.metric_meta (
    metric_name VARCHAR(64) PRIMARY KEY, business_desc TEXT NOT NULL,
    metric_type VARCHAR(16) NOT NULL DEFAULT 'derived', sql_expression TEXT NOT NULL,
    numerator TEXT, denominator TEXT, dimensions TEXT[], filters TEXT[],
    owner VARCHAR(64), ai_hints TEXT
);

CREATE TABLE IF NOT EXISTS _agent_meta.filters (
    filter_name VARCHAR(64) PRIMARY KEY, description TEXT NOT NULL,
    sql_fragment TEXT NOT NULL, filter_scope VARCHAR(16) NOT NULL DEFAULT 'where',
    owner VARCHAR(64)
);

5.3 Register queryable tables

INSERT INTO _agent_meta.tables VALUES
('public','customers',   'Customer Master',   'Registered customer information including membership level, source channel, and spending summary', 'Sales'),
('public','products',    'Product Master',    'Active products with category, pricing, and inventory data',                                       'Product'),
('public','orders',      'Order Master',      'Order header data including amount, status, channel, and region',                                  'Sales'),
('public','order_items', 'Order Line Items',  'Per-product quantity and amount within each order',                                                'Sales'),
('public','campaigns',   'Marketing Campaigns','Campaign definitions including channel, budget, and performance metrics',                         'Marketing');

5.4 Register column semantics (core step)

Define the role and synonyms for each column so the Agent can understand natural language:

-- orders table columns
INSERT INTO _agent_meta.columns (table_schema, table_name, column_name, display_name, column_role, data_type, column_description, synonyms, agg_default, example_values) VALUES
('public','orders','order_id',       'Order ID',       'identifier','BIGINT',  'Unique order identifier',                      NULL,                                                    NULL,   NULL),
('public','orders','customer_id',   'Customer ID',    'identifier','BIGINT',  'Foreign key to customers table',                NULL,                                                    NULL,   NULL),
('public','orders','order_date',    'Order Date',     'dimension', 'DATE',    'Date the order was created',                    ARRAY['date','purchase date','transaction date'],         NULL,   NULL),
('public','orders','order_status',  'Order Status',   'dimension', 'VARCHAR', 'Current order status',                          ARRAY['status','transaction status'],                     NULL,   ARRAY['Paid','Completed','Refunded','Shipped','Pending']),
('public','orders','payment_method','Payment Method', 'dimension', 'VARCHAR', 'Payment channel used by the customer',          ARRAY['payment','payment type'],                          NULL,   ARRAY['Alipay','WeChat Pay','Bank Card','Huabei']),
('public','orders','channel',       'Order Channel',  'dimension', 'VARCHAR', 'Device or platform used to place the order',    ARRAY['channel','platform','device'],                     NULL,   ARRAY['APP','Mini Program','PC','H5']),
('public','orders','province',      'Province',       'dimension', 'VARCHAR', 'Shipping province for the order',               ARRAY['region','province','shipping address'],             NULL,   NULL),
('public','orders','actual_amount', 'Actual Amount',  'measure',   'NUMERIC', 'Amount actually paid after discounts (CNY)',     ARRAY['paid amount','payment amount','GMV','revenue'],    'SUM',  NULL),
('public','orders','total_amount',  'Original Amount','measure',   'NUMERIC', 'Original order amount before discounts (CNY)',   ARRAY['original price','order amount'],                   'SUM',  NULL),
('public','orders','discount_amount','Discount Amount','measure',  'NUMERIC', 'Discount or coupon amount for the order (CNY)', ARRAY['discount','coupon amount','savings'],               'SUM',  NULL),
('public','orders','is_refunded',   'Is Refunded',    'dimension', 'BOOLEAN', 'Whether the order has been refunded',           ARRAY['refund','is returned'],                            NULL,   ARRAY['true','false'])
ON CONFLICT DO NOTHING;

-- order_items key columns
INSERT INTO _agent_meta.columns (table_schema, table_name, column_name, display_name, column_role, data_type, column_description, synonyms, agg_default, example_values) VALUES
('public','order_items','item_id',     'Item ID',       'identifier','BIGINT',  'Unique line item identifier',                   NULL,                                         NULL,   NULL),
('public','order_items','order_id',    'Order ID',      'identifier','BIGINT',  'Foreign key to orders table',                   NULL,                                         NULL,   NULL),
('public','order_items','product_id',  'Product ID',    'identifier','BIGINT',  'Foreign key to products table',                 NULL,                                         NULL,   NULL),
('public','order_items','qty',         'Quantity',      'measure',   'INT',     'Number of units purchased',                     ARRAY['quantity','units','count','sales volume'],'SUM', NULL),
('public','order_items','subtotal',    'Subtotal',      'measure',   'NUMERIC', 'Line item total amount (CNY)',                  ARRAY['amount','line amount','sales amount'],  'SUM',  NULL)
ON CONFLICT DO NOTHING;

-- customers key columns
INSERT INTO _agent_meta.columns (table_schema, table_name, column_name, display_name, column_role, data_type, column_description, synonyms, agg_default, example_values) VALUES
('public','customers','customer_id',   'Customer ID',   'identifier','BIGINT',  'Unique customer identifier',                    NULL,                                         NULL,   NULL),
('public','customers','customer_name', 'Customer Name', 'dimension', 'VARCHAR', 'Customer name',                                 ARRAY['customer','buyer','user name'],         NULL,   NULL),
('public','customers','city',          'City',          'dimension', 'VARCHAR', 'Customer city',                                 ARRAY['city','location'],                     NULL,   ARRAY['Beijing','Shanghai','Guangzhou']),
('public','customers','channel',       'Acquisition Channel','dimension','VARCHAR','Customer registration source channel',        ARRAY['channel','source','acquisition method'],NULL,  ARRAY['Organic','Paid Ads','Referral','Campaign']),
('public','customers','is_member',     'Is Member',     'dimension', 'BOOLEAN', 'Whether the customer is a member',              ARRAY['member','membership status'],           NULL,   ARRAY['true','false']),
('public','customers','member_level',  'Member Level',  'dimension', 'VARCHAR', 'Membership tier',                               ARRAY['level','membership tier'],              NULL,   ARRAY['Basic','Silver','Gold','Diamond']),
('public','customers','total_orders',  'Lifetime Orders','measure',  'INT',     'Total number of orders by the customer',        ARRAY['order count','purchase count'],         'SUM',  NULL),
('public','customers','total_amount',  'Lifetime Spending','measure','NUMERIC', 'Total cumulative spending by the customer (CNY)',ARRAY['total spending','lifetime amount'],     'SUM',  NULL)
ON CONFLICT DO NOTHING;

-- products key columns
INSERT INTO _agent_meta.columns (table_schema, table_name, column_name, display_name, column_role, data_type, column_description, synonyms, agg_default, example_values) VALUES
('public','products','product_id',  'Product ID',     'identifier','BIGINT',  'Unique product identifier',                     NULL,                                          NULL,   NULL),
('public','products','product_name','Product Name',   'dimension', 'VARCHAR', 'Product name',                                  ARRAY['product','item','SKU'],                  NULL,   NULL),
('public','products','category_l1',  'L1 Category',   'dimension', 'VARCHAR', 'Top-level product category',                    ARRAY['main category','top category'],          NULL,   ARRAY['Electronics','Home Living','Sports Health']),
('public','products','brand',       'Brand',          'dimension', 'VARCHAR', 'Product brand',                                 ARRAY['brand name','manufacturer'],              NULL,   NULL),
('public','products','unit_price',  'Unit Price',     'measure',   'NUMERIC', 'Retail price per unit (CNY)',                    ARRAY['price','retail price','selling price'],   'AVG',  NULL),
('public','products','stock_qty',   'Stock Quantity', 'measure',   'INT',     'Current available inventory quantity',           ARRAY['inventory','remaining stock'],            'SUM',  NULL),
('public','products','status',      'Product Status', 'dimension', 'VARCHAR', 'Current listing status of the product',         ARRAY['status','availability'],                  NULL,   ARRAY['Active','Discontinued','Pre-order'])
ON CONFLICT DO NOTHING;

-- campaigns key columns
INSERT INTO _agent_meta.columns (table_schema, table_name, column_name, display_name, column_role, data_type, column_description, synonyms, agg_default, example_values) VALUES
('public','campaigns','campaign_id',  'Campaign ID',   'identifier','BIGINT',  'Unique campaign identifier',                    NULL,                                          NULL,   NULL),
('public','campaigns','campaign_name','Campaign Name', 'dimension', 'VARCHAR', 'Campaign name',                                 ARRAY['campaign','promotion','sale'],            NULL,   NULL),
('public','campaigns','channel',      'Channel',       'dimension', 'VARCHAR', 'Campaign distribution channel',                 ARRAY['channel','platform'],                    NULL,   ARRAY['APP','Mini Program','PC','All Channels']),
('public','campaigns','budget',      'Budget',        'measure',   'NUMERIC', 'Total campaign budget (CNY)',                    ARRAY['budget','ad budget'],                    'SUM',  NULL),
('public','campaigns','actual_cost',  'Actual Cost',   'measure',   'NUMERIC', 'Actual campaign spending (CNY)',                 ARRAY['spending','cost','actual spend'],         'SUM',  NULL),
('public','campaigns','impressions',  'Impressions',   'measure',   'INT',     'Total campaign impressions',                     ARRAY['views','displays'],                      'SUM',  NULL),
('public','campaigns','clicks',      'Clicks',        'measure',   'INT',     'Total campaign clicks',                          ARRAY['click count'],                           'SUM',  NULL),
('public','campaigns','conversions',  'Conversions',   'measure',   'INT',     'Number of order conversions from the campaign',  ARRAY['conversion count','orders'],              'SUM',  NULL)
ON CONFLICT DO NOTHING;

Core value: The synonyms column enables the Agent to understand natural language. When you say "check the GMV", "view revenue", or "product sales volume", the Agent correctly matches these to the corresponding columns.

5.5 Define table join relationships

INSERT INTO _agent_meta.joins
(left_schema, left_table, left_column, right_schema, right_table, right_column, join_type, relationship_type, description)
VALUES
('public','order_items','order_id',   'public','orders',   'order_id',   'LEFT','many_to_one','Order line items to order header'),
('public','order_items','product_id', 'public','products', 'product_id', 'LEFT','many_to_one','Order line items to products'),
('public','orders',    'customer_id','public','customers','customer_id','LEFT','many_to_one','Orders to customers');

5.6 (Optional) Define business metrics

INSERT INTO _agent_meta.metric_meta VALUES
('net_revenue', 'Net revenue = Actual amount - Discount amount', 'derived',
 'SUM(actual_amount) - SUM(discount_amount)', NULL, NULL,
 '{public.orders.province, public.orders.channel, public.orders.order_date}',
 '{paid_or_completed, not_refunded}', 'finance_team', 'Must be aggregated by province or channel'),
('refund_rate', 'Refund rate = Refunded orders / Total orders', 'ratio',
 'SUM(CASE WHEN is_refunded THEN 1 ELSE 0 END)::numeric / COUNT(*)',
 'SUM(CASE WHEN is_refunded THEN 1 ELSE 0 END)', 'COUNT(*)',
 '{public.orders.province, public.orders.channel}', NULL, 'ops_team', NULL),
('cumulative_gmv', 'Cumulative GMV (actual amount) by date', 'cumulative',
 'SUM(SUM(actual_amount)) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING)',
 NULL, NULL, '{public.orders.order_date}', '{not_refunded}', 'finance_team', NULL),
('completed_gmv', 'GMV of completed orders', 'simple',
 'SUM(actual_amount) FILTER (WHERE order_status = ''Completed'')',
 NULL, NULL, '{public.orders.province, public.orders.channel}',
 '{completed_measure}', 'ops_team', NULL),
('campaign_roi', 'Campaign ROI = Conversions / Actual cost', 'ratio',
 'SUM(conversions)::numeric / NULLIF(SUM(actual_cost), 0)',
 'SUM(conversions)', 'NULLIF(SUM(actual_cost), 0)',
 '{public.campaigns.channel}', NULL, 'marketing_team', 'Applies to the campaigns table only');

5.7 (Optional) Define filters

INSERT INTO _agent_meta.filters VALUES
('paid_or_completed', 'Paid or completed orders',
 'order_status IN (''Paid'',''Completed'')', 'where', 'ops_team'),
('not_refunded', 'Exclude refunded orders',
 'is_refunded = false', 'where', 'finance_team'),
('completed_measure', 'Completed orders measure filter',
 'order_status = ''Completed''', 'measure', 'ops_team');

Step 6: Grant permissions to the read-only account (DBA operation)

After the semantic model is created, grant SELECT permissions on the corresponding tables to the read-only account:

-- Grant SELECT only on the 5 tables registered in the semantic model
GRANT SELECT ON public.customers, public.products, public.orders,
               public.order_items, public.campaigns TO readonly_agent;

-- Grant read access to the semantic model metadata tables
GRANT USAGE ON SCHEMA _agent_meta TO readonly_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA _agent_meta TO readonly_agent;

Security principle: Grant permissions only for tables registered in the semantic model. Even if the Agent constructs a query against other tables for any reason, the database permission layer rejects the request.

Step 7: Verify the setup

Return to the local terminal and verify the entire configuration using the read-only account:

# 1. Verify the connection
psql "service=adbpg" -c "SELECT current_user;"
# Expected output: readonly_agent

# 2. Verify read-only mode
psql "service=adbpg" -c "CREATE TABLE test_write (id int);"
# Expected error: ERROR: cannot execute CREATE TABLE in a read-only transaction

# 3. Verify the semantic model is readable
psql "service=adbpg" -c "SELECT COUNT(*) FROM _agent_meta.tables;"
# Expected output: 5

# 4. Verify that unregistered tables are inaccessible (permission denied)
psql "service=adbpg" -c "SELECT * FROM employee_salary LIMIT 1;"
# Expected error: ERROR: permission denied for table employee_salary

After all checks pass, the environment setup is complete.

Step 8: Start a conversation with the Agent

Open the Agent (Claude, Qoder, ClawHub, or another compatible platform) and ask questions in natural language.

The Agent automatically runs security checks at the start of each session:

1. Verify connection → 2. Check account permissions → 3. Check resource group → 4. Detect semantic model

After passing these checks, you can start querying.

Conversation examples

You: Show me the total actual amount by province for the last 30 days, ranked by amount

Agent: Based on the semantic model, here is the generated query:

  SELECT province, SUM(actual_amount) AS total_amount
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY province
  ORDER BY total_amount DESC
  LIMIT 50000;

  Do you want to execute this query?

You: Yes

Agent: Query results:
  | province  | total_amount |
  |-----------|--------------|
  | Guangdong | 128,350.00   |
  | Zhejiang  | 96,720.00    |

You: What is the net revenue by channel?

Agent: Matched the predefined metric "net_revenue". Standard filters are automatically applied:
  - Only paid or completed orders
  - Refunded orders excluded

  SELECT channel,
         SUM(actual_amount) - SUM(discount_amount) AS net_revenue
  FROM orders
  WHERE order_status IN ('Paid','Completed')
    AND is_refunded = false
  GROUP BY channel
  ORDER BY net_revenue DESC;

  Do you want to execute this query?

You: Show me the sales volume ranking for Brand_A products

Agent: This requires joining the products and order_items tables. Generated based on the semantic model join definitions:

  SELECT p.product_name, SUM(oi.qty) AS total_qty
  FROM order_items oi
  LEFT JOIN products p ON oi.product_id = p.product_id
  WHERE p.brand = 'Brand_A'
  GROUP BY p.product_name
  ORDER BY total_qty DESC
  LIMIT 50000;

  Do you want to execute this query?

You: Show me employee salary information

Agent: Unable to execute this query.
  The employee_salary table is not registered in the semantic model and is outside the queryable scope.
  To access this data, contact your DBA to add the table to the _agent_meta semantic model.

You: Export last month's order data to CSV

Agent: Exporting last month's order data. Estimated ~80 rows.
  \copy (SELECT order_id, customer_id, order_date, actual_amount, channel
         FROM orders WHERE order_date >= '2026-05-01' AND order_date < '2026-06-01')
  TO '/tmp/orders_20260623_143022.csv' WITH CSV HEADER;

  The exported file may contain business data. Handle it according to your organization's data security policies.
  Do you want to execute this query?

Workflow summary

The complete workflow has 8 steps, divided by role:

DBA tasks (Steps 2/4/5/6):
  Create read-only account → Prepare business tables → Configure semantic model → Grant permissions

User tasks (Steps 1/3/7/8):
  Environment preparation → Configure local connection → Verify → Start conversational queries

Security layers summary