# 09 — Stack A Output Spec (SQLite Schema + sections_json)

## SQLite databases produced by seed_databases.py

### pages.sqlite

```sql
CREATE TABLE pages (
  id INTEGER PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  template TEXT NOT NULL,       -- home | static | classes | schedule | glossary | blog
  title TEXT NOT NULL,
  meta_description TEXT,
  canonical_url TEXT,
  og_image TEXT,
  schema_json TEXT,
  hero_eyebrow TEXT,
  hero_h1 TEXT,
  hero_lead TEXT,
  sections_json TEXT,           -- JSON array of section objects
  updated_at TEXT
);
```

### nav.sqlite

```sql
CREATE TABLE nav_items (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  href TEXT NOT NULL,
  display_order INTEGER DEFAULT 0,
  is_cta INTEGER DEFAULT 0      -- 1 = render as button
);
```

### blog.sqlite

```sql
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  title TEXT NOT NULL,
  excerpt TEXT,
  body_html TEXT,
  author TEXT DEFAULT 'Admin',
  published_at TEXT,
  og_image TEXT,
  tags TEXT
);
```

### testimonials.sqlite

```sql
CREATE TABLE testimonials (
  id INTEGER PRIMARY KEY,
  quote TEXT NOT NULL,
  author_name TEXT NOT NULL,
  author_role TEXT,
  is_featured INTEGER DEFAULT 0,
  display_order INTEGER DEFAULT 0
);
```

### glossary.sqlite (if site has a glossary)

```sql
CREATE TABLE terms (
  id INTEGER PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  term TEXT NOT NULL,
  pronunciation TEXT,
  definition TEXT NOT NULL,
  category TEXT NOT NULL,
  level TEXT NOT NULL,
  display_order INTEGER DEFAULT 0
);
```

### faq.sqlite (if site has FAQs)

```sql
CREATE TABLE faqs (
  id INTEGER PRIMARY KEY,
  question TEXT NOT NULL,
  answer TEXT NOT NULL,
  category TEXT NOT NULL,
  display_order INTEGER DEFAULT 0
);
```

## sections_json section types

Each page row's sections_json is a JSON array. Each element is a typed object:

### text_split

Two-column: text on one side, image on the other. CTAs optional.

```json
{
  "type": "text_split",
  "eyebrow": "",
  "h2": "",
  "body": "",
  "img": "/assets/images/x.webp",
  "img_alt": "",
  "cta_label": "",
  "cta_href": "",
  "reverse": false
}
```

### feature_cards

Grid of 3-4 cards, each with icon + title + body.

```json
{
  "type": "feature_cards",
  "eyebrow": "",
  "h2": "",
  "lead": "",
  "cards": [
    {"icon": "", "title": "", "body": ""}
  ]
}
```

### accordion

Collapsible question/answer pairs.

```json
{
  "type": "accordion",
  "eyebrow": "",
  "h2": "",
  "items": [
    {"q": "", "a": ""}
  ]
}
```

### cta_band

Full-width call-to-action with headline + button.

```json
{
  "type": "cta_band",
  "eyebrow": "",
  "h2": "",
  "lead": "",
  "btn_label": "",
  "btn_href": "",
  "variant": "forest"
}
```

### text_block

Simple text heading + body.

```json
{
  "type": "text_block",
  "eyebrow": "",
  "h2": "",
  "body": ""
}
```

### stats_strip

Grid of stat + label pairs.

```json
{
  "type": "stats_strip",
  "stats": [
    {"value": "", "label": ""}
  ]
}
```

### topic_pills

Row of clickable topic/tag items.

```json
{
  "type": "topic_pills",
  "eyebrow": "",
  "h2": "",
  "items": [
    {"label": "", "href": ""}
  ]
}
```

### form_contact

Embedded contact form.

```json
{
  "type": "form_contact",
  "h2": "",
  "lead": ""
}
```

### booking_options

Pricing table or service options grid.

```json
{
  "type": "booking_options",
  "eyebrow": "",
  "h2": "",
  "options": [
    {"name": "", "price": "", "features": [], "cta_label": "", "cta_href": ""}
  ]
}
```

## Divi module → section type mapping

