Skip to content
Singular DB Sprint Framework Docs

🔍 Phase 1 - Discovery

Objective

Section titled “Objective”

Understand the problem, capture requirements, and build the first visual representation of the data structure with AI assistance.

Key Activities

Section titled “Key Activities”
  • Discovery meetings and client information gathering (prototypes, documentation, workflows, transcriptions).
  • Data type and compliance assessment.
  • AI-assisted generation of an initial DBML draft including:
    • Tables
    • Fields
    • Field types (aligned with Airtable types)
    • Business and technical field descriptions

👉 Fill out the Compliance Assessment Form

Final Deliverables

Section titled “Final Deliverables”
  • Discovery Document + Initial DBML Schema Draft.
  • Initial Compliance and Risk Assessment.

DBML Guidelines

Section titled “DBML Guidelines”

All implementation must be based on a DBML file that documents the database schema. This file must follow these principles:

  • Use snake_case for all table and field names.
  • Each field must include a note attribute with:
    • business desc: a non-technical description of the field purpose, written for business users. This will be used as the Airtable field description.
    • technical desc: a technical description of the field, for use by developers.
  • At the end of each table, include a note summarizing the purpose of the table itself.

✅ Example DBML

Section titled “✅ Example DBML”
Table media_outlet_print_details {
  id autonumber [note: 'business desc: Unique identifier for each print media outlet detail record., technical desc: Type: autonumber.']
  record_id formula [note: 'business desc: System-generated unique identifier used for referencing this record across Airtable and integrations., technical desc: Type: formula.']


  media_outlet_id foreignKey [note: 'business desc: Link to the associated media outlet record., technical desc: Type: foreignKey, references media_outlets table.']


  subcategory singleSelect [note: 'business desc: Defines the type of print media as either a newspaper or a magazine, used to distinguish publication format and distribution type., technical desc: Type: singleSelect, Options: Newspaper, Magazine.']
  is_publication_free checkbox [note: 'business desc: Indicates whether the publication is distributed free of charge to readers., technical desc: Type: checkbox.']
  average_circulation number [note: 'business desc: Estimated average number of printed copies distributed per publishing cycle., technical desc: Type: number.']
  price_per_copy currency [note: 'business desc: Price charged per individual printed copy if applicable., technical desc: Type: currency.']
  sample_publication_upload attachment [note: 'business desc: File upload of a sample issue of the print publication, commonly used for quality review., technical desc: Type: attachment.']


  ad_rate_full_page currency [note: 'business desc: Advertising cost for a full-page placement in the print media outlet., technical desc: Type: currency.']
  ad_rate_half_page currency [note: 'business desc: Advertising cost for a half-page placement in the print media outlet., technical desc: Type: currency.']
  ad_rate_quarter_page currency [note: 'business desc: Advertising cost for a quarter-page placement in the print media outlet., technical desc: Type: currency.']


  created createdTime [note: 'business desc: Timestamp when this record was first created, used for tracking and audit purposes., technical desc: Type: createdTime.']
  last_modified lastModifiedTime [note: 'business desc: Timestamp of the latest update to the record for change tracking., technical desc: Type: lastModifiedTime.']
  last_modified_by lastModifiedBy [note: 'business desc: User who last updated this print media outlet record., technical desc: Type: lastModifiedBy.']


  note: '### media_outlet_print_details Table - Stores detailed attributes and ad rates specific to print-based media outlets. Linked to core media_outlets table.'
}

✅ Prompt Example DBML

Section titled “✅ Prompt Example DBML”
# 🧠 Prompt: Generate DBML from Data Structure (Airtable-Compatible)


**✅ Airtable to DBML Data Modeling Prompt**


---


You are an expert database modeler tasked with converting business requirements into a DBML (Database Markup Language) data model **compatible with Airtable field types** and following specific documentation standards.


