A Three-Layer Architecture for AI Reporting Skills

I. Background and the Problem

I’d built a handful of AI “skills” for generating recurring business reports — a weekly business review, an industry-contribution breakdown, a monthly business review, and a few others. Each one worked, but as the set grew, some structural problems showed up:

Problem	Description
Duplicated data pulls	Every skill had its own data-fetching logic, so the same metric could get pulled — and computed — more than once.
Definition drift	Metric definitions were scattered across skills, so numbers would occasionally stop matching each other.
High maintenance cost	Changing one definition (say, a reporting-week boundary or an org-level filter) meant hunting down and editing it in multiple places.
Not shareable	Hardcoded local paths and parameters meant that even if someone else installed a skill, it wouldn’t actually run for them.

II. Target Architecture: Three-Layer Decoupling

The application layer (application-skill) depends on the analysis-standards layer (analysis-skill), which in turn depends on the data layer (data-skill).

1. data-skill (data layer): the single source of truth for data access — encapsulates all data-fetching logic and metric-definition constraints.
2. analysis-skill (analysis-standards layer): a unified analytical knowledge base. Does no data-fetching of its own — pure written standards.
3. application-skill (presentation layer): focused purely on output/presentation, calling the two layers above as needed.

---

III. Responsibilities by Layer

3.1 data-skill (Data Layer)

Responsibility: the sole entry point for data, encapsulating all fetching logic and metric definitions.

What it contains:

• Dashboard data extraction (browser-automation against internal BI tools)
• SQL-based data pulls (e.g. gross transaction value or order count broken out by channel)
• BI-API-based data pulls (including the quirks of the internal query syntax)
• Metric definitions: SQL/API filter conditions (org-unit filters, business-group filters, date ranges), calculation formulas (e.g. take-rate = revenue ÷ gross transaction value)
• Local cache management (SQLite, partitioned by brand × metric × week/day)
• Cache-refresh policy (scheduled weekly refresh, or manual force-refresh)

Output: a standard JSON schema that any downstream consumer can parse.

{
  "brand": "brand_a",
  "week": "WK11",
  "begin_date": "2026-03-05",
  "end_date": "2026-03-11",
  "metrics": {
    "gross_transaction_value": {"this_week": 2104000000, "wow": 0.0258, "yoy": 0.155},
    "order_count": {"this_week": 123456, "wow": -0.01, "yoy": 0.08}
  },
  "industry_breakdown": [
    {"industry": "category_a", "gross_transaction_value": 350000000, "wow": 0.03, "yoy": 0.12},
    {"industry": "category_b", "gross_transaction_value": 280000000, "wow": -0.01, "yoy": 0.08}
  ],
  "channel_breakdown": [
    {"channel": "search", "gross_transaction_value": 620000000, "wow": 0.02, "yoy": 0.18},
    {"channel": "recommendation_feed", "gross_transaction_value": 410000000, "wow": -0.03, "yoy": 0.05}
  ]
}

Cache storage design (tiered SQLite)

Data lands in a single analytics.db, with table names following a {granularity}_{dimension} convention — weekly_org, weekly_industry, weekly_org_industry, monthly_org, daily_org, and so on.

A metadata table (cache_meta) governs the refresh policy per table:

CREATE TABLE cache_meta (
  table_name      TEXT PRIMARY KEY,
  refresh_mode    TEXT,      -- 'scheduled' | 'on_demand'
  cron_expr       TEXT,      -- e.g. '0 8 * * 4' (Thursday 8am)
  last_updated    TEXT,      -- ISO timestamp
  ttl_hours       INTEGER,   -- cache validity window in on-demand mode
  source_type     TEXT,      -- 'bi_api' | 'hive_sql' | 'browser_extract'
  source_config   TEXT       -- JSON, records the query parameters
);

Two refresh strategies:

