Data model

ApiGo's persistence layer uses MySQL. The initialization script db/mysql/ddl.sql creates 45 core tables, and the upgrade script db/mysql/update_ddl.sql adds AI-scenario and dictionary tables. This document explains how the tables relate to each other by business domain, to help you understand the data boundaries behind each feature.

No physical foreign keys

The entire model does not use database foreign keys; referential integrity is enforced by the application layer. The goals are to support sharding and data migration, avoid accidental damage from cascading deletes, and make it easy to purge data per project. When reading table structures, focus on unique keys and indexes—they are the real constraints.

Global relationships

Project and permission domain

Table	Purpose	Key fields / constraints
base_project	Project (tenant space)	Project code, name, status; all business data carries project_id
base_project_member	Project members and roles	Project + user + role; determines project-level data visibility
base_sys_user	Platform users	user_name unique; password, status, department
base_sys_role / base_sys_role_menu	Roles and menu permissions	Role-menu many-to-many
base_sys_menu	Menu and button permissions	Tree structure; source of frontend dynamic routes
base_sys_dept	Departments	wecom_dept_id unique; used for WeCom sync
base_sys_sso_bind	Third-party account bindings	(provider, provider_user_id) unique
base_sys_log	Admin operation logs	Operator, module, action, request and result
base_sys_message	In-app messages	Recipient, type, read status
base_sys_params	System parameters	Parameter key-value pairs, adjustable at runtime

Isolation rule: Except for platform-level system tables, APIs, groups, data sources, rules, alerts, and AI conversations are all isolated by project_id. Users can only see business data of projects they belong to; super administrators can operate across projects.

API asset domain

Table	Purpose	Key fields / constraints
base_api_info	Design-time API master table	api_path + api_method unique; api_type is sql/table/proxy; api_status is edit/audit/release/destroy; api_link_id identifies the version family; parent_id supports derived APIs
base_api_param	API parameter definitions	Input/output, type, required, default value, ordering; snapshotted into the release table on release
base_api_proxy	Proxy mode configuration	Target address, request method, timeout, authentication, payload mode, error code mapping
base_api_env	Environment definitions	(project_id, env_code) unique; path prefix, access address, MCP prefix, access mode, production flag
base_auth_env	Environment access authorization	Environment + user; scopes visibility of non-public environments
base_api_release	Release snapshot	(api_link_id, env_code, deploy_revision) unique; source_api_id points to the design-time API; is_current marks the currently effective version
base_group	API groups	Tree-structured groups; source of the Workbench left navigation
base_category	API categories	Cross-group horizontal tags
base_api_test_case	Automated test cases	Organized by api_id + env_code; stores request snapshots, assertions, and the latest execution result
base_api_mock_rule	Mock rules	Configured by api_id + env_code; includes priority, delay, status code, response body, and match conditions
base_api_log	API call logs	Request/response, duration, status code, traceId, app, and release version; the primary log store when ES is not enabled

Design highlights:

• Design state and release state are separate. base_api_info can be edited at any time; base_api_release is an immutable snapshot; rollback only switches snapshots and does not modify the design state.
• api_link_id links multiple environment snapshots and test cases of the same API into one version family, with revision numbers increasing within the family.
• base_api_env's access_mode determines whether an environment is publicly accessible or permission-controlled; is_prod marks production environments and drives alerting.

Open and authorization domain

