Database Schema Design: a Tokeny case study
Generate database table structures and SQL migration scripts from business requirements. Follow the recorded task, execution screenshots, actual files and verification notes.
By the Tokeny team · Windows · Tokeny 1.7.8 · gpt-6-sol
Prepare the inputs
Create a separate workspace in Tokeny, download and unpack the sample inputs, then select a model that supports tools. The sample uses synthetic data or the public sources named in the article.
- requirements.md
Send the task to Tokeny
The original task was executed in Chinese. Copying these instructions reproduces the same task; the results below include an English report.
为图书借阅系统设计 SQLite Schema 和迁移文件，包含书籍、读者、借阅记录，外键、ISBN 唯一和活动借阅唯一约束；用临时 SQLite 实际验证约束。 交付文件：schema.sql、verify.py、schema.md。 验收要点： - 重复 ISBN 和重复未归还借阅被拒绝 - 非法外键被拒绝 - MCP 连接不在本例验证范围 这是使用合成演示输入或指定公开资料的真实操作案例。请先读取工作空间输入，再实际生成文件并自检。所有写入、脚本与临时服务限于当前工作空间；临时服务只监听 127.0.0.1，执行后关闭；不触达外部收件人、不发布、不付款、不更改系统设置，不调用子智能体。需要公开资料时可读取给定 URL。只启用完成任务必需的工具。另写 report.md（中文）和 report.en.md（英文），简洁、完整，包含输入、处理步骤、真实结果、检查方式和局限；失败或未验证部分如实记录，不能宣称成功。最终回复用中文列出产物、关键结果与待核实项。
Follow-up instructions after review (1)
为图书借阅系统设计 SQLite Schema 和迁移文件，包含书籍、读者、借阅记录，外键、ISBN 唯一和活动借阅唯一约束；用临时 SQLite 实际验证约束。 交付文件：schema.sql、verify.py、schema.md。 验收要点： - 重复 ISBN 和重复未归还借阅被拒绝 - 非法外键被拒绝 - MCP 连接不在本例验证范围 这是使用合成演示输入或指定公开资料的真实操作案例。请先读取工作空间输入，再实际生成文件并自检。所有写入、脚本与临时服务限于当前工作空间；临时服务只监听 127.0.0.1，执行后关闭；不触达外部收件人、不发布、不付款、不更改系统设置，不调用子智能体。需要公开资料时可读取给定 URL。只启用完成任务必需的工具。另写 report.md（中文）和 report.en.md（英文），简洁、完整，包含输入、处理步骤、真实结果、检查方式和局限；失败或未验证部分如实记录，不能宣称成功。最终回复用中文列出产物、关键结果与待核实项。
The recorded run used an isolated demonstration workspace. Review the commands and file paths before approving operations in your own workspace.
What actually ran
The task was submitted through the Tokeny input box. An online model read the inputs and executed tools. This run covers the local sample task and its actual files. Results and checks are recorded below.
Results and verification
Input
The workspace inputs requirements.md and ENVIRONMENT.md were read. The requested local SQLite design requires one inventory row per book, unique reader email, a non-null borrowing time, at most one unreturned loan for the same book, and protection against deleting a book referenced by loan records. No production database may be connected.
Steps
- Created
schema.sqlwithbooks,readers, andloanstables. - Expressed the rules with unique constraints, foreign keys,
ON DELETE RESTRICT,NOT NULL, a check constraint, and a partial unique index. - Created
verify.py, which opens a SQLite database in a temporary directory and enablesPRAGMA foreign_keys. - Inserted valid sample data and attempted each invalid operation, expecting
sqlite3.IntegrityError.
Actual Results
The verification script passed: duplicate ISBN, invalid book and reader foreign keys, duplicate unreturned loan, and deletion of a referenced book were rejected. After the original loan was marked returned, a later loan for the same book was accepted. The database was temporary and was cleaned up after the connection closed.
Check Method
Run python verify.py in the current workspace. The script prints one PASS line per check and fails with an assertion if an expected constraint rejection does not occur. MCP connectivity is outside this verification scope.
Limitations
This test covers SQLite constraints and representative SQL only. It does not cover application-level concurrent transactions, authorization, overdue calculation, renewal policy, multi-copy inventory, or production backup and recovery. Timestamps are stored as ISO-8601 text; an application should standardize timezone and formatting.
Download and inspect the outputs
The archive contains the files created during this run. Use these checks when adapting the example to your own data.
- Reconcile the reported results against the downloadable sample inputs.
- Open the generated files and check the contents relevant to your task.
- Review the limitations and any unverified steps in the report.