daylily-tapdb 0.1.37

Templated Abstract Polymorphic Database - A flexible object model library

daylily-tapdb

Templated Abstract Polymorphic Database — A flexible object model library for building template-driven database applications with PostgreSQL and SQLAlchemy.

Overview

TAPDB provides a reusable foundation for applications that need:

• Template-driven object creation — Define blueprints, instantiate objects from them
• Polymorphic type hierarchies — Single-table inheritance with typed subclasses
• Flexible relationships — DAG-based lineage tracking between instances
• Enterprise identifiers — Trigger-generated Meridian EUIDs with opaque-ID guidance
• Full audit trails — Automatic change tracking via database triggers
• Soft deletes — Records are never hard-deleted

Target use cases: LIMS, workflow management, inventory tracking, any system needing flexible template-driven objects with complex relationships.

System Architecture

Data Model

TAPDB uses three polymorphic single-table-inheritance tables linked by BIGINT identity primary keys (uid). Each row also carries a human-readable EUID (Enterprise Unique Identifier) generated by database triggers.

erDiagram
    generic_template ||--o{ generic_instance : "instantiates"
    generic_instance ||--o{ generic_instance_lineage : "parent_of"
    generic_instance ||--o{ generic_instance_lineage : "child_of"
    generic_instance ||--o{ audit_log : "tracked_by"

    generic_template {
        bigint uid PK "GENERATED BY DEFAULT AS IDENTITY"
        text euid UK "Meridian EUID (e.g. GT-00001-BXKQ7)"
        uuid tenant_id "nullable multi-tenancy scope"
        text polymorphic_discriminator "single-table inheritance"
        text category
        text type
        text subtype
        text version
        text instance_prefix "EUID prefix for spawned instances"
        jsonb json_addl "schema, defaults, layouts, actions"
    }

    generic_instance {
        bigint uid PK
        text euid UK
        uuid tenant_id
        text polymorphic_discriminator
        bigint template_uid FK
        jsonb json_addl "instance properties"
        text bstatus "lifecycle status"
        boolean is_deleted "soft delete flag"
    }

    generic_instance_lineage {
        bigint uid PK
        text euid UK
        uuid tenant_id
        bigint parent_instance_uid FK
        bigint child_instance_uid FK
        text relationship_type "e.g. contains, derived_from"
    }

    audit_log {
        bigint uid PK
        text euid UK
        text rel_table_name
        bigint rel_table_uid_fk FK
        text operation_type "INSERT / UPDATE / DELETE"
        jsonb old_value
        jsonb new_value
    }

Identifier Hierarchy

flowchart LR
    subgraph Internal
        UID["uid (BIGINT)"]
    end
    subgraph Business
        EUID["euid (Text, Meridian)"]
    end
    subgraph Tenancy
        TID["tenant_id (UUID)"]
    end

    UID -- "database PK / FK joins" --> UID
    EUID -- "human-facing lookups" --> EUID
    TID -- "row-level isolation" --> TID

    style UID fill:#3b82f6,color:#fff
    style EUID fill:#10b981,color:#fff
    style TID fill:#8b5cf6,color:#fff

Identifier	Type	Purpose
uid	BIGINT (identity)	Internal primary key, used in all FK joins
euid	TEXT (Meridian format)	Human-readable business identifier, auto-generated by triggers
tenant_id	UUID (nullable)	Logical multi-tenancy partition key

Lineage Graph

Instances form a directed acyclic graph (DAG) through lineage edges:

flowchart TD
    P1["🧪 Plate CX-00001"]
    S1["🧬 Sample CX-00002"]
    S2["🧬 Sample CX-00003"]
    E1["📦 Extract CX-00004"]
    L1["📊 Library CX-00005"]

    P1 -- "contains" --> S1
    P1 -- "contains" --> S2
    S1 -- "derived_from" --> E1
    E1 -- "derived_from" --> L1

    style P1 fill:#f59e0b,color:#000
    style S1 fill:#3b82f6,color:#fff
    style S2 fill:#3b82f6,color:#fff
    style E1 fill:#10b981,color:#fff
    style L1 fill:#8b5cf6,color:#fff

Stack Overview

flowchart TB
    subgraph CLI["tapdb CLI (Typer)"]
        BOOT[bootstrap]
        PG[pg]
        DB[db]
        UI_CMD[ui]
        AURORA[aurora]
        COGNITO[cognito]
    end

    subgraph Core["daylily_tapdb (Python library)"]
        MODELS[Models / ORM]
        FACTORY[InstanceFactory]
        TMGR[TemplateManager]
        DISPATCH[ActionDispatcher]
        CONN[TAPDBConnection]
    end

    subgraph Admin["Admin UI (FastAPI + Jinja2)"]
        ROUTES[HTML Routes]
        API[JSON API]
        GRAPH[Cytoscape.js Graph]
    end

    subgraph Data["PostgreSQL"]
        SCHEMA[Schema + Triggers]
        AUDIT[Audit Log]
        OUTBOX[Outbox Events]
    end

    CLI --> Core
    CLI --> Admin
    Core --> Data
    Admin --> Core

Core Tables

Table	Purpose	EUID Prefix
generic_template	Blueprints defining how instances should be created	GT
generic_instance	Concrete objects created from templates	Per-template via instance_prefix
generic_instance_lineage	Directed edges between instances (DAG)	GN
audit_log	Automatic change tracking	Configured via audit_log_euid_prefix
outbox_event	Transactional outbox for durable external delivery	N/A
tapdb_identity_prefix_config	Bootstrap-managed prefix registry for non-template identity domains	N/A
_tapdb_migrations	Applied migration tracking	N/A

TAPDB admin/auth users are actor-backed rows stored in generic_instance with subtype=system_user.

Internal primary keys are BIGINT identity columns named uid, while public identity remains EUID-based.

tenant_id is a native nullable UUID column on generic_template, generic_instance, generic_instance_lineage, audit_log, and outbox_event.

Type Hierarchy

TAPDB uses SQLAlchemy single-table inheritance. The repository bundles only core template packs, while client applications can add domain-specific template subclasses and configs.

Installation

• Library + CLI (default): pip install daylily-tapdb
• Admin UI (optional): pip install "daylily-tapdb[admin]"
• Embedding guide (host app + auth modes): see tapdb_gui_inclusion.md
• Developer tooling (optional): pip install "daylily-tapdb[dev]"
• Aurora (AWS) support: pip install "daylily-tapdb[aurora]" (adds boto3 for Aurora cluster management)
• CLI YAML config support (optional): pip install "daylily-tapdb[cli]" (adds PyYAML for TAPDB CLI config files like tapdb-config*.yaml; template config files remain JSON)

Quick Start (recommended)

python -m venv .venv
source .venv/bin/activate  # or .venv\\Scripts\\activate on Windows
pip install -U pip
pip install -e ".[admin,dev]"

If you want the optional CLI YAML config support during development, install:

• pip install -e ".[admin,dev,cli]"

This workflow:

• creates and activates a virtual environment
• installs this repo in editable mode (with admin + dev extras)

Optional convenience wrapper (macOS/Linux):

source tapdb_activate.sh

Notes

• To enable completion persistently: tapdb --install-completion

Quick Start

1. Initialize the database

# Using the CLI (recommended; strict namespace required):
export TAPDB_CLIENT_ID=tapdb
export TAPDB_DATABASE_NAME=tapdb
export TAPDB_ENV=dev
tapdb config init --client-id "$TAPDB_CLIENT_ID" --database-name "$TAPDB_DATABASE_NAME" --env dev --db-port dev=5533 --ui-port dev=8911
tapdb bootstrap local

# Or apply schema manually to an existing database:
# psql -d your_database -f schema/tapdb_schema.sql

2. Connect and create objects

import os
from daylily_tapdb import TAPDBConnection, TemplateManager, InstanceFactory
from daylily_tapdb.cli.db_config import get_db_config_for_env

# Connect to database using canonical config loader (recommended)
env = os.environ.get("TAPDB_ENV", "dev")
cfg = get_db_config_for_env(env)
db = TAPDBConnection(
    db_hostname=f"{cfg['host']}:{cfg['port']}",
    db_user=cfg["user"],
    db_pass=cfg["password"],
    db_name=cfg["database"],
)

# Initialize managers
templates = TemplateManager()
factory = InstanceFactory(templates)

# Create an instance from a template
with db.session_scope(commit=True) as session:
    plate = factory.create_instance(
        session=session,
        template_code='container/plate/fixed-plate-96/1.0/',
        name='PLATE-001'
    )
    print(f"Created: {plate.euid}")  # e.g., CX1234

Core Concepts

Templates

Templates are blueprints stored in generic_template. They define:

• Type hierarchy: category, type, subtype, version
• Instance prefix: EUID prefix for created instances (e.g., CX for containers)
• JSON schema: Optional validation for instance json_addl
• Default properties: Merged into instance json_addl at creation
• Action imports: Actions available on instances of this template
• Instantiation layouts: Child objects to create automatically

# Template code format: {category}/{type}/{subtype}/{version} (optional trailing `/`)
with db.session_scope(commit=False) as session:
    template = templates.get_template(session, 'container/plate/fixed-plate-96/1.0/')
    # Or by EUID
    template = templates.get_template_by_euid(session, 'GT123')

Template Configuration Schema

TAPDB templates are typically seeded from JSON files under ./config/. The canonical v2 schema metadata is:

• config/_metadata.json

Each JSON file contains a top-level templates array.

Bundled TAPDB core templates are:

• config/generic/generic.json (generic/generic/generic/1.0, generic/generic/external_object_link/1.0)
• config/actor/actor.json (generic/actor/generic/1.0, generic/actor/system_user/1.0)

Non-core domain packs (workflow/action/content/etc.) are expected to live in client repositories or external config directories, not in TAPDB core.

When seeding or validating templates with tapdb db data seed or tapdb db config validate, TAPDB loads template configs in this order:

1. TAPDB core config bundle (daylily_tapdb/core_config or repo config/)
2. Client-specified --config directory (if provided)

Duplicate template keys across merged sources are a hard failure; TAPDB does not silently override by load order.

Canonical fields (v2)

Each element in templates is a template definition with:

• name (string)
• polymorphic_discriminator (string; e.g. generic_template, actor_template)
• category, type, subtype, version (strings) — used to build the template code:
  • {category}/{type}/{subtype}/{version} (optional trailing /)
• instance_prefix (string; EUID prefix for created instances)
• is_singleton (bool)
• bstatus (string; lifecycle status)
• json_addl (object; template-specific data). Common subkeys in core examples:
  • properties (object)
  • expected_inputs / expected_outputs (arrays)
  • instantiation_layouts (array)
  • cogs (object)

Action templates must expose json_addl.action_definition. TapDB no longer falls back to legacy json_addl.action_template payloads during action materialization.

{
  "templates": [
    {
      "name": "Generic Object",
      "polymorphic_discriminator": "generic_template",
      "category": "generic",
      "type": "generic",
      "subtype": "generic",
      "version": "1.0",
      "instance_prefix": "GX",
      "json_addl": {"properties": {"name": "Generic Object"}}
    }
  ]
}

instantiation_layouts schema

If a template’s json_addl.instantiation_layouts is present, TAPDB validates it (Phase 2) and uses it to create child instances automatically.

Canonical shape:

• instantiation_layouts: list of layout objects
  • relationship_type (string, optional; default "contains")
  • name_pattern (string, optional)
  • child_templates: list of child entries
    • string form: a template code (e.g. "content/sample/dna/1.0/")
    • object form: { "template_code": "...", "count": 1, "name_pattern": "..." } (count must be >= 1)

Example:

{
  "json_addl": {
    "instantiation_layouts": [
      {
        "relationship_type": "contains",
        "child_templates": [
          "content/sample/dna/1.0/",
          {"template_code": "content/sample/dna/1.0/", "count": 2, "name_pattern": "Sample-{i}"}
        ]
      }
    ]
  }
}

Validating config files (structural vs semantic)

You can run a structure/format validation pass over template JSON without connecting to a database:

• tapdb db config validate (loads TAPDB core templates only by default)
• tapdb db config validate --config /path/to/client/config (loads core first, then client dir)

Note: commands still require namespace context (--client-id + --database-name, or env vars).

What it checks (structural):

• JSON parses and top-level shape ({"templates": [...]})
• required template keys + basic types
• duplicate (category, type, subtype, version) keys
• template-code formatting in reference fields (accepts optional trailing /)
• json_addl.instantiation_layouts shape via Pydantic (e.g., count >= 1, relationship_type non-empty)

What it does not check (semantic):

• whether relationship_type values are from an allowed vocabulary for your application
• cross-template business rules (capacity constraints, compatibility, naming conventions)
• database state (foreign keys, existing rows) or runtime action wiring correctness

Useful flags:

• --strict/--no-strict: in strict mode, missing referenced templates are errors
• --json: emit a machine-readable report (includes ordered config_dirs)

Instances

Instances are concrete objects created from templates:

with db.session_scope(commit=True) as session:
    # Create with default properties
    instance = factory.create_instance(
        session=session,
        template_code='container/plate/fixed-plate-96/1.0/',
        name='My Plate'
    )

    # Create with custom properties
    instance = factory.create_instance(
        session=session,
        template_code='content/sample/dna/1.0/',
        name='Sample-001',
        properties={
            'concentration': 25.5,
            'volume_ul': 100
        }
    )

Lineages (Relationships)

Lineages connect instances in a directed acyclic graph:

with db.session_scope(commit=True) as session:
    # Link two existing instances (plate/sample assumed already loaded)
    lineage = factory.link_instances(
        session=session,
        parent=plate,
        child=sample,
        relationship_type='contains'
    )

    # Traverse relationships (read-only)
    for lineage in plate.parent_of_lineages:
        child = lineage.child_instance
        print(f"{plate.euid} -> {child.euid}")

Enterprise Unique IDs (EUIDs)

EUIDs are Meridian-format identifiers generated by database triggers. TapDB documents its canonical substrate-managed prefixes through the shared Python helpers, while template instance_prefix values and audit_log_euid_prefix remain database/bootstrap configuration inputs. Callers must still treat all EUIDs as opaque values.

Prefix	Type	Example pattern
GT	Template	GT-<body><check>
GX	Generic instance	GX-<body><check>
GN	Lineage	GN-<body><check>
WX	Workflow instance	WX-<body><check>
WSX	Workflow-step instance	WSX-<body><check>
XX	Action instance	XX-<body><check>

TapDB does not expose application-specific prefix registration as a shared API, and calling code must not derive business behavior from parsing EUID prefixes.

Actions

Actions are operations executed on instances with automatic audit tracking:

from daylily_tapdb import ActionDispatcher

class MyActionHandler(ActionDispatcher):
    def do_action_set_status(self, instance, action_ds, captured_data):
        """Handler for 'set_status' action."""
        new_status = captured_data.get('status')
        instance.bstatus = new_status
        return {'status': 'success', 'message': f'Status set to {new_status}'}

    def do_action_transfer(self, instance, action_ds, captured_data):
        """Handler for 'transfer' action."""
        # Implementation here
        return {'status': 'success', 'message': 'Transfer complete'}

# Execute an action
handler = MyActionHandler()
with db.session_scope(commit=True) as session:
    result = handler.execute_action(
        session=session,
        instance=plate,
        action_group='core_actions',
        action_key='set_status',
        action_ds=action_definition,
        captured_data={'status': 'in_progress'},
        user='john.doe'
    )

Connection Management

TAPDB provides two transaction patterns:

Manual transaction control (recommended for complex operations)

session = db.get_session()
try:
    # Your operations
    session.add(instance)
    session.commit()  # You control when to commit
except Exception:
    session.rollback()
    raise
finally:
    session.close()

Scoped sessions (auto-commit)

with db.session_scope(commit=True) as session:
    session.add(instance)

Database Schema

The schema includes:

• Tables: generic_template, generic_instance, generic_instance_lineage, audit_log, outbox_event
• Sequences: Per-prefix sequences for EUID generation
• Triggers:
  • EUID auto-generation on insert
  • Soft delete (prevents hard deletes, sets is_deleted=TRUE)
  • Audit logging (INSERT, UPDATE, DELETE tracking)
  • Auto-update modified_dt timestamp

Initialize the schema:

# Using the CLI (recommended; strict namespace required):
export TAPDB_CLIENT_ID=tapdb
export TAPDB_DATABASE_NAME=tapdb
export TAPDB_ENV=dev
tapdb bootstrap local

# Or apply schema manually to an existing database:
# psql -d your_database -f schema/tapdb_schema.sql

Configuration

Environment variables

Variable	Description	Default
DATABASE_URL	Full database URL (overrides component/env-style config)	—
TAPDB_ENV	Active environment name used by the CLI (e.g. dev, test, prod)	—
TAPDB_CLIENT_ID	Required for runtime/DB commands. Client/app namespace key	—
TAPDB_DATABASE_NAME	Required for runtime/DB commands. Database namespace key	—
TAPDB_<ENV>_COGNITO_USER_POOL_ID	Optional override for env-bound Cognito pool ID	—
TAPDB_<ENV>_AUDIT_LOG_EUID_PREFIX	Required for schema/bootstrap writes; prefix for audit_log EUIDs	—
TAPDB_<ENV>_SUPPORT_EMAIL	Optional support email shown in GUI footer/help	—
TAPDB_CONFIG_PATH	Explicit path to TAPDB CLI config (tapdb-config*.yaml/.json)	—
TAPDB_TEST_DSN	Postgres DSN enabling integration tests (tests/test_integration.py)	—
TAPDB_SESSION_SECRET	Prod admin UI required: session secret	—
TAPDB_ADMIN_ALLOWED_ORIGINS	Prod admin UI required: comma-separated CORS origins	—
PGPASSWORD	PostgreSQL password	—
PGPORT	PostgreSQL port	5533
USER	Database user / audit username	System user
ECHO_SQL	Log SQL statements (true/1/yes)	false

CLI config file (required for strict namespace mode)

For runtime/DB commands, TAPDB resolves the active config at:

• ~/.config/tapdb/<client-id>/<database-name>/tapdb-config.yaml
• or explicit override: TAPDB_CONFIG_PATH=/path/to/tapdb-config.yaml

Namespace keys are required:

tapdb --client-id atlas --database-name app info
# equivalent:
TAPDB_CLIENT_ID=atlas TAPDB_DATABASE_NAME=app tapdb info

Runtime state is isolated per namespace + env at:

• ~/.config/tapdb/<client-id>/<database-name>/<env>/ui/...
• ~/.config/tapdb/<client-id>/<database-name>/<env>/postgres/...
• ~/.config/tapdb/<client-id>/<database-name>/<env>/locks/...

An example config is included at: ./config/tapdb-config-example.yaml

Example config:

meta:
  config_version: 2
  client_id: atlas
  database_name: app
environments:
  dev:
    engine_type: local
    host: localhost
    port: 5533
    ui_port: 8911
    user: daylily
    password: ""
    database: tapdb_app_dev
    cognito_user_pool_id: us-east-1_xxxxxxxxx
    audit_log_euid_prefix: TAD
    support_email: [e-mail]

audit_log_euid_prefix must be a valid Meridian prefix (^[A-HJ-KMNP-TV-Z]{2,3}$). For engine_type: local, host must be exactly localhost.

Supported file shapes:

• {"dev": {...}, "test": {...}, "prod": {...}}
• or {"environments": {"dev": {...}}}

Admin Auth (Cognito via daycog)

TAPDB Admin login uses daylily-cognito and resolves Cognito runtime settings from daycog-managed env files:

• ~/.config/daycog/<pool-name>.<region>.env (pool-selected app context)
• ~/.config/daycog/<pool-name>.<region>.<app-name>.env (app-specific context)
• ~/.config/daycog/default.env (global default context)

TAPDB config stores only:

• environments.<env>.cognito_user_pool_id

Typical setup:

# Create/reuse pool + app client and bind pool ID into tapdb config
# (AWS profile must be provided via --profile or AWS_PROFILE)
tapdb cognito setup dev \
  --region us-east-1 \
  --profile my-aws-profile \
  --domain-prefix tapdb-dev-users \
  --autoprovision \
  --client-name tapdb

# Create user with permanent password
tapdb cognito add-user dev [e-mail] --password 'SecurePass123' --no-verify

tapdb cognito delegates lifecycle operations to daycog (0.1.24+ patterns), so multiple apps on the same user account can coexist without config collisions. tapdb cognito status also reports daycog metadata including client name and domain/callback/logout URLs when available.

GUI behavior:

• /login authenticates against Cognito.
• /signup creates a Cognito user and automatically provisions an actor-backed system_user.
• First successful Cognito login auto-provisions an actor-backed system_user when missing.

Admin UI (prod hardening)

If you run the admin UI in production mode (TAPDB_ENV=prod), startup will refuse to proceed unless:

• TAPDB_SESSION_SECRET is set
• TAPDB_ADMIN_ALLOWED_ORIGINS is set (comma-separated origins)

Connection parameters

db = TAPDBConnection(
    db_url='postgresql://user:pass@host:5432/dbname',  # Full URL (overrides others)
    # OR component-based:
    db_url_prefix='postgresql://',
    db_hostname='localhost:5432',
    db_user='myuser',
    db_pass='mypass',
    db_name='tapdb',
    # Connection pool settings:
    pool_size=5,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=1800,
    # Debugging:
    echo_sql=False,
    app_username='my_app'  # For audit logging
)

API Reference

TAPDBConnection

Method	Description
get_session()	Get a new session (caller manages transaction)
session_scope(commit=False)	Context manager with optional auto-commit
reflect_tables()	Reflect database tables into AutomapBase
close()	Close session and dispose engine

TemplateManager

Method	Description
get_template(session, template_code)	Get template by code string
get_template_by_euid(session, euid)	Get template by EUID
list_templates(session, category=None, type_=None, include_deleted=False)	List templates with filters
template_code_from_template(template)	Generate code string from template
clear_cache()	Clear template cache

InstanceFactory

Method	Description
create_instance(session, template_code, name, properties=None, create_children=True)	Create instance from template
link_instances(session, parent, child, relationship_type="generic")	Create lineage between instances

ActionDispatcher

Method	Description
execute_action(session, instance, action_group, action_key, ...)	Execute and (optionally) create an audit action record

Implement handlers as do_action_{action_key}(self, instance, action_ds, captured_data) methods.

Integration Testing

TAPDB’s integration tests require a reachable PostgreSQL DSN.

For a quick local Postgres (recommended):

export TAPDB_CLIENT_ID=atlas
export TAPDB_DATABASE_NAME=app
export TAPDB_ENV=test

tapdb pg init test
tapdb pg start-local test
tapdb db create test

export TAPDB_TEST_DSN="postgresql://$USER@localhost:5534/tapdb_test"

Or, point at any reachable Postgres:

export TAPDB_TEST_DSN='postgresql://user@localhost:5432/postgres'

Run the integration tests:

pytest -q tests/test_integration.py

GitHub Actions

CI uses a PostgreSQL service container and sets TAPDB_TEST_DSN automatically. See:

• .github/workflows/ci.yml

Testing

# Run all tests
pytest tests/ -v

# Run Postgres integration tests (always-on when TAPDB_TEST_DSN is set)
TAPDB_TEST_DSN='postgresql://user:pass@localhost:5432/dbname' pytest -q

# With coverage
pytest tests/ --cov=daylily_tapdb --cov-report=term-missing

# Type checking
mypy daylily_tapdb/

# Linting
ruff check daylily_tapdb/

Admin Web Interface

TAPDB includes a FastAPI-based admin interface for browsing and managing objects. For mounting TAPDB inside another FastAPI app (including shared-auth and disable-auth modes), see tapdb_gui_inclusion.md.

Running the Admin App

Set namespace + environment first:

export TAPDB_CLIENT_ID=atlas
export TAPDB_DATABASE_NAME=app
export TAPDB_ENV=dev

# Activate environment
source .venv/bin/activate

# Start the server (background)
tapdb ui start

# Check status
tapdb ui status

# View logs
tapdb ui logs -f

# Stop the server
tapdb ui stop

Runtime files are namespaced under:

• ~/.config/tapdb/<client-id>/<database-name>/<env>/ui/ui.pid
• ~/.config/tapdb/<client-id>/<database-name>/<env>/ui/ui.log
• ~/.config/tapdb/<client-id>/<database-name>/<env>/ui/certs/

Trusted Local HTTPS with mkcert (recommended)

tapdb ui start always serves HTTPS. For browser-trusted local certs, use mkcert:

# One command: install local CA and generate certs TAPDB uses by default
tapdb ui mkcert

# Restart UI to use the generated certs
tapdb ui restart

Manual fallback (if you prefer to run mkcert yourself):

mkcert -install
mkcert -cert-file ~/.config/tapdb/<client>/<database>/<env>/ui/certs/localhost.crt \
  -key-file ~/.config/tapdb/<client>/<database>/<env>/ui/certs/localhost.key \
  localhost

Then open https://localhost:<ui_port> where <ui_port> comes from environments.<env>.ui_port in the namespaced config.

Or run in foreground with auto-reload for development:

tapdb ui start --reload --foreground
# Or directly:
uvicorn admin.main:app --reload --port <ui_port>

Admin DB Metrics

TAPDB UI records lightweight DB latency metrics (TSV, 2-week rolling files) and renders summaries at /admin/metrics (admin-only).

Environment controls:

Variable	Default	Purpose
TAPDB_DB_METRICS	1 (disabled by default under pytest)	Enable/disable metrics capture
TAPDB_DB_METRICS_QUEUE_MAX	20000	Bounded in-memory queue size
TAPDB_DB_METRICS_FLUSH_SECS	1.0	Background flush interval
TAPDB_DB_METRICS_DIR	namespaced runtime dir	Override metrics output root
TAPDB_DB_POOL_SIZE	5	SQLAlchemy pool size for admin UI
TAPDB_DB_MAX_OVERFLOW	10	Pool overflow connections
TAPDB_DB_POOL_TIMEOUT	30	Pool checkout timeout (seconds)
TAPDB_DB_POOL_RECYCLE	1800	Pool recycle interval (seconds)

CLI Quickstart

The fastest way to get a local TAPDB instance running:

# 1. Activate environment
source tapdb_activate.sh

# 2. Set active target and bootstrap in one command
export TAPDB_CLIENT_ID=atlas
export TAPDB_DATABASE_NAME=app
export TAPDB_ENV=dev
tapdb bootstrap local

# 3. Verify everything is working
tapdb db schema status dev
# Open https://localhost:<ui_port> from environments.dev.ui_port

To stop everything:

source tapdb_deactivate.sh  # Stops PostgreSQL and deactivates .venv

Reset / "Nuke" (Local vs Full)

• Local reset (default): removes local repo/user artifacts only (does not delete remote DBs or AWS resources)
  • bash bin/nuke_all.sh
  • bash bin/nuke_all.sh --dry-run
• Full deletion (dangerous): local reset + optional remote DB deletion + optional AWS deletion
  • bash bin/nuke_all.sh --full
  • Full deletion is gated behind double confirmations and requires explicit AWS resource IDs via env vars.

CLI Command Reference

# Runtime/DB commands require namespace context:
# tapdb --client-id <client> --database-name <db> <command>

# General
tapdb --help                 # Show all commands
tapdb version                # Show version
tapdb info                   # Show config and status

# Config namespace management
tapdb config init --client-id <id> --database-name <db> --env dev --db-port dev=5533 --ui-port dev=8911
tapdb config migrate-legacy --client-id <id> --database-name <db>

# End-to-end bootstrap (TAPDB_ENV must be set)
tapdb bootstrap local        # Local runtime + db + schema + seed + UI
tapdb bootstrap local --no-gui
tapdb bootstrap aurora --cluster tapdb-dev --region us-west-2

# Local PostgreSQL (data-dir based; requires initdb/pg_ctl on PATH; conda recommended)
tapdb pg init <env>          # Initialize data directory (dev/test only)
tapdb pg start-local <env>   # Start local PostgreSQL instance
tapdb pg stop-local <env>    # Stop local PostgreSQL instance

# System PostgreSQL (system service; production only)
tapdb pg start               # Start system PostgreSQL service
tapdb pg stop                # Stop system PostgreSQL service
tapdb pg status              # Check if PostgreSQL is running
tapdb pg logs                # View logs (--follow/-f to tail)
tapdb pg restart             # Restart system PostgreSQL

# Database management (env: dev | test | prod)
tapdb db create <env>        # Create empty database (e.g., tapdb_dev)
tapdb db delete <env>        # Delete database (⚠️ destructive)
tapdb db setup <env>         # Full setup: create db + schema + seed (recommended)
tapdb db schema apply <env>  # Apply TAPDB schema to existing database
tapdb db schema status <env> # Check schema status and row counts
tapdb db schema reset <env>  # Drop TAPDB schema objects (⚠️ destructive)
tapdb db schema migrate <env># Apply schema migrations
tapdb db data seed <env>     # Seed core templates first, then optional client --config
tapdb db data seed <env> -w  # Include optional non-core packs if present
tapdb db data backup <env>   # Backup database (--output/-o FILE)
tapdb db data restore <env>  # Restore from backup (--input/-i FILE)
tapdb db config validate     # Validate core template config set
tapdb db config validate --config /path/to/client/config  # Validate core + client config set

# User management
tapdb user list              # List users
tapdb user add               # Add a user
tapdb user set-role          # Promote/demote (admin|user)
tapdb user deactivate        # Disable login
tapdb user activate          # Re-enable login
tapdb user set-password      # Set password
tapdb user delete            # Permanently delete (⚠️ destructive)
# Note: Admin UI authentication is Cognito-based; tapdb users are app roles/metadata.

# Cognito integration (delegates to daycog; stores only pool-id in tapdb config)
tapdb cognito setup <env>    # daycog 0.1.24+ setup wrapper (pool/client/callback policy)
tapdb cognito setup-with-google <env> [google flags]
tapdb cognito bind <env>     # Bind existing pool ID to env
tapdb cognito status <env>   # Show pool binding and mapped daycog env file
tapdb cognito list-pools <env>
tapdb cognito add-user <env> <email> --password <pw> [--role user|admin] [--no-verify]
tapdb cognito list-apps <env>
tapdb cognito add-app <env> --app-name <name> --callback-url <url> [--set-default]
tapdb cognito edit-app <env> (--app-name <name>|--client-id <id>) [--new-app-name <name>]
tapdb cognito remove-app <env> (--app-name <name>|--client-id <id>) [--force]
tapdb cognito add-google-idp <env> (--app-name <name>|--client-id <id>) [google creds flags]
tapdb cognito fix-auth-flows <env>
tapdb cognito config print <env>
tapdb cognito config create <env>
tapdb cognito config update <env>

# Admin UI
tapdb ui start               # Start admin UI (background)
tapdb ui mkcert             # Install mkcert CA + generate trusted localhost cert/key
tapdb ui stop                # Stop admin UI
tapdb ui status              # Check if running
tapdb ui logs                # View logs (--follow/-f to tail)
tapdb ui restart             # Restart server

# Aurora PostgreSQL (AWS) — requires pip install daylily-tapdb[aurora]
tapdb aurora create <env>    # Provision Aurora cluster via CloudFormation
tapdb aurora status <env>    # Show cluster status, endpoint, outputs
tapdb aurora connect <env>   # Print connection info / psql command
tapdb aurora list            # List all tapdb Aurora stacks in a region
tapdb aurora delete <env>    # Tear down Aurora CloudFormation stack

CLI Migration (Duplicate Commands Removed)

Old command	New canonical command
tapdb pg create <env>	tapdb db create <env>
tapdb pg delete <env>	tapdb db delete <env>
tapdb db create <env> (schema apply)	tapdb db schema apply <env>
tapdb db status <env>	tapdb db schema status <env>
tapdb db nuke <env>	tapdb db schema reset <env>
tapdb db migrate <env>	tapdb db schema migrate <env>
tapdb db seed <env>	tapdb db data seed <env>
tapdb db backup <env>	tapdb db data backup <env>
tapdb db restore <env>	tapdb db data restore <env>
tapdb db validate-config	tapdb db config validate

Backup & Recovery

TAPDB includes thin wrappers around PostgreSQL client tools:

• tapdb db data backup <env> uses pg_dump and writes an SQL file
• tapdb db data restore <env> replays that file using psql (ON_ERROR_STOP=1)

What is included by default (table-limited logical backup):

• generic_template
• generic_instance
• generic_instance_lineage
• audit_log

Notes / operational guidance:

• These commands require pg_dump / psql on your PATH (PostgreSQL client install).
• For most workflows, prefer data-only backups and restore into an existing TAPDB schema:
  • Backup: tapdb db data backup <env> --data-only -o tapdb_<env>.sql
  • Ensure schema exists: tapdb db schema apply <env> (or tapdb db setup <env>)
  • Restore: tapdb db data restore <env> -i tapdb_<env>.sql --force
• restore does not automatically drop tables; for a clean restore, run tapdb db schema reset <env> (or recreate the database) before restoring.

Environments

TAPDB supports three environments: dev, test, and prod. In strict namespace mode, each environment must define explicit DB and UI ports in config.

Environment	Example DB Port	Example UI Port	Runtime Root
dev	5533	8911	~/.config/tapdb/<client>/<database>/dev/
test	5534	8912	~/.config/tapdb/<client>/<database>/test/
prod	explicit in config	explicit in config	~/.config/tapdb/<client>/<database>/prod/

Local vs Remote:

• dev and test can use local PostgreSQL (tapdb pg init/start-local)
• prod can use remote PostgreSQL/Aurora (recommended) or local by explicit config
• For engine_type: local, host must be exactly localhost

For local/manual PostgreSQL, bring your own Postgres instance, database, credentials, and network access. For automated AWS provisioning, see Aurora PostgreSQL (AWS).

For remote/AWS databases, configure via environment variables:

export TAPDB_PROD_HOST=my-rds.us-west-2.rds.amazonaws.com
export TAPDB_PROD_PORT=5432
export TAPDB_PROD_USER=tapdb_admin
export TAPDB_PROD_PASSWORD=your-password
export TAPDB_PROD_DATABASE=tapdb_prod

tapdb db setup prod

Environment Configuration

Database connections are configured via environment variables:

Variable	Description	Default
TAPDB_DEV_HOST	Dev database host	from config (localhost for local engine)
TAPDB_DEV_PORT	Dev database port	from config (required in strict mode)
TAPDB_DEV_UI_PORT	Dev UI HTTPS port	from config (required in strict mode for local engine)
TAPDB_DEV_USER	Dev database user	$USER
TAPDB_DEV_PASSWORD	Dev database password	—
TAPDB_DEV_DATABASE	Dev database name	tapdb_dev

Replace DEV with TEST or PROD for other environments. Environment variables override config values; TAPDB does not auto-select alternative ports on conflicts.

Admin Features

• Dashboard — Overview of templates, instances, and lineages
• Home Query + Audit — Simple query plus object/user audit trails on /
• Complex Query — Multi-field object search on /query
• Browse — List views with filtering and pagination
• Object Details — View any object by EUID with relationships
• Graph Visualization — Interactive Cytoscape.js DAG explorer
  • Dagre hierarchical layout
  • Graph gestures (delete/link/wave)
  • Node/edge detail panel with rendered properties + raw JSON
• Info Page — DB/Cognito runtime details on /info (plus admin-only catalog inventory)
• Admin Metrics — DB latency metrics from rolling TSV files on /admin/metrics (admin only)

GUI Routes (HTML)

All HTML routes use session auth unless otherwise noted.

Route	Auth	Description
/login, /signup, /logout	Public/session transition	Local login/signup pages and logout
/auth/login, /auth/callback	Public/session transition	Cognito Hosted UI/OAuth redirect flow
/change-password	Auth	Password change flow
/	Auth	Home dashboard + simple query + object/user audit panels
/query	Auth	Advanced multi-field query page
/templates	Auth	Template list and filters
/instances	Auth	Instance list and filters
/lineages	Auth	Lineage list
/object/{euid}	Auth	Object detail page
/create-instance/{template_euid} (GET/POST)	Auth	Instance creation workflow from template
/graph	Auth	Interactive DAG graph view
/help	Public	GUI help/support page
/info	Auth	Runtime configuration and Cognito diagnostics
/admin/metrics	Admin	DB performance metrics summary from TSV

JSON API Endpoints

Current JSON routes in admin/main.py:

Endpoint	Method	Auth	Description
/api/graph/data	GET	Public	Cytoscape-compatible graph payload (elements.nodes, elements.edges)
/api/templates	GET	Public	Template list (page, page_size, optional category)
/api/instances	GET	Public	Instance list (page, page_size, optional category)
/api/object/{euid}	GET	Public	Object lookup by EUID across template/instance/lineage
/api/lineage	POST	Admin	Create lineage edge from parent_euid + child_euid
/api/object/{euid}	DELETE	Admin	Soft-delete object by EUID

/api/lineage returns 409 for duplicate edges and validates both endpoints exist.

Aurora PostgreSQL (AWS)

TAPDB can provision and manage Aurora PostgreSQL clusters on AWS via CloudFormation. This is the recommended path for staging and production deployments.

Prerequisites

• AWS CLI configured with credentials (aws configure or AWS_PROFILE)
• IAM permissions: CloudFormation, RDS, EC2 (VPC/SG/subnets), Secrets Manager, IAM (for IAM auth)
• Install Aurora extras: pip install "daylily-tapdb[aurora]"

Aurora Quick Start

# 1. Set active TAPDB target (strict namespace required)
export TAPDB_CLIENT_ID=tapdb
export TAPDB_DATABASE_NAME=tapdb
export TAPDB_ENV=dev

# 2. Bootstrap Aurora end-to-end (provision + schema + seed + UI)
tapdb bootstrap aurora --cluster tapdb-dev --region us-east-1

# 3. Verify schema and row counts
tapdb db schema status dev

The create command provisions a full CloudFormation stack, waits for completion, and writes connection details to the active namespaced TAPDB config path (~/.config/tapdb/<client-id>/<database-name>/tapdb-config.yaml).

Aurora CLI Command Reference

tapdb aurora create <env>

Provision a new Aurora PostgreSQL cluster via CloudFormation.

Flag	Default	Description
--region, -r	us-west-2	AWS region
--instance-class	db.r6g.large	RDS instance class
--engine-version	16.6	Aurora PostgreSQL engine version
--vpc-id	(auto-discover)	VPC to deploy into (uses default VPC if omitted)
--cidr	10.0.0.0/8	Ingress CIDR for the security group
--cost-center	global	Value for lsmc-cost-center tag
--project	tapdb-<region>	Value for lsmc-project tag
--publicly-accessible / --no-publicly-accessible	False	Whether the instance has a public endpoint
--no-iam-auth	False	Disable IAM database authentication
--no-deletion-protection	False	Disable deletion protection (enabled by default)
--background	False	Initiate creation and exit immediately

tapdb aurora status <env>

Show stack status, endpoint, and CloudFormation outputs.

Flag	Description
--region, -r	AWS region (default: us-west-2)
--json	Output as JSON

tapdb aurora connect <env>

Print connection info for an Aurora environment.

Flag	Description
--region, -r	AWS region (default: us-west-2)
--user, -u	Database user (default: tapdb_admin)
--database, -d	Database name (default: tapdb_<env>)
--export, -e	Print shell export statements (PGHOST, PGPORT, etc.)

tapdb aurora list

List all tapdb Aurora stacks in a region.

Flag	Description
--region, -r	AWS region to scan (default: us-west-2)
--json	Output as JSON

tapdb aurora delete <env>

Delete an Aurora CloudFormation stack.

Flag	Description
--region, -r	AWS region (default: us-west-2)
--retain-networking / --no-retain-networking	Retain VPC security group and subnet group (default: retain)
--force, -f	Skip confirmation prompt

--force bypasses all confirmation prompts — use with caution in scripts.

Architecture

The tapdb aurora create command deploys a CloudFormation stack containing:

Resource	Type	Purpose
DBSubnetGroup	AWS::RDS::DBSubnetGroup	Subnet group for cluster placement
ClusterSecurityGroup	AWS::EC2::SecurityGroup	Ingress rule allowing port 5432 from configured CIDR
MasterSecret	AWS::SecretsManager::Secret	Auto-generated 32-char master password
AuroraCluster	AWS::RDS::DBCluster	Aurora PostgreSQL Serverless-compatible cluster
AuroraInstance	AWS::RDS::DBInstance	Writer instance in the cluster

Module structure:

daylily_tapdb/aurora/
├── __init__.py           # Public API exports
├── cfn_template.py       # CloudFormation template generator
├── config.py             # AuroraConfig dataclass
├── connection.py         # AuroraConnectionBuilder (IAM + Secrets Manager auth)
├── schema_deployer.py    # Deploy TAPDB schema to Aurora
├── stack_manager.py      # CloudFormation stack CRUD operations
└── templates/
    └── aurora-postgres.json  # Base CFN template

Security Model

Authentication:

• IAM auth (default): Short-lived tokens via rds.generate_db_auth_token(). No long-lived passwords in config files.
• Secrets Manager: Master password auto-generated and stored in Secrets Manager. Retrieved at connection time.

SSL/TLS:

• All connections use sslmode=verify-full with the AWS RDS global CA bundle
• CA bundle auto-downloaded and cached at ~/.config/tapdb/rds-ca-bundle.pem

Network security:

• Security group restricts port 5432 ingress to the configured --cidr
• Default CIDR is 0.0.0.0/0 — restrict to your IP for dev: --cidr $(curl -s ifconfig.me)/32
• Use --no-publicly-accessible for VPC-only access

Credential storage:

• Connection config written to the active namespaced TAPDB config file (~/.config/tapdb/<client-id>/<database-name>/tapdb-config.yaml)
• Secrets Manager ARN stored in CloudFormation outputs (no plaintext passwords on disk)

Cost Considerations

Aurora pricing varies by region. Representative us-east-1 costs (as of 2026):

Instance Class	vCPU	RAM	Hourly	Daily	Monthly (730h)
db.t4g.medium	2	4 GB	~$0.073	~$1.75	~$53
db.t4g.large	2	8 GB	~$0.146	~$3.50	~$107
db.r6g.large	2	16 GB	~$0.26	~$6.24	~$190
db.r6g.xlarge	4	32 GB	~$0.52	~$12.48	~$380

Additional costs:

• Storage: $0.10/GB-month (Aurora automatically scales)
• I/O: $0.20 per million requests (Aurora Standard) or included (Aurora I/O-Optimized)
• Secrets Manager: $0.40/secret/month + $0.05 per 10K API calls

Tip: Use db.t4g.medium for dev/test. Use --retain-networking on delete to speed up re-creation.

Connectivity Options

Method	Best For	Setup
IP-restricted public access	Dev/test, solo developers	--publicly-accessible --cidr $(curl -s ifconfig.me)/32
VPN / Tailscale subnet router	Teams, multi-user	Route VPC subnet through Tailscale exit node
VPC peering	Production, cross-account	Peer application VPC with database VPC
Private (no public endpoint)	Production, compliance	--no-publicly-accessible + VPC-internal access only

For development, IP-restricted public access is the simplest approach. The security group limits port 5432 to your IP.

Teardown

# Delete the Aurora stack (retains networking resources by default)
tapdb aurora delete dev --region us-east-1

# Delete everything including VPC security group and subnet group
tapdb aurora delete dev --region us-east-1 --no-retain-networking

# Skip confirmation prompt
tapdb aurora delete dev --region us-east-1 --force

Security Considerations

Credential Passing (PGPASSWORD)

TAPDB passes database credentials to psql via the PGPASSWORD environment variable — the standard PostgreSQL approach. On Linux, environment variables may be visible to other processes on the same system via /proc/<pid>/environ.

Mitigations:

• IAM auth tokens (the default for Aurora) are short-lived (~15 minutes)
• For password-based auth, consider using a .pgpass file as an alternative
• The TAPDB config file is written with 0600 permissions

Network Security

The Aurora security group created by CloudFormation:

• Ingress: TCP port 5432 from the configured CIDR (default: 10.0.0.0/8, VPC-only)
• Egress: All outbound allowed (required for Aurora — S3 backups, CloudWatch metrics, RDS API)
• Public access: Disabled by default (--publicly-accessible flag to override)

For compliance environments requiring explicit egress rules, modify the security group manually after provisioning.

Deletion Protection

Deletion protection is enabled by default. Use --no-deletion-protection for dev/test clusters. The tapdb aurora delete command automatically disables deletion protection before stack deletion.

Caution: The --force flag bypasses all confirmation prompts. Use with care in automated scripts.

Logging

Debug-level logging may include infrastructure metadata (hostname, port, username). Ensure production deployments use logging level INFO or above:

export TAPDB_LOG_LEVEL=INFO

No credentials or IAM tokens are ever logged at any level.

Releasing

Use the release build helper to ensure artifacts are built from the most recent semantic version tag (and not an untagged local commit).

make release-build

What this does:

• resolves the latest semantic version tag (format like 0.1.17)
• creates a temporary detached git worktree at that tag
• builds in that worktree
• copies artifacts back to project dist/

This works even if your current checkout is dirty or ahead of the tag.

Direct script usage:

./bin/release_build.sh

Project Structure

daylily-tapdb/
├── daylily_tapdb/
│   ├── __init__.py          # Public API exports
│   ├── _version.py          # Version info
│   ├── connection.py        # TAPDBConnection
│   ├── euid.py              # Meridian validation + read-only canonical prefix catalog
│   ├── models/
│   │   ├── base.py          # tapdb_core abstract base
│   │   ├── template.py      # generic_template + typed subclasses
│   │   ├── instance.py      # generic_instance + typed subclasses
│   │   └── lineage.py       # generic_instance_lineage + typed subclasses
│   ├── templates/
│   │   └── manager.py       # TemplateManager
│   ├── factory/
│   │   └── instance.py      # InstanceFactory
│   ├── actions/
│   │   └── dispatcher.py    # ActionDispatcher
│   ├── validation/
│   │   └── instantiation_layouts.py  # Pydantic schema for json_addl.instantiation_layouts
│   ├── aurora/              # Aurora PostgreSQL (AWS) support
│   │   ├── cfn_template.py  # CloudFormation template generator
│   │   ├── config.py        # AuroraConfig dataclass
│   │   ├── connection.py    # IAM + Secrets Manager auth
│   │   ├── schema_deployer.py # Schema deployment to Aurora
│   │   └── stack_manager.py # CloudFormation stack operations
│   └── cli/
│       └── __init__.py      # CLI entry point
├── admin/                   # FastAPI admin interface
│   ├── main.py              # App entry point
│   ├── api/                 # API endpoints
│   ├── templates/           # Jinja2 HTML templates
│   └── static/              # CSS and JavaScript
├── schema/
│   └── tapdb_schema.sql     # PostgreSQL schema
├── tests/
│   ├── conftest.py
│   ├── test_euid.py
│   └── test_models.py
├── tapdb_activate.sh        # Dev helper: create/activate .venv
├── tapdb_deactivate.sh      # Dev helper: deactivate .venv
├── pyproject.toml
└── README.md

Requirements

• Python: 3.10+
• PostgreSQL: 13+
• SQLAlchemy: 2.0+
• psycopg2-binary: 2.9+

License

MIT License — see LICENSE for details.

Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/my-feature)
3. Make your changes with tests
4. Run the test suite (pytest tests/ -v)
5. Submit a pull request

---

Daylily Informatics — daylilyinformatics.com