### 🎯 **Your Objective:**
Given a description of a business domain, output a **DBML schema** using Airtable-compatible data types, field names in **snake_case**, and documenting every field with a `note:` containing both a business description and a technical description.


### ✅ **Key Rules & Conventions:**


1. **Field Names:** Always use `snake_case`.
2. **Airtable Field Types → DBML Types Mapping:**


| Airtable Type        | DBML Type Equivalent | SQL Standard Equivalent |
|---------------------|---------------------|------------------------|
| singleLineText       | singleLineText       | VARCHAR                |
| longText             | longText             | TEXT                   |
| email                | email                | VARCHAR                |
| phoneNumber          | phoneNumber          | VARCHAR                |
| url                  | url                  | VARCHAR                |
| checkbox             | checkbox             | BOOLEAN                |
| number               | number               | NUMERIC                |
| currency             | currency             | NUMERIC                |
| percent              | percent              | NUMERIC                |
| date                 | date                 | DATE                   |
| createdTime          | createdTime          | TIMESTAMP              |
| lastModifiedTime     | lastModifiedTime     | TIMESTAMP              |
| lastModifiedBy       | lastModifiedBy       | VARCHAR (user ID)      |
| formula              | formula              | EXPRESSION             |
| linkedRecord         | linkedRecord         | FOREIGN KEY (1:1)      |
| multipleRecordLinks  | multipleRecordLinks  | FOREIGN KEY (1:N)      |
| singleSelect         | singleSelect         | ENUM                   |
| multipleSelects      | multipleSelects      | SET                    |
| autoNumber           | autoNumber           | SERIAL / AUTO_INCREMENT|
| attachments          | attachments          | BLOB / FILE REF        |


✅ **Use these DBML types exactly as written.**


---


### 📐 **Field Documentation Format (for `note:`):**
Each field must have a `note:` using this structure:


dbml
[note: 'business desc: <describe the business meaning in 20–50 words>, technical desc: Type: <AirtableType>, Options: <comma-separated list if applicable>, Reference to <referenced_table> if linked.']


## **For singleSelect and multipleSelects, list Options only inside the technical desc.
✅ For linkedRecord and multipleRecordLinks, include Reference to <table> inside the technical desc.


## Table Documentation Format (Table-Level note:):


At the end of each table, include:
note: '### <table_name> Table (airtable id: <table_airtable_id>) - <describe table purpose in business language>'


Use ### before the table name for clarity.
✅ Always include a placeholder or provided airtable id.


Other Mandatory Rules:


Always include standard control fields at the end of every table:


id autoNumber [note: '...']
record_id formula [note: '...']
created createdTime [note: '...']
last_modified lastModifiedTime [note: '...']
last_modified_by lastModifiedBy [note: '...']


The control fields must always appear last.


📝 Output Example (for one field):


company_name singleLineText [note: 'business desc: Name of the company associated with the record., technical desc: Type: singleLineText.']


Your Output Must Contain:


One Table <table_name> {} block per table.


All fields documented per the above standard.


Control fields at the end of each table.


Table-level note:.


No extra explanations outside the DBML block.


When ready, generate only valid DBML inside a code block.


Example Output:


