Define execution-state storage schema

[alexeygrigorev]

Goal

Define the production execution-state schema for DataOps V1 so runtime work can move from the current undeployed work-engine prototype into SAM-managed DynamoDB tables without locking us into DynamoDB forever.

Process knowledge, SOPs, task templates, and other durable operating documents stay Git-backed. This issue is only for mutable execution state: what tasks exist, who owns them, what reminders are due, what workflow bundle they belong to, and what runtime metadata is needed to operate the portal.

Decision Inputs

• .goal-v1.md
• docs/v1-runtime-architecture.md
• docs/operations-manager-platform-jtbd.md
• docs/repository-structure-recommendation.md
• Existing work-engine model and seed templates
• Data safety/export issue Add portable execution-data export and DynamoDB backup safety #48

Scope

• Define the logical entity model for V1 execution state.
• Decide which data is DynamoDB runtime state versus Git-backed process knowledge.
• Define table names, keys, GSIs, retention, and ownership in SAM/CloudFormation.
• Refactor or specify work-engine table access so production table names come from environment variables.
• Define relationship fields in a portable way so records can later migrate to Postgres.
• Document backup requirements for every durable table.
• Ensure the schema feeds the portable export registry in Add portable execution-data export and DynamoDB backup safety #48.

Entities In Scope

• Users or operator identities.
• Tasks and task status transitions.
• Workflow bundles/projects.
• Task templates and instantiated template metadata.
• Recurring task configurations.
• File metadata, not file binaries.
• Notifications and reminders.
• Sessions only where the portal needs server-side session state.
• Later-compatible slots for artifacts, assistant jobs, and audit events.

Acceptance Criteria

• A schema document or ADR describes each execution entity, required fields, optional fields, ownership, retention expectation, and whether it is user-editable.
• Every entity has a stable application-level ID that is not only a DynamoDB key expression.
• Relationships are represented with explicit IDs suitable for a future relational migration, for example task_id, bundle_id, template_id, assignee_id, and parent_task_id.
• DynamoDB table resources or a precise implementation spec define partition keys, sort keys where used, GSIs, billing mode, tags, PITR, and deletion/retain policy.
• The schema includes access patterns needed by V1: today’s tasks, overdue tasks, tasks by owner/status, tasks by bundle/workflow, recurring schedules due soon, notifications due, and task-linked files/artifacts.
• Production table lifecycle is owned by SAM/CloudFormation; production handlers do not create tables on cold start.
• work-engine table names are configured through environment variables with local-development defaults only.
• The schema explicitly distinguishes Git-backed templates/process docs from DynamoDB runtime template instances.
• The portable export work in Add portable execution-data export and DynamoDB backup safety #48 can enumerate every durable entity from this schema.
• Tests or validation scripts cover table-name configuration, schema serialization assumptions, and at least one representative query path per core entity.

Out Of Scope

• Building the full task UI.
• Building the export/import implementation from Add portable execution-data export and DynamoDB backup safety #48.
• Migrating to Postgres now.
• Storing file binaries in DynamoDB.

Dependencies

• Blocks Add portable execution-data export and DynamoDB backup safety #48 for a complete export entity list.
• Blocks private work-engine deployment behind the portal broker.
• Should be completed before adding assistant jobs, artifacts, or audit logs to production runtime state.

Test Expectations

• Unit tests for schema helpers or repository methods.
• Local DynamoDB/dynalite or mocked integration tests for key query paths.
• Infrastructure validation through SAM/CloudFormation template checks.
• A small fixture that can later become the seed for Add portable execution-data export and DynamoDB backup safety #48 export validation.

Notes

Current deployed V1 does not use DynamoDB yet. lambda-functions/template.full.yaml defines the public full app Lambda and secrets resources, but no execution tables. This issue is where that changes in a controlled way.

[alexeygrigorev]

Added the production-facing schema reference in commit 718f0b6: docs/v1-execution-state-schema.md. This settles the V1 direction: DynamoDB for mutable execution state, Git for process knowledge, S3/external systems for binaries, stable application IDs for portability, and env-configured production table names. Keeping this issue open for the SAM table resources, work-engine env refactor, and tests.

[alexeygrigorev]

Implemented part of the production execution-state schema work in commit 2cd3a1f.\n\nCompleted now:\n- table names read the documented environment variables\n- local defaults remain compatible with existing dynalite/dev tables\n- Lambda handler no longer auto-creates tables by default in production\n- auto-create is limited to test/local mode or explicit \n- setup-env tests cover default names, production env names, and auto-create gating\n\nStill open for this issue: SAM/CloudFormation DynamoDB table resources with PITR/retain policy/tags, least-privilege IAM for the private work-engine Lambda, and production query/index validation once #49 wires the Lambda into the stack.

[alexeygrigorev]

Correction to the previous comment, with code formatting preserved.

Implemented part of the production execution-state schema work in commit 2cd3a1f.

Completed now:

• work-engine table names read the documented DATAOPS_*_TABLE environment variables.
• Local defaults remain compatible with existing dynalite/dev tables.
• Lambda handler no longer auto-creates tables by default in production.
• Auto-create is limited to test/local mode or explicit DATAOPS_AUTO_CREATE_TABLES=true.
• Setup-env tests cover default names, production env names, and auto-create gating.

