Advisory · 02

Airtable schema.

The single authoritative CRM base for DTTT. Four tables hold every advisory record — members, engagements, work blocks and activity types. Credits flow upward through linked tables, so a logged Work Block updates the Member's remaining balance automatically. This page captures the live schema with field IDs, formulas and operational rules.

Core tables

CRM

Base name

Live

Schema status

May 26

Last migration

Base details connection info

Base ID

appLkX81B97Aunjxm

The only authoritative base for CRM data. URL: https://airtable.com/appLkX81B97Aunjxm

Related base

app1fN0tuzTW16VYZ

Fáilte Ireland Advisory — research and programme tracking only. Not an engagement-hours base. Don't log advisory work there.

Credit flow how credits roll up

Work Block (Hours × Band Credits/hr)
       ↓ Credits Used auto-calculates per Work Block

Advisory Ledger (Credits Used Rollup = SUM of linked Work Blocks)
       ↓

Member (Credits Used = SUM of all linked engagements)
       ↓

Compare with Allocation → Remaining · % · Hours equivalent

Two parallel pools Advisory Credits — for strategic work, banded E/D/C/B/A. 9,000/year at Advisory tier.
Session Credits — for workshops, keynotes, coaching. Fixed cost per activity. 10/year for Impact and Advisory tiers.

Table 01 · Members top of stack

Members

One record per advisory member organisation. Holds annual allocations and the rolled-up balance picture. Pro-rata adjustments for mid-year activations live here.

tbljsopy24z698sX7

Field	Field ID	Type	Purpose
Name	`fld7CwO2780Z0PXyd`	text · primary	Organisation name
Membership tier	`fldlTPyBBDylz8Wau`	singleSelect	Engage / Evolve / Impact / Advisory / Corporate / Accelerate
Advisory Credits Allocation	`fldR3I2lLBo1ineOc`	number	Annual entitlement (Advisory tier = 9,000)
Session Credits Allocation	`fldb3ouRuXj0TfUYY`	number	Annual entitlement (Impact / Advisory = 10)
Research Credits Allocation	`flduk5naZ3YTqeIwg`	number	Annual entitlement (Evolve+ = 3, internal pool)
Advisory Ledger	`fld3oGNRqSm4eObSh`	link → Ledger	All engagements for this member
Credits Used	`fldgJrKuDirNmoEQv`	rollup	SUM of Advisory Ledger.Credits Used (Rollup)
Credits Remaining	`fldpmHDZlY885hDXQ`	formula	`{Advisory Credits Allocation} - {Credits Used}`
Credits Used (%)	`fldUvQDNrynB62GhL`	formula	`IF({Allocation} > 0, ({Credits Used} / {Allocation}) * 100, 0)`
Equivalent Hours at Band C	`fldfwQv1usOhMKPWO`	formula	`{Credits Used} / 100`
Session Credits Used	`fldAAjg98uku7zYnq`	rollup	SUM of Advisory Ledger.Session Credits Used
Session Credits Remaining	`fldYtUV7cU9I4ZZvV`	formula	`{Session Allocation} - {Session Used}`
Session Credits Used (%)	`fldaSRvV9Q4ybNRnu`	formula	Percentage equivalent for sessions

Table 02 · Advisory Ledger engagement-level

Advisory Ledger

Parent record per engagement. One row equals one piece of work with a Job Reference (e.g. FI-2026-006). The ledger holds engagement metadata, AI transparency scoring, and the rollup of all linked Work Blocks.

tbl7zE17DIvjLoneS

Field	Field ID	Type	Purpose
Job Reference	`fldyyFU6clM4Az6Lp`	text · primary	`MEMBER-YEAR-NNN` pattern
Member	`fldG4yLBNjsLHtakU`	link → Members	The member organisation
Request Title	`fld07yWbjE3veO7Mi`	text	Short engagement title
Status	`fldbAjcDu0PA6cdzT`	singleSelect	Delivered / Scheduled / In Progress / Requested / Closed / Complete
Request Date	`fldH1DwR8AJwlHVl3`	date	When the request came in
Request Summary	`fldpCImbBlNoP8934`	multilineText	What the client asked for
Engagement Type	`fldYuOEAi8al0s1qt`	singleSelect	Advisory / Workshop·Session / Keynote / Ad-hoc / Knowledge Programme / Bespoke Research
Counts Towards Allowance	`fldDmBFSfQsPWoJ8G`	singleSelect	Advisory Hours / Session Credits / Research Credits / Not Deductible
Activity Type	`fld6Ptkyfs8OdZTK9`	link → Activity	The menu item
Session Credits Used	`fldtfOipIzq9kAf2S`	number	Cost for Session-pool engagements
Credits Used (Rollup)	(rollup)	rollup	SUM of linked Work Blocks.Credits Used
Start Date	`fldQEbKpbx28kk26J`	date	When work began
Completed Date	`fldTJ6HanbyLd715d`	date	When delivered
Work Summary	`fld2hrt81VJjPX9Y7`	multilineText	What was delivered
Methodology & Approach	`fldqjnFik9kD6Vbn9`	multilineText	How it was done
Advisory Work Blocks	`fld8UXUneyo6iBbEI`	link → Blocks	Child blocks
AI Grade	`fldKFA3crSf1MKnxC`	singleSelect	AITC grade A–E
AI Percentage	`fldGuxmfzthLCOgED`	number	% of work that was AI-assisted
AI Productivity Score	`fld5GSRa9Z507hvyl`	singleSelect	AITC productivity score
Delivery Extension Score	`fldIXNJVQCfKzLB2j`	singleSelect	AITC delivery extension score
LLMs / Models Used	`fldZPxyYISrA2xEgu`	multipleSelects	Specific models
AI-Supported Tasks	`fld1yLlZbBg4erjuH`	multipleSelects	What AI did
AI Note	`fldQF9WhapJuMs6Ol`	multilineText	Free-text on AI involvement