dbml
Table media_outlet_print_details {
  id autonumber [note: 'business desc: Unique identifier for each print media outlet detail record., technical desc: Type: autonumber.']
  record_id formula [note: 'business desc: System-generated unique identifier used for referencing this record across Airtable and integrations., technical desc: Type: formula.']


  media_outlet_id foreignKey [note: 'business desc: Link to the associated media outlet record., technical desc: Type: foreignKey, references media_outlets table.']


  subcategory singleSelect [note: 'business desc: Defines the type of print media as either a newspaper or a magazine, used to distinguish publication format and distribution type., technical desc: Type: singleSelect, Options: Newspaper, Magazine.']
  is_publication_free checkbox [note: 'business desc: Indicates whether the publication is distributed free of charge to readers., technical desc: Type: checkbox.']
  average_circulation number [note: 'business desc: Estimated average number of printed copies distributed per publishing cycle., technical desc: Type: number.']
  price_per_copy currency [note: 'business desc: Price charged per individual printed copy if applicable., technical desc: Type: currency.']
  sample_publication_upload attachment [note: 'business desc: File upload of a sample issue of the print publication, commonly used for quality review., technical desc: Type: attachment.']


  ad_rate_full_page currency [note: 'business desc: Advertising cost for a full-page placement in the print media outlet., technical desc: Type: currency.']
  ad_rate_half_page currency [note: 'business desc: Advertising cost for a half-page placement in the print media outlet., technical desc: Type: currency.']
  ad_rate_quarter_page currency [note: 'business desc: Advertising cost for a quarter-page placement in the print media outlet., technical desc: Type: currency.']


  created createdTime [note: 'business desc: Timestamp when this record was first created, used for tracking and audit purposes., technical desc: Type: createdTime.']
  last_modified lastModifiedTime [note: 'business desc: Timestamp of the latest update to the record for change tracking., technical desc: Type: lastModifiedTime.']
  last_modified_by lastModifiedBy [note: 'business desc: User who last updated this print media outlet record., technical desc: Type: lastModifiedBy.']


  note: '### media_outlet_print_details Table (Airtable id: )- Stores detailed attributes and ad rates specific to print-based media outlets. Linked to core media_outlets table.'
}

Airtable Run Automation Scripts Guidelines

Section titled “Airtable Run Automation Scripts Guidelines”

All automation scripts must be documented and follow these principles:

  • Use clear and descriptive names for scripts
  • Include detailed comments explaining the script’s purpose and functionality
  • Follow Airtable’s best practices for automation
  • Document all input and output fields
  • Include error handling and validation

Example Airtable Run Automation Script Prompt

Section titled “Example Airtable Run Automation Script Prompt”
### PROMPT TEMPLATE - Airtable Run Automation Script (Resilient, Typed, OOP, SOLID)


---


### OBJECTIVE:
Write a resilient Airtable Run Automation script that performs a business function while abstracting table and field identifiers using clear variable mappings. The script must be written using Object-Oriented Programming (OOP) and must follow SOLID principles to ensure it is easy to maintain, extend, and update when business rules or field IDs change.


---


### HARD REQUIREMENTS (Non-Negotiable)
- Use **OOP** with clearly separated classes.
- Follow **SOLID** principles:
  - **S**ingle Responsibility: each class does one job.
  - **O**pen/Closed: extend behavior by adding methods/classes, not rewriting existing logic.
  - **L**iskov Substitution: classes should be replaceable without breaking usage (e.g., repositories).
  - **I**nterface Segregation: avoid "god classes"; keep class responsibilities narrow.
  - **D**ependency Inversion: business logic depends on abstractions (repositories/services), not raw Airtable calls scattered everywhere.
- NEVER hardcode field names inside logic functions. Always use Field IDs via constant maps.
- Avoid writing to read-only fields (formula, createdTime, lastModifiedTime, lastModifiedBy, autoNumber).


---


### REQUIRED ARCHITECTURE (OOP + Clean Layers)
Implement the script in a single file (Airtable limitation) but structured as modular classes:


1) **Description Block** (Top of file)
- Include objective, idempotency rules (if any), data sources, outputs, and constraints.


2) **Configuration Layer (Editable Only)**
- Constants only:
  - `TABLE_IDS`
  - `FIELD_MAPS`
  - Business labels (e.g., "Paid")
  - Scope settings (period rules, offsets, thresholds)
- Example:
  - `const CONFIG = { SNAPSHOT_OFFSET_DAYS: 1, PAID_STATUS_LABEL: 'Paid' };`


3) **Logger Class**
- Centralize logging with configurable verbosity levels:
  - Level A: minimal
  - Level B: audit logs (recommended default)
  - Level C: debug