Still open for this issue: SAM/CloudFormation DynamoDB table resources with PITR/retain policy/tags, least-privilege IAM for the private work-engine Lambda, and production query/index validation once #49 wires the Lambda into the stack.

[alexeygrigorev]

Implemented the SAM table-resource part in commit 12d1c6d.

Completed now:

• lambda-functions/template.full.yaml declares stack-owned DynamoDB tables for tasks/recurring state, bundles, templates, users, files, notifications, and sessions.
• Durable execution tables use PAY_PER_REQUEST, server-side encryption, point-in-time recovery, DeletionPolicy: Retain, and UpdateReplacePolicy: Retain.
• Tasks table includes GSI-Date, GSI-Bundle, and GSI-Status for current work-engine access patterns.
• Files table includes GSI-Task for task-linked file metadata.
• Template outputs expose table names for future DATAOPS_*_TABLE env wiring on WorkEngineFunction.
• Added tests/docs_app/test_infra_template.py to guard table safety properties and indexes.

Still open: least-privilege IAM and env injection on the private work-engine Lambda when #49 adds the Lambda/broker to the stack, plus production smoke validation after deployment.

[alexeygrigorev]

Deployment follow-up is complete.

Evidence:

• DataOps app deploy run succeeded after rerun: https://github.com/DataTalksClub/dataops/actions/runs/28291969677
• Follow-up deploy run for the role-source commit also succeeded: https://github.com/DataTalksClub/dataops/actions/runs/28292133543
• dataops-v1 stack is UPDATE_COMPLETE.
• DynamoDB tables are active: dataops-v1-tasks, dataops-v1-bundles, dataops-v1-templates, dataops-v1-users, dataops-v1-files, dataops-v1-notifications, dataops-v1-sessions.
• PITR is enabled on the six durable execution tables: tasks, bundles, templates, users, files, notifications.
• The live GitHub OIDC deploy role policy now includes scoped DynamoDB permissions for arn:aws:dynamodb:eu-west-1:817685572750:table/dataops-v1-*.
• Source commits:
  • 12d1c6d in dataops: adds the SAM DynamoDB tables.
  • b76e5f6 in dataops: records deploy-role DynamoDB permissions in this repo.
  • 482dce5 in aws-infra: records the same deploy-role source update in infra.

Still open for #28: least-privilege runtime IAM and env injection on WorkEngineFunction when #49 adds the private work-engine Lambda.

[alexeygrigorev]

READY FOR TEST

Implemented the remaining #28 runtime wiring hardening after verifying the current state against the acceptance criteria.

Changed files:

• lambda-functions/template.full.yaml
• tests/docs_app/test_infra_template.py
• docs/v1-execution-state-schema.md
• docs/v1-runtime-architecture.md

What changed:

• Removed unused runtime DynamoDB permissions from WorkEngineFunction: dynamodb:BatchGetItem, dynamodb:BatchWriteItem, and dynamodb:DescribeTable.
• Kept runtime IAM scoped to the stack-owned DataOps tables and required task/files index ARNs.
• Strengthened the infra regression test to verify every DATAOPS_*_TABLE env var is injected into WorkEngineFunction and to guard against the unused DynamoDB actions returning.
• Updated the schema/runtime docs so they reflect the current state: private WorkEngineFunction exists, receives stack-owned table names through env vars, and is brokered through the public portal.

Acceptance audit:

• Schema/ADR describes entities, fields, ownership, retention, editability: satisfied by docs/v1-execution-state-schema.md, kept current in this patch.
• Stable application IDs: satisfied by schema doc and current work-engine entity records.
• Explicit relationship IDs for migration: satisfied by schema doc and current work-engine fields such as bundleId, templateId, assigneeId, taskId.
• Table resources/spec define keys, GSIs, billing, tags, PITR, retain: satisfied by lambda-functions/template.full.yaml and guarded by tests/docs_app/test_infra_template.py.
• V1 access patterns: satisfied by the schema query requirements plus current work-engine query paths for date/range/status/bundle/files/recurring/notifications. Assignee filtering is represented as an explicit relationship field and currently handled at the app layer rather than a dedicated GSI.
• Production lifecycle owned by SAM/CloudFormation; production handlers do not create tables on cold start: satisfied by SAM tables and shouldAutoCreateTables() gating.
• work-engine table names configured through env vars with local defaults: satisfied by work-engine/src/db/setup.ts and work-engine/tests/setup-env.test.ts.
• Git-backed process docs/templates are distinct from DynamoDB runtime template projections: satisfied by schema/runtime docs.
• Add portable execution-data export and DynamoDB backup safety #48 export can enumerate durable entities: satisfied by schema entity list and existing portable export coverage.
• Tests cover table-name config, serialization/export assumptions, and representative query paths: covered by setup-env, export-portable, DB/API tests, and infra tests.

Verification:

• git diff --check passed.
• pytest tests/docs_app/test_infra_template.py passed: 6 passed.
• npm --prefix work-engine test -- setup-env.test.ts passed: 621 passed. The repo test script runs the full work-engine unit suite plus the requested setup-env test.
• npm --prefix work-engine run typecheck passed.
• sam validate --lint --template-file lambda-functions/template.full.yaml could not run because sam is not installed in this environment.
• Additional template smoke check passed for env wiring and removal of unused DynamoDB actions.

No source repos were modified. No commit or push.