| Divi Module | AM Section Type | Notes |
|---|---|---|
| et_pb_blurb | feature_cards item | Extract icon, title, body |
| et_pb_toggle | accordion item | Extract q/a pairs |
| et_pb_cta | cta_band | Extract headline, button text, href |
| et_pb_pricing_table | booking_options | Extract plan names, prices, features |
| et_pb_testimonial | testimonials.sqlite row | Extract quote, author, role |
| et_pb_text | text_block | Extract body copy |
| et_pb_code | text_block (sanitized) | Extract HTML, remove script tags |
| et_pb_number_counter | stats_strip item | Extract number, label |
| et_pb_button | cta_band (minimal) | Extract button text, href |
| et_pb_menu / header | nav.sqlite rows | Extract label, URL, menu order |

## seed_databases.py structure

Every migration generates a seed_databases.py at `build/seed_databases.py`.

Template structure:

```python
import sqlite3
import json
from pathlib import Path

pages_path = Path('src/api/data/pages.sqlite')
nav_path = Path('src/api/data/nav.sqlite')
blog_path = Path('src/api/data/blog.sqlite')
testimonials_path = Path('src/api/data/testimonials.sqlite')

def seed_pages(conn):
    """INSERT all pages with sections_json and hero data."""
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS pages (
            id INTEGER PRIMARY KEY,
            slug TEXT UNIQUE NOT NULL,
            template TEXT NOT NULL,
            title TEXT NOT NULL,
            meta_description TEXT,
            canonical_url TEXT,
            og_image TEXT,
            schema_json TEXT,
            hero_eyebrow TEXT,
            hero_h1 TEXT,
            hero_lead TEXT,
            sections_json TEXT,
            updated_at TEXT
        )
    ''')
    
    pages = [
        ('home', 'home', 'Home', 'Home meta', '/home', '', '{}', 
         '', 'Welcome', 'Lead text', json.dumps([...])),
        # ... more rows
    ]
    for page in pages:
        cursor.execute(
            'INSERT INTO pages (slug, template, title, meta_description, canonical_url, og_image, schema_json, hero_eyebrow, hero_h1, hero_lead, sections_json, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime("now"))',
            page
        )

def seed_nav(conn):
    """INSERT navigation items from nav.json."""
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS nav_items (
            id INTEGER PRIMARY KEY,
            label TEXT NOT NULL,
            href TEXT NOT NULL,
            display_order INTEGER DEFAULT 0,
            is_cta INTEGER DEFAULT 0
        )
    ''')
    
    items = [
        ('Home', '/', 0, 0),
        ('About', '/about', 1, 0),
        ('Contact', '/contact', 2, 1),
        # ... more rows
    ]
    for item in items:
        cursor.execute(
            'INSERT INTO nav_items (label, href, display_order, is_cta) VALUES (?, ?, ?, ?)',
            item
        )

def seed_blog(conn):
    """INSERT blog posts if site has a blog."""
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY,
            slug TEXT UNIQUE NOT NULL,
            title TEXT NOT NULL,
            excerpt TEXT,
            body_html TEXT,
            author TEXT DEFAULT 'Admin',
            published_at TEXT,
            og_image TEXT,
            tags TEXT
        )
    ''')
    # ... INSERT rows

def seed_testimonials(conn):
    """INSERT testimonials if present."""
    # ... CREATE TABLE + INSERT rows

if __name__ == '__main__':
    for db_path, seeder_fn in [
        (pages_path, seed_pages),
        (nav_path, seed_nav),
        (blog_path, seed_blog),
        (testimonials_path, seed_testimonials),
    ]:
        if db_path.exists():
            db_path.unlink()  # clear if re-running
        conn = sqlite3.connect(db_path)
        seeder_fn(conn)
        conn.commit()
        conn.close()
        print(f"seeded: {db_path.name}")
    
    print("All databases seeded successfully.")
```

## Content validation checklist

After staging seed_databases.py and before running it:

- [ ] No raw Divi shortcode residue: `[et_pb_`, `[vc_`, etc.
- [ ] No em-dashes (—): replace with commas, periods, or spaces
- [ ] No "Netherlands" or other location-specific copy (unless intentional)
- [ ] hero_h1 is 5-10 words (brand voice, not generic)
- [ ] Each section type matches the spec above (no custom types)
- [ ] All images are `/assets/images/{name}.webp` (not absolute URLs)
- [ ] All CTAs point to correct slugs (`/about`, `/contact`, etc.)
- [ ] Nav items include at least 3 menu links
- [ ] At least one nav item has `is_cta=1` (usually Contact or Book)