1. Scheduled: for data a weekly report always needs. Register a cron job that periodically overwrites (REPLACE INTO) or appends by time partition.
2. On-demand: for dimensions only needed during deeper analysis, not worth refreshing every week. Before use, check last_updated + ttl_hours; if the cache is still valid, read directly — if expired, trigger a fresh query, write it, and update the metadata.

def get_data(table_name, query_fn):
    if is_cache_valid(table_name):   # check ttl
        return read_from_db(table_name)
    else:
        data = query_fn()            # trigger BI API / Hive SQL
        write_to_db(table_name, data)
        update_meta(table_name)
        return data

Why SQLite over CSV/JSON: supports WHERE/JOIN queries across dimensions; multi-dimension queries can join tables at different granularities; appending historical data doesn’t overwrite old data; metadata can be managed independently; and the whole data layer stays to one or two files instead of sprawling into dozens.

3.2 analysis-skill (Analysis-Standards Layer)

Responsibility: a unified analytical knowledge base — does no data-fetching or generation itself (pure written standards).

Where a given standard belongs (data-skill vs. analysis-skill):

Standard Type	Belongs To	Why
SQL/API filter conditions (org-unit filters, business-group filters, date-range definitions)	data-skill	Needed at fetch time — a lower-layer concern.
Metric calculation formulas (take-rate = revenue ÷ GTV)	data-skill	Affects the computed result itself.
Metric business definitions (what “precise-match search volume” actually means)	analysis-skill	Shapes the analytical narrative and perspective.
How a conclusion should be written, and which framework to use	analysis-skill	Pure analytical standard.

Core principle: filter/calculation constraints live in data-skill; analytical perspective lives in analysis-skill. Data flows one direction between the two layers (data → analysis).

What analysis-skill contains:

Module	Content
Metric business definitions	Definitions of key metrics, and the analytical lens for each
Analytical frameworks	The weekly-review’s four-section structure, the monthly-review framework, the industry-contribution breakdown method
Business knowledge base	Seasonality patterns, competitive-analysis lenses, intra-month rhythms
Formatting standards	Decimal-place rules, color conventions (positive = blue, negative = red), unit conversions

Where report templates live Different reports need different specific formats, so templates split into two tiers:

1. The analysis layer (analysis-skill) owns: structural standards, independent of output format
   • The business-overview report’s module composition: totals → volume/price drivers → health-check summary → keyword highlights per section
   • The deep-dive report’s four-part structure: channel → industry → business levers → key watch items
   • Priority-flagging convention (🔴/🟡/🟢)
   • The analytical lens for each section
2. Each report-layer skill owns: format constraints (defined independently per report)

Report	Format Constraint
Weekly-review text summary	Concise, no more than five bullets, roughly 300 words
Weekly-review HTML report	Populates a --panorama-text / --insight-text parameter
Monthly review	Monthly view, must cover both month-over-month and year-over-year, can run longer
Industry-contribution dashboard	Pure chart output, no text template needed

Core principle: the analysis layer defines structure; the report layer defines format. Report-layer skills reference the analysis layer’s structural standard but decide their own word count and presentation on their own.

3.3 Application Layer (Report Layer)

Responsibility: focused purely on “presentation” — agnostic to where the data comes from or how metrics are defined.

Skill	Input	Output
weekly-report-skill	data-skill’s JSON + analysis-skill’s structural standard	HTML report (channel/industry/operations/traffic, four sections)
dashboard-skill	data-skill’s JSON + analysis-skill’s structural standard	Interactive industry-contribution dashboard
monthly-report-skill	data-skill’s JSON + analysis-skill’s structural standard	Monthly HTML report
peak-analysis-skill	data-skill’s historical data (no analysis-skill needed)	Historical peak-analysis table

Each report skill’s own manifest declares its dependency chain up front: run data-skill first to fetch data, then load analysis-skill’s standards, then generate and send the report.

---

IV. Designing for Shareability

