AM-WEB/arisingmedia-web-sops

Fork 0

Files

T

acobham 94f7a1f72a recent updates

2026-06-09 18:31:59 +02:00

8.0 KiB

Raw Permalink Blame History

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

SQLite databases produced by seed_databases.py

pages.sqlite

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

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

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

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)

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)

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.

{
  "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.

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

accordion

Collapsible question/answer pairs.

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

cta_band

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

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

text_block

Simple text heading + body.

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

stats_strip

Grid of stat + label pairs.

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

topic_pills

Row of clickable topic/tag items.

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

form_contact

Embedded contact form.

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

booking_options

Pricing table or service options grid.

{
  "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:

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)

8.0 KiB Raw Permalink Blame History