Task 003: Postgres Schema And Seed Data

Development description: Create the initial relational schema, migrations, indexes, and seed data for articles, sites, workflow state, jobs, research manifests, publishing versions, and audit events.

Implementation Details

Add migration tooling for the FastAPI backend, such as Alembic.
Create tables:
- users
- target_sites
- script_config_versions
- articles
- boundary_questions
- article_plans
- plan_sections
- evidence_items
- claims
- article_drafts
- assets
- workflow_events
- agent_jobs
- research_run_manifests
- publish_commits
- prompt_versions
Store article/domain status in domain tables as authoritative state.
Store LangGraph checkpoints separately from domain tables if checkpointing is implemented in this task.
Seed:
- One Admin user.
- One Editor user.
- One Git-backed Next target site.
- One active publishing YAML/script config version.
Add indexes for article dashboard queries, job queues, workflow timeline, and site lookup by slug.

Public Interface

alembic upgrade head creates the schema.
Backend repository methods can create/read seeded target sites and users.

Acceptance Criteria

TDD pre-requirement: before implementing migrations, write one failing integration test through repository/public backend interfaces for seeded site lookup; add further tests one behavior at a time and record red-green evidence in Result.
Migrations run cleanly on an empty Postgres database.
Seed data is idempotent.
Article/domain tables hold authoritative article statuses.
Script/config versions store author, timestamp, diff, rollback target, activation timestamp, YAML, and transform script.
Research manifests store S3 prefixes, source URLs, object keys, content hashes, artifact types, and metadata references.
Publish commits store repository URL, branch, commit SHA, content bundle manifest, and status.

Verification

Run migration tests against a disposable Postgres database.
Run seed command twice and verify no duplicates.
Run repository integration tests.

Result

Status: Completed.
TDD plan:
- Add one integration-style unittest slice before schema/seed implementation.
- Exercise the future public backend repository setup and application seed use case through open_backend_repository(dsn) and seed_reference_data(repository).
- Use PIPELINE_TEST_DATABASE_DSN when provided so the same test can later run against Postgres; otherwise use a temporary sqlite:///... DSN only as a disposable Red-phase repository substitute.
- Verify observable seeded-site behavior:
  - running repository setup plus seed makes the Git-backed Next target site lookupable by slug b2b_saas_blog;
  - running seed twice leaves exactly one site with that slug;
  - the returned site exposes the public TargetSiteConfig shape with Git/Next publishing fields and an active script config version.

Red evidence:

Command: env PYTHONDONTWRITEBYTECODE=1 python3 -m unittest discover -s apps/backend/tests/integration -p test_seeded_target_site_lookup.py

Actual output:

E
======================================================================
ERROR: test_seeded_git_next_target_site_lookup_by_slug_is_idempotent (test_seeded_target_site_lookup.SeededTargetSiteLookupIntegrationTest.test_seeded_git_next_target_site_lookup_by_slug_is_idempotent)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/gavrilovdev/tmp/pupline/apps/backend/tests/integration/test_seeded_target_site_lookup.py", line 32, in test_seeded_git_next_target_site_lookup_by_slug_is_idempotent
    from src.application.seed_data import seed_reference_data
ModuleNotFoundError: No module named 'src.application.seed_data'

----------------------------------------------------------------------
Ran 1 test in 0.000s

FAILED (errors=1)

Result: failed as expected because seed data and repository interfaces are not implemented yet.

Green evidence:
- Pre-requirement command: docker compose run --rm --no-deps -v /Users/gavrilovdev/tmp/pupline:/workspace -w /workspace backend python -m unittest discover -s apps/backend/tests/integration -p test_seeded_target_site_lookup.py
- Actual output:
```
.
----------------------------------------------------------------------
Ran 1 test in 0.216s

OK
```
- Added focused storage tests for the follow-up behaviors:
  - task table/index shape;
  - authoritative article status columns;
  - script config audit/rollback/activation/YAML/script fields;
  - research manifest S3/source/object/hash/type/metadata fields;
  - publish commit repository/branch/SHA/manifest/status fields;
  - seed idempotency for users, site, and active script config version.
Refactor notes:
- Added domain schema/reference constants only under src/domain/schema.py.
- Added seed_reference_data(repository) in application and kept DB connections/repository/DDL in infrastructure.
- Added Alembic under apps/backend/alembic with root alembic.ini; DATABASE_URL and POSTGRES_DSN are supported.
- Fixed Alembic env import path so the same migration runs from repo root and inside the backend container.
- Added make migrate and make seed; public root command remains alembic upgrade head.

Verification output:

Backend tests:

Command:
docker compose run --rm --no-deps -v /Users/gavrilovdev/tmp/pupline:/workspace -w /workspace backend python -m unittest discover -s apps/backend/tests -p 'test*.py'

Output:
......s....
----------------------------------------------------------------------
Ran 11 tests in 0.123s

OK (skipped=1)

Empty Postgres migration:

Command:
env POSTGRES_PORT=55433 docker compose -p pupline-task003 run --rm --no-deps -e DATABASE_URL=postgresql://pipeline:pipeline_local@postgres:5432/pipeline backend alembic upgrade head

Output:
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 202605210003, initial postgres schema

Postgres Alembic integration test:

Command:
env POSTGRES_PORT=55433 docker compose -p pupline-task003 run --rm --no-deps -v /Users/gavrilovdev/tmp/pupline:/workspace -w /workspace -e PIPELINE_TEST_DATABASE_DSN=postgresql://pipeline:pipeline_local@postgres:5432/pipeline backend python -m unittest discover -s apps/backend/tests/integration -p test_postgres_alembic_migration.py

Output:
.
----------------------------------------------------------------------
Ran 1 test in 0.693s

OK

Postgres repository/seed tests:

Command:
env POSTGRES_PORT=55433 docker compose -p pupline-task003 run --rm --no-deps -v /Users/gavrilovdev/tmp/pupline:/workspace -w /workspace -e PIPELINE_TEST_DATABASE_DSN=postgresql://pipeline:pipeline_local@postgres:5432/pipeline backend python -m unittest discover -s apps/backend/tests/integration -p test_seeded_target_site_lookup.py

Output:
.
----------------------------------------------------------------------
Ran 1 test in 0.269s

OK

Command:
env POSTGRES_PORT=55433 docker compose -p pupline-task003 run --rm --no-deps -v /Users/gavrilovdev/tmp/pupline:/workspace -w /workspace -e PIPELINE_TEST_DATABASE_DSN=postgresql://pipeline:pipeline_local@postgres:5432/pipeline backend python -m unittest discover -s apps/backend/tests/integration -p test_schema_storage_contracts.py

Output:
...
----------------------------------------------------------------------
Ran 3 tests in 0.348s

OK

Seed command twice/no duplicates:

Commands:
env POSTGRES_PORT=55433 docker compose -p pupline-task003 run --rm --no-deps -e POSTGRES_DSN=postgresql://pipeline:pipeline_local@postgres:5432/pipeline backend python -m src.infrastructure.seed_data
env POSTGRES_PORT=55433 docker compose -p pupline-task003 run --rm --no-deps -e POSTGRES_DSN=postgresql://pipeline:pipeline_local@postgres:5432/pipeline backend python -m src.infrastructure.seed_data

Verification output:
admin=1 editor=1 sites=1 scripts=1 active=ACTIVE

Smoke:

Command:
bash tests/smoke/public-health.sh

Output:
backend health ok
runner health ok
frontend health ok
backend dependencies ok
public health smoke ok

8.5 KiB Raw Permalink Blame History