Table 03 · Work Blocks per-session detail

Advisory Work Blocks

Child records linked to an engagement. One row equals one discrete unit of work at a single band. Credits Used auto-calculates from Hours × Band Credits/hr. Every log lands here.

tblelINfgijh1sRZU

Field	Field ID	Type	Purpose
Block ID	`fldpxWRXhjTmmuRiR`	text · primary	`JOBREF.N` pattern (e.g. `FI-2026-006.3`)
Engagement	`fldvhhLCHZjI8TpgV`	link → Ledger	Parent engagement
Advisory Activity Types	`fldpuhfdOICdD9B2x`	link → Activity	The band attribution
Block Title	`fld21TEce5wUw505w`	text	Short title
Date	`fldZlRpb9RQOJuJQw`	date	When the work happened
Hours	`fld6bGZbXVRqC3uJM`	number	In 0.25-hour increments
Credits Per Hour	`fldqOCGDdDV9Xgx5T`	lookup	From linked band
Credits Used	`fldTuYIcB3el8eEeq`	formula	`IF(AND({Hours}, {Credits Per Hour}), {Hours} * {Credits Per Hour}, 0)`
Summary	`fldCWE4Xa5vaC87Gf`	multilineText	What was done
Notes	`fldpekRs5JbByHPRm`	multilineText	Internal notes

Table 04 · Activity Types reference

Advisory Activity Types

Reference table of activity types and their credit costs. Linked from both Advisory Ledger (one per engagement) and Work Blocks (one per block). Each carries a default band that enforces consistency. Record IDs are listed in full on the credits page.

tblbldnEHNZIXSaTH

Operational rules how the schema is used

01 Job Reference numbering

Pattern: MEMBER-YEAR-NNN (e.g. FI-2026-006, CTB-2026-001).

MEMBER — short prefix per organisation, agreed at first engagement
YEAR — four-digit year
NNN — sequential per member per year, three digits

Gaps from deleted records are preserved. Don't renumber.

02 Engagement vs Work Block

A new engagement = a new piece of work with its own deliverable scope, separately requested by the client. A new Work Block = a unit of time within an existing engagement (a session, a review round, a colleague's contribution). Reviews, meetings and colleague contributions are Work Blocks within an existing engagement — not new engagements — unless they're substantive enough to warrant their own scope and deliverable.

03 Band attribution

Each Work Block gets a single band. If work crosses bands, split into multiple Work Blocks. Bands attribute the seniority of the person doing the work, not the nature of the work — the same drafting task done by Nick is Band E, the same task done by a coordinator is Band A.

04 Counts Towards Allowance

Set on every Advisory Ledger record:

Advisory Hours — Advisory pool work (banded)
Session Credits — Session pool work (fixed cost per activity)
Research Credits — internal-only, Knowledge Programme work
Not Deductible — ad-hoc support, member-care, light correspondence

05 Allocation lives at the Member level

Allocation is set per member, not per engagement. A member with one Advisory Membership has 9,000 Advisory Credits for the year, drawn across all engagements. For parallel memberships (e.g. two ring-fenced 9,000-credit pools), use separate Member records — one per pool — rather than doubling the allocation on a single record. This keeps engagement reporting properly ring-fenced.

06 Pro-rata activations

For mid-year activations, set the member's Advisory Credits Allocation to the pro-rated figure for the remaining months. Update to 9,000 at year-end.

Schema change log migrations

11 May 2026 · Band architecture implemented

Activity Types extended with five Advisory bands (E/D/C/B/A)
Work Blocks extended with band link, Credits Per Hour lookup, Credits Used formula
Advisory Ledger Member field converted singleSelect → multipleRecordLinks (proper relational link)
Members extended with allocation rollups for Advisory and Session Credits, plus formulas for Remaining, % Used, Equivalent Hours at Band C
Duplicate fields cleaned up (Members/Members 2 on Ledger; Advisory Engagements on Members)
Equivalent Hours formula bug fixed — was reading Allocation, now reads Credits Used
Stockholm Business Region restructured to itemise session credit draws (5 + 4 + 1 = 10, fourth informal session marked Not Deductible)
Visit Düsseldorf DT-2026-001 reclassified as Ad-hoc / Not Deductible / Complete

Prior to May 2026 · hours-based Single "Strategic Advisory engagement" Activity Type with hours rolled up directly. No band attribution. See the deprecated advisory-hours skill for historical reference.

Linked skill dttt-advisory-credits writes to this base. Significant schema changes need to be reflected in the skill — particularly the Activity Type record IDs and the Work Blocks fields. See the skills catalogue entry.