Self-containment principle: no skill depends on a local absolute path; every script lives inside its own skill directory; dataset IDs are passed as parameters, never hardcoded. Before installing a report skill, install data-skill and analysis-skill first — then everything runs end to end.

Directory structure:

~/.skills/
├── data-skill/
│   ├── SKILL.md            <- metric definitions + fetch logic docs
│   └── scripts/
│       ├── query_overview.py
│       ├── query_industry.py
│       ├── run_channel_sql.py
│       └── cache_manager.py
├── analysis-skill/
│   ├── SKILL.md            <- analytical framework + report structure + formatting standards
│   └── templates/
│       ├── panorama_structure.md   <- business-overview structural standard (no word-count/param constraints)
│       └── insight_structure.md    <- deep-dive structural standard (no word-count/param constraints)
└── weekly-report-skill/
    ├── SKILL.md            <- declares dependencies + execution steps + format constraints (word count/params)
    └── scripts/
        └── generate_report.py

---

V. Migration Path (Phased)

• Phase 1 (within a week, low risk): extract analysis-skill
  • Pull the scattered analytical standards out of working notes and organize them into a standalone skill
  • Leave all existing data-fetching logic untouched — just restructure the standards documents
  • Validation: have the weekly-report skill load analysis-skill’s standards before writing its analysis
• Phase 2 (2–4 weeks): build the data-skill skeleton
  • Extract an existing report’s data-fetching scripts into a shared module
  • Build the SQLite cache, starting with weekly report data (high-frequency) and historical peak data (low-frequency)
  • Validation: have both the weekly report and industry-contribution report pull from the shared cache
• Phase 3 (after ~1 month): slim down the report layer
  • Gradually replace each report skill’s own data-fetching logic with calls into data-skill
  • Keep each skill’s own generation scripts (generate_report.py, etc.), just swap out where the data comes from

---

VI. Sharing and Maintenance

6.1 How It’s Shared

The three core skill types (data-skill, analysis-skill, and each report-layer skill) get published to a shared internal skill marketplace, with access and edit permissions opened up to everyone who needs them, supporting collaborative maintenance.

6.2 Version-Update Process

During day-to-day local use, a version update is triggered whenever:

• A data definition changes (a filter field’s logic changes, a metric is added or retired)
• The analytical framework or a report template changes meaningfully
• A bug affecting data accuracy gets fixed

Update steps:

1. Validate the fix locally (run it, compare the numbers)
2. Publish a new version to the marketplace with clear release notes (what changed, what’s affected)
3. Notify everyone downstream to pull the update locally
4. Once everyone confirms they’ve updated, archive the old version

6.3 Things to Watch For

Item	Note
Backward compatibility	Changes to data-skill’s JSON schema need versioning (v1/v2) so the report layer doesn’t break outright.
Scope of change communicated	Every release clearly states which skills are affected and whether data needs to be re-pulled.
No silent local changes	Definition-level changes never get quietly absorbed locally — they must go through the marketplace’s version-update process, to keep everyone consistent.

---

VII. Risks and Mitigations

Risk	Description	Mitigation
Stale cache	Report runs on stale data if the cache didn’t refresh	Timestamp everything; auto-check on each call; auto-refresh if expired
Breaking schema change	data-skill’s JSON schema changes and the report layer breaks	Version the schema (v1/v2), maintain backward compatibility
Heavy agent context	Loading three SKILL.md files at once	Keep analysis-skill lean — only the must-follow standards, with background material moved to an appendix
Mismatched definitions after sharing	Someone else’s dataset IDs differ from yours	Make data-skill’s dataset IDs a parameter, never hardcoded

---

VIII. Conclusion

Recommended approach: the three-layer architecture (data-skill + analysis-skill + report-layer skills), migrated incrementally through Phase 1 → Phase 2 → Phase 3.

What to do right now: Phase 1 — extract analysis-skill. Low risk, high payoff, and doable within a week.