Table	Purpose	Key fields / constraints
base_app	Calling apps	app_code unique; app_key/app_secret; ips credential-level IP whitelist; scope is api/project; mcp_enabled
base_app_api	App-API authorization	(project_id, api_id, app_id, grant_source) unique; grant_type permanent/validity period; linked approval ticket
base_audit	Call approval tickets	Approval type, status process/pass/reject/revoke, authentication method, validity period, daily quota
base_subscription	Subscriptions	Status pending/active/expired/unsubscribed/frozen; effective start/end, daily quota, reminder level
base_ip_rule	IP access control rules	Environment, allow/deny list type, IP list; bound to APIs via base_rule_api
base_rule_api	Rule-API binding	ruleType distinguishes flow/degrade/cache/ip/warn; api_id + env_code
base_flow_rule	Flow control rules	Environment, QPS threshold, rule name
base_degrade_rule	Circuit breaking rules	Slow-call ratio / exception ratio / exception count, time window, minimum request count, circuit open duration
base_cache_rule	API cache rules	Expiration time, maximum response body, cache key rule
base_warn_rule	Alert rules	Alert type, comparison condition, threshold, time window, minimum request count, notification method and frequency
base_warn_record	Alert records	Trigger value, send status, associated log and traceId

Authorization model: An app can gain permission to call an API through two paths—manual authorization (grant_source=manual) and approval (grant_source=audit). Subscription is another layer of "per-API subscription + quota" control, independent of app authorization; both are validated at runtime.

AI domain

Table	Purpose	Key fields / constraints
base_ai_agent	Agent configuration	agent_code unique; api_key_hash stores only the SHA-256 hash, api_key_prefix is used for display
base_ai_agent_tool	Tools available to the agent	(agent_id, tool_code) unique; controls read/write permission and enabled status
base_ai_conversation	Conversations	Conversation ownership, scenario, status
base_ai_agent_turn	Conversation turns	Input, SQL, result, duration, success status of one Q&A round
base_ai_agent_message	Messages within a turn	(conversation_id, turn_id, seq) unique; stores user/assistant/tool messages
base_ai_agent_session_state	Conversation runtime state	Pending operations and context state; supports multi-turn confirmation
base_ai_audit_log	AI call audit	Action, question, tokens, duration, success status; written asynchronously
base_ai_agent_call_log	Tool call logs	Tool name, input parameters, result, duration
base_ai_semantic_term	Semantic layer terms	Business term → table/field/definition
base_ai_semantic_metric	Semantic layer metrics	Metric formula, dimensions, filter conditions

Design highlights: Agent keys are stored only as hashes and cannot be viewed in plaintext after creation; conversations and turns are separated, making it easy to compute success rates per turn; session_state supports the "generate SQL → user confirms → execute" confirmation card flow.

Logs and statistics

• base_api_log serves both call details and report data. Once Elasticsearch is enabled, logs are written to ES while the database keeps configuration and alert records.
• base_warn_record stores alert trigger snapshots, so they remain traceable even after the original logs are purged.
• base_ai_audit_log is independent of API logs and is aggregated by AI action.

Unique keys and concurrency control

Unique key	Problem it prevents
UK_API_PATH_METHOD	The same path + method being occupied by two APIs
UK_RELEASE_REV	Duplicate revision numbers for the same version family in the same environment
uk_app_code	Duplicate app codes causing MCP/subscription addressing ambiguity
APP_API_SOURCE_UK	Duplicate authorization of the same API to the same app
UK_DATASOURCE_AUTH	Duplicate authorization of the same data source to the same project
UK_ENV_PROJECT_CODE	Duplicate environment code within a project
UK_AGENT_CODE / UK_AGENT_TOOL	Duplicate agent codes, or the same tool bound twice

The release pipeline also takes a row lock on the version-family root API (lockVersionRoot), ensuring revision numbers do not collide when multiple instances release concurrently.

Data lifecycle

Data	Cleanup strategy
API call logs	Archived by the deployer per table/index; the platform does not delete them automatically
AI audit logs	No automatic cleanup; long text is truncated to 4000 characters
Alert records	Trigger snapshots are retained for post-mortem tracing
Release snapshots	Cleaned up when an API is deleted or an environment version is manually deleted
Session state	Expires when the conversation ends

For production, we recommend partitioning or regularly archiving base_api_log, base_ai_audit_log, and base_ai_agent_message to prevent unbounded single-table growth.