4) **DateManager Class** (If any time logic exists)
- Responsible only for computing period windows, date keys, offsets, etc.


5) **Repository Layer (Data Access Layer)**
- Encapsulate Airtable reads/writes:
  - `BaseRepository` (shared helpers)
  - Domain repositories (e.g., `InvoiceRepository`, `ExpenseRepository`)
- Repositories must be the only place where Airtable `selectRecordsAsync`, `createRecordAsync`, `updateRecordAsync` are called.


6) **Calculator / Business Rules Layer**
- Encapsulate business logic in pure methods:
  - `calculateX()`, `computeY()`
- No Airtable calls inside calculators.


7) **Service / Orchestrator Layer**
- A single class that coordinates:
  - Load inputs
  - Query data using repositories
  - Compute results using calculators
  - Build payload
  - Write results (create/update)
  - Output set


8) **Main Execution Block**
- Parse `input.config()`
- Instantiate classes
- Run service
- Use `output.set()` to pass values downstream


---


### STRUCTURE (Must Follow)
1. **Description block at the top**
2. Define `TABLE_IDS`, `FIELD_MAPS`, `CONFIG`
3. Parse `input.config()` with validation
4. Instantiate OOP modules
5. Execute orchestrator/service
6. Perform Airtable actions via repositories only
7. Final `output.set()` values


---


### RESILIENT PATTERNS
- Use `const` mappings with Field IDs, never field names in logic:
  - `record.getCellValue(FIELDS.someField)`
- Use optional chaining for safe reads:
  - `record?.getCellValue(FIELDS.someField)`
- Wrap JSON parsing in `try/catch`:
  - `try { JSON.parse(x) } catch { ... }`
- Guard clauses for missing input / critical values:
  - `throw new Error('Missing required input: ...')`
- Idempotency (if required):
  - Implement "find existing record by unique key -> update else create".


---


### FIELD TYPE GUIDELINES


#### singleSelect
When writing:
- Must pass object format:
  - `{ name: 'Your Option Label' }`
- Never pass plain strings.


#### multipleRecordLinks
- Must pass:
  - `[{ id: someRecordId }]`
- Even when linking one record.


#### DO NOT write to read-only fields
- formula
- createdTime
- lastModifiedTime
- lastModifiedBy
- autoNumber


---


### ERROR HANDLING REQUIREMENTS
- Use clear, actionable errors:
  - `throw new Error('Missing invoice header table ID');`
- Wrap sensitive operations in try/catch and log context:
  - which step failed
  - what record/table was involved (IDs only)


---


### OUTPUT SETUP
At the end of the script, pass key outputs to downstream steps using `output.set()`:
- `output.set('mode', 'CREATE' | 'UPDATE')`
- `output.set('record_id', createdOrUpdatedRecordId)`
- `output.set('period_key', 'YYYY-MM')`
- Include relevant computed KPIs if requested.


---


### EXAMPLE FIELD MAP BLOCK (Required Format)
- Must use Field IDs:
  - `const FIELDS_XXX = { fieldName: 'fld...' };`


Example:
- `const FIELDS_INSERTION_ORDERS = { media_plan_drafts_id: 'fld...', ... };`


---


### LOOP STRUCTURE FOR BULK RECORDS (Example Pattern)
Use this pattern inside a repository method:


- `for (const record of records) { await table.createRecordAsync({ ... }) }`


Always write using Field IDs:
- `[FIELD_MAP.someField]: value`
- `[FIELD_MAP.selectField]: { name: 'Approved' }`
- `[FIELD_MAP.linkedRecord]: [{ id: linkedId }]`


---


### DELIVERABLE EXPECTATION
Generate a complete Airtable Automation script that:
- Runs in a single Airtable script file
- Implements OOP with clear class separation
- Adheres to SOLID principles
- Uses Field IDs everywhere
- Has clean logging and safe guards
- Is maintainable when business rules or Field IDs change


---