Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
Download platform/core/amazon_projects.py from fsanyoto/runloopable: direct link, hf CLI and curl.
- Browser
- Download file 68.8 kB
-
https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/core/amazon_projects.py
- Command line
-
hf download hf://spaces/fsanyoto/runloopable/platform/core/amazon_projects.py
-
curl -L -o amazon_projects.py https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/core/amazon_projects.py
68.8 kB
"""Per-tenant PostgreSQL Amazon projects, each assigned to many SKUs, ASINs and Parent ASINs.
No request reads the `user_tables` document for a project (C6). Core imports no harness module
(verify_layering) and no App module (rule 13). Tables are created on the first write.
A door called with `by` appends one `core.activity_log` event per create, field or target change.
"""
from contextlib import contextmanager
from datetime import date, datetime, timezone
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
import json
import hashlib
import heapq
import re
import sys
import uuid
PROJECTS = 'amazon_projects'
TARGETS = 'amazon_project_targets'
MEMBERS = 'amazon_project_members'
TABLES = (PROJECTS, TARGETS, MEMBERS)
GRAINS = ('sku', 'asin', 'parent_asin')
#: R18: the fixed status set of SDLC 82.
STATUSES = ('Backlog', 'Planned', 'In progress', 'Completed', 'Canceled')
COMPLETED = 'Completed'
CANCELED = 'Canceled'
PRIORITIES = ('No priority', 'Urgent', 'High', 'Medium', 'Low')
TYPES = ('project', 'pdp')
#: Health turns At risk this many days before the target date.
AT_RISK_DAYS = 7
CHUNK = 200
PROJECT_COLUMNS = ('id', 'tenant', 'name', 'status', 'priority', 'owner', 'start_on', 'target_on',
'progress', 'notes', 'completed_on', 'source_id', 'created_at', 'updated_at',
'created_by')
TARGET_COLUMNS = ('project_id', 'grain', 'key', 'added_at')
EDITABLE = frozenset(('name', 'status', 'priority', 'owner', 'start_on', 'target_on', 'progress', 'notes',
'completed_on', 'result_configs', 'contributors'))
RESULT_CONFIGS_KEY = '_resultConfigs'
DATES = ('start_on', 'target_on', 'completed_on')
NOTES_BYTES = 65536
#: Activity events use kind "app" on this object key. KINDS is a DB CHECK, so no kind is added.
ACTIVITY_KEY = 'amazon_projects'
ACTIVITY_LIMIT = 200
#: The camelCase name an activity entry carries for each editable field. Status is logged last.
ACTIVITY_FIELDS = {'name': 'name', 'priority': 'priority', 'owner': 'owner', 'start_on': 'startOn',
'target_on': 'targetOn', 'progress': 'progress', 'notes': 'notes',
'completed_on': 'completedOn', 'result_configs': 'resultConfigs', 'status': 'status'}
GRAIN_LABELS = {'sku': 'SKU', 'asin': 'ASIN', 'parent_asin': 'Parent ASIN'}
LISTING_ACTIVITY_FIELDS = {'title': 'Title', 'item_name': 'Title', 'image': 'Main image',
'main_image': 'Main image', 'main_image_url': 'Main image',
'main_product_image_locator': 'Main image',
'description': 'Description', 'product_description': 'Description',
'bullet_point': 'Bullet points'}
#: R39: an old `ut_amazon_projects` status becomes (status, progress).
MOVE_STATUS = {'Open': ('In progress', 0), 'Done': ('Completed', 100)}
_GRAIN_WORDS = {'sku': 'sku', 'asin': 'asin', 'parent asin': 'parent_asin', 'parent_asin': 'parent_asin'}
MOVED_NAME = 'Untitled project'
LISTINGS = 'amzn_listings'
PRICING = 'amzn_pricing'
SEARCH_MAX = 500
OWNER_PREFIX = 'loopable-user:'
def owner_value(tenant, identity):
"""Encode a tenant-bound canonical account identity in the existing owner text column."""
value = {'tenant': _text(tenant, 'tenant', limit=128),
'username': _text(identity.get('username'), 'ownerUsername', limit=128).lower(),
'name': _text(identity.get('name'), 'owner name', limit=512)}
return _text(OWNER_PREFIX + json.dumps(value, ensure_ascii=False), 'owner')
def owner_identity(value, tenant=None):
"""Decode a stored account identity while preserving every legacy text owner."""
if not isinstance(value, str) or not value.startswith(OWNER_PREFIX):
return None
try:
identity = json.loads(value[len(OWNER_PREFIX):])
if (not isinstance(identity, dict)
or not all(isinstance(identity.get(k), str) and identity[k]
for k in ('tenant', 'username', 'name'))
or (tenant is not None and identity['tenant'] != tenant)):
return None
return identity
except (ValueError, TypeError):
return None
def _member_values(values, tenant):
"""Normalize unique contributor identities bound to the project's tenant."""
if not isinstance(values, (list, tuple)):
raise ValueError('Contributors must be a list of workspace users.')
members = {}
for identity in values:
if not isinstance(identity, dict) or identity.get('tenant') != tenant:
raise ValueError('Each contributor must belong to this workspace.')
canonical = owner_identity(owner_value(tenant, identity), tenant)
members[canonical['username']] = {key: canonical[key] for key in ('username', 'name')}
return sorted(members.values(), key=lambda member: member['username'])
def _in_sql(values):
return ', '.join(f"'{value}'" for value in values)
#: The DDL clause that refuses a status outside R18. Kept whole so a gate can name it.
STATUS_CHECK = f'CHECK (status IN ({_in_sql(STATUSES)}))'
class BackendUnavailable(RuntimeError):
"""Project rows require PostgreSQL and never fall back to an HF document."""
class FixtureAdapter:
"""Explicit offline fixture marker, never selected as a production fallback."""
class MirrorUnreadable(RuntimeError):
"""The Amazon mirror refused a read."""
def _now():
# Microseconds order two projects created in the same second.
return datetime.now(timezone.utc).isoformat(timespec='microseconds')
def _today():
from core import periods
return periods.today()
def _text(value, field, *, optional=False, limit=1024):
if value is None and optional:
return None
if (not isinstance(value, str) or not value.strip() or '\x00' in value
or len(value.encode('utf-8')) > limit):
raise ValueError(f'{field} must contain between 1 and {limit} bytes of text.')
return value.strip()
def _date(value, field):
if value is None:
return None
if isinstance(value, datetime):
return value.date().isoformat()
if isinstance(value, date):
return value.isoformat()
if not isinstance(value, str) or not re.fullmatch(r'\d{4}-\d{2}-\d{2}', value):
raise ValueError(f'{field} must use YYYY-MM-DD.')
try:
return date.fromisoformat(value).isoformat()
except ValueError:
raise ValueError(f'{field} must be a valid calendar date.') from None
def _choice(value, field, allowed):
if not isinstance(value, str) or value not in allowed:
raise ValueError(f'{field} must be one of: {", ".join(allowed)}.')
return value
def _progress(value):
if type(value) is not int or not 0 <= value <= 100:
raise ValueError('progress must be a whole number from 0 to 100.')
return value
def _target(grain, key):
grain = _choice(grain, 'grain', GRAINS)
key = _text(key, 'key')
return grain, key.upper() if grain != 'sku' else key
def target(grain, key):
"""The normalized (grain, key) pair. ValueError for an unknown grain or an empty key."""
return _target(grain, key)
def _targets(values):
"""Unique (grain, key) pairs in first-seen order, from dicts or pairs."""
out, seen = [], set()
for value in values or ():
pair = _target(value.get('grain'), value.get('key')) if isinstance(value, dict) else _target(*value)
if pair not in seen:
seen.add(pair)
out.append(pair)
return out
def _fields(values, *, previous=None):
"""Validate a full project from `values` over `previous`, and derive its completion date."""
result = {**(previous or {}), **values}
result['name'] = _text(result.get('name'), 'name')
result['status'] = _choice(result.get('status'), 'status', STATUSES)
result['priority'] = _choice(result.get('priority'), 'priority', PRIORITIES)
result['owner'] = _text(result.get('owner'), 'owner', optional=True)
result['notes'] = _text(result.get('notes'), 'notes', optional=True, limit=NOTES_BYTES)
result['progress'] = _progress(result.get('progress'))
for field in DATES:
result[field] = _date(result.get(field), field)
if result['start_on'] is not None and result['target_on'] is not None and result['target_on'] < result['start_on']:
raise ValueError('Target date cannot be before the start date.')
from core import amazon_project_results
result['result_configs'] = amazon_project_results.configurations(result.get('result_configs', ()))
if (values.get('completed_on') is not None and result['status'] != COMPLETED):
raise ValueError('completed_on is set only on a Completed project.')
# R68: Impact counts from the first day the project turned Completed.
if result['status'] == COMPLETED:
result['completed_on'] = result['completed_on'] or _today().isoformat()
if date.fromisoformat(result['completed_on']) > _today():
raise ValueError('completed_on cannot be in the future.')
elif result['completed_on'] is not None and previous is None:
raise ValueError('completed_on is set only on a Completed project.')
else:
result['completed_on'] = None
return result
def _with_result_configs(impact, configs):
"""Keep authored configurations separate from replaceable result figures in the existing JSONB."""
out = dict(impact or {})
out.pop(RESULT_CONFIGS_KEY, None)
if configs:
out[RESULT_CONFIGS_KEY] = configs
return out or None
def _record(values, columns=PROJECT_COLUMNS):
if values is None:
raise KeyError('Project not found.')
result = dict(zip(columns, values))
for field in DATES:
result[field] = _date(result[field], field)
if len(values) > len(columns):
result['target_count'] = int(values[len(columns)] or 0)
for offset, field in enumerate(('rollup', 'impact'), len(columns) + 1):
value = values[offset] if len(values) > offset else None
result[field] = json.loads(value) if isinstance(value, str) else value
result['result_configs'] = (result.get('impact') or {}).get(RESULT_CONFIGS_KEY, [])
value = values[len(columns) + 3] if len(values) > len(columns) + 3 else None
members = json.loads(value) if isinstance(value, str) else value
result['contributors'] = sorted(members or [], key=lambda member: member['username'])
result['type'] = values[len(columns) + 4] if len(values) > len(columns) + 4 else 'project'
result['listed'] = bool(values[len(columns) + 5]) if len(values) > len(columns) + 5 else True
return result
def _backend(rt):
if rt is None:
raise BackendUnavailable('Projects require an explicitly bound PostgreSQL tenant runtime.')
bound = rt._store() if callable(getattr(rt, '_store', None)) else rt
from core import store_pg
if not isinstance(bound, (store_pg.PgStore, FixtureAdapter)):
raise BackendUnavailable('Projects require PostgreSQL (STORE_BACKEND=pg). HF document storage is unsupported.')
slug = getattr(bound, 'tenant_slug', None)
if (not slug or getattr(rt, 'key', slug) != slug or getattr(rt, 'store_namespace', '')
or getattr(getattr(rt, 'tenant', None), 'key', slug) != slug):
raise ValueError('The projects runtime and its tenant store must match.')
if isinstance(bound, FixtureAdapter):
return bound
return PgProjectStore(bound)
# --------------------------------------------------------------------------- pure rules
def health(project, today):
"""Contract C6 health: None when Completed, Canceled or undated, else Off track, At risk or On track."""
target = project.get('target_on')
if project.get('status') in (COMPLETED, CANCELED) or not target:
return None
days = (date.fromisoformat(_date(target, 'target_on'))
- date.fromisoformat(_date(today, 'today'))).days
if days < 0:
return 'Off track'
return 'At risk' if days <= AT_RISK_DAYS else 'On track'
def summary(projects):
"""Average progress with Completed at 100 and Canceled excluded."""
counted = [p for p in projects if p.get('status') != CANCELED]
total = len(counted)
return {'average': round(sum(100 if p.get('status') == COMPLETED else p['progress']
for p in counted) / total) if total else None,
'completed': sum(1 for p in counted if p.get('status') == COMPLETED), 'total': total}
# --------------------------------------------------------------------------- doors
def create(rt, *, name, status='Backlog', priority='No priority', owner=None, start_on=None,
target_on=None, progress=0, notes=None, targets=(), created_by=None,
completed_on=None, source_id=None, by=None, result_configs=(), contributors=()):
"""Insert one project and its targets in one transaction. `source_id` is unique per tenant."""
values = _fields(dict(name=name, status=status, priority=priority, owner=owner, start_on=start_on,
target_on=target_on, progress=progress, notes=notes, completed_on=completed_on,
result_configs=result_configs))
backend = _backend(rt)
values['contributors'] = _member_values(contributors, backend.tenant_slug)
stamp = _now()
values.update(id=uuid.uuid4().hex, tenant=backend.tenant_slug, created_at=stamp, updated_at=stamp,
created_by=_text(created_by or by, 'created_by', optional=True),
source_id=_text(source_id, 'source_id', optional=True))
record = backend.insert(values, _targets(targets), stamp)
if by is not None:
_log(rt, record['id'], by, 'project', None, record['name'])
_log_members(rt, record['id'], by, [], record['contributors'])
return record
def get(rt, project_id):
"""One project with its target count. KeyError when it is not in this tenant."""
return _backend(rt).get(_text(project_id, 'project_id'))
def update(rt, project_id, changes, by=None):
"""Change only the named fields. Turning Completed sets the completion date once."""
if not isinstance(changes, dict) or not changes or set(changes) - EDITABLE:
raise ValueError(f'Update only: {", ".join(sorted(EDITABLE))}.')
project_id = _text(project_id, 'project_id')
backend = _backend(rt)
before = backend.get(project_id) if by is not None else None
after = backend.update(project_id, changes, _now())
if by is not None:
# One event per changed field. Status goes last, so it reads back first.
for field, name in ACTIVITY_FIELDS.items():
if field == 'completed_on' and field not in changes:
continue
if before[field] != after[field]:
_log(rt, project_id, by, name, before[field], after[field])
_log_members(rt, project_id, by, before['contributors'], after['contributors'])
return after
def delete(rt, project_id, by=None):
"""Delete one project, its targets and its contributors in one transaction."""
project_id = _text(project_id, 'project_id')
backend = _backend(rt)
name = backend.get(project_id)['name'] if by is not None else None
result = backend.delete(project_id)
if by is not None:
_log(rt, project_id, by, 'project', name, None)
return result
def list_projects(rt):
"""Every project of the tenant, oldest first, with target counts."""
return _backend(rt).list_all()
def targets(rt, project_id):
"""The (grain, key) targets of one project, in the order they were added."""
return _backend(rt).targets(_text(project_id, 'project_id'))
def projects_for(rt, pairs):
"""`{(grain, key): [project]}` for every requested pair, read in one indexed query per 200 pairs."""
wanted = _targets(pairs)
return _backend(rt).projects_for(wanted) if wanted else {}
def _pairs_of(listed):
return {(t['grain'], t['key']) for t in listed}
def add_targets(rt, project_id, values, by=None):
"""Assign the project to more SKUs, ASINs or Parent ASINs. A repeated pair is kept once."""
wanted = _targets(values)
if not wanted:
raise ValueError('Name at least one target.')
project_id = _text(project_id, 'project_id')
backend = _backend(rt)
before = _pairs_of(backend.targets(project_id)) if by is not None else set()
result = backend.add_targets(project_id, wanted, _now())
added = [pair for pair in wanted if pair not in before]
if by is not None and added:
_log(rt, project_id, by, 'targets', None, _pair_list(added))
return result
def remove_targets(rt, project_id, values, by=None):
"""Unassign the named pairs from the project. An unassigned pair is ignored."""
wanted = _targets(values)
if not wanted:
raise ValueError('Name at least one target.')
project_id = _text(project_id, 'project_id')
backend = _backend(rt)
before = _pairs_of(backend.targets(project_id)) if by is not None else set()
result = backend.remove_targets(project_id, wanted, _now())
removed = [pair for pair in wanted if pair in before]
if by is not None and removed:
_log(rt, project_id, by, 'targets', _pair_list(removed), None)
return result
def source_ids(rt):
"""The source ids already copied into this tenant's store, for a guarded move."""
return _backend(rt).source_ids()
# --------------------------------------------------------------------------- period and Impact
def _sku_targets(computed):
index = {}
for sku, cells in computed.items():
for grain in GRAINS:
key = sku if grain == 'sku' else str(cells.get(grain) or '').strip().upper()
if key:
index.setdefault((grain, key), []).append(sku)
return index
def _project_skus(pairs, index):
"""Resolve overlapping targets against supplied SKU cells, counting each SKU once."""
matched = []
for pair in pairs:
matched.extend(index.get(pair, ()))
return sorted(set(matched))
def _total(values, *, money=False):
"""Sum known finite cells, leaving an empty or partly unknown total blank."""
values = list(values)
if not values or any(value is None or value == '' for value in values):
return None
try:
numbers = [Decimal(str(value)) for value in values]
except InvalidOperation:
return None
if any(not number.is_finite() for number in numbers):
return None
total = sum(numbers, Decimal(0))
return float(total.quantize(Decimal('.01'), rounding=ROUND_HALF_UP)) if money else int(total)
def _period_rollup(skus, computed, period, stamp):
return {'period': period, 'netSales': _total((computed[s].get('net_sales') for s in skus), money=True),
'netProfit': _total((computed[s].get('net_profit') for s in skus), money=True),
'units': _total(computed[s].get('units_sold') for s in skus), 'asOf': stamp}
def _project_impact(project, skus, *, today, reader, words):
"""Aggregate validated per-SKU results for equal completion and prior-year windows."""
out = dict(netSales=None, units=None, priorNetSales=None, priorUnits=None, state=None)
completed = project.get('completed_on')
if project['status'] != COMPLETED or not completed:
out['state'] = words['waiting_for_completion']
return out
if date.fromisoformat(completed) >= today:
token = 'waiting_for_completed_day' if completed == today.isoformat() else 'unavailable'
out['state'] = words[token]
return out
if not skus:
out['state'] = words['missing_grain_or_key']
return out
fields = (('netSales', 'sales', 'current'), ('units', 'units', 'current'),
('priorNetSales', 'sales', 'comparison'), ('priorUnits', 'units', 'comparison'))
values = {field: [] for field, _metric, _window in fields}
states, scopes = [], set()
for sku in skus:
action = dict(id=project['id'], grain='sku', key=sku, status='done', completed_on=completed)
try:
result = reader(action, today=today)
except Exception as exc: # noqa: BLE001
print(f'[amazon_projects] Impact unavailable for {project["id"]}: {type(exc).__name__}',
file=sys.stderr)
result = {'state': 'unavailable'}
if result.get('state') != 'ready':
states.append(result.get('state') or 'unavailable')
scope = result.get('scope')
if scope:
scopes.add((scope.get('marketplace_id'), scope.get('currency')))
for field, metric, window in fields:
figure = next((f for f in result.get('figures', ()) if f.get('id') == metric), {})
cell = figure.get(window) or {}
value = cell.get('value') if cell.get('validated') else None
values[field].append(value)
if value is None or cell.get('status') not in (None, 'ready', 'available'):
states.append(cell.get('status') or 'unavailable')
if len(scopes) > 1:
out['state'] = words['unavailable']
return out
for field, metric, _window in fields:
out[field] = _total(values[field], money=metric == 'sales')
if states:
token = next((s for s in ('not_verified', 'unavailable', 'no_prior_data', 'partial_data')
if s in states), states[0])
out['state'] = words.get(token, token)
return out
def update_projects(rt, computed, *, period, impact_reader, impact_state_words, today=None, as_of=None,
period_label=None):
"""Publish only grid-validated period rollups and Impact in one refresh-only UPDATE.
impact_reader(action, *, today) returns sku_action_results.results JSON for one SKU.
It maps validation errors to state=not_verified and source errors to state=unavailable.
The caller formats net_sales, net_profit and units_sold through its grid cells() writer first.
Preserve SKU, ASIN and parent_asin identity fields when supplying those stored cells.
period_label names the persisted grid window and defaults to period.
Core never reads the mirror or imports the module supplying that callback.
"""
backend = _backend(rt)
period = _text(period, 'period', limit=200)
period_label = period if period_label is None else _text(period_label, 'period_label', limit=200)
day, stamp = today or _today(), as_of or _now()
records = backend.list_all()
assignments = backend.all_targets() if records else {}
grid = _grid_cells(backend, records)
index = _sku_targets(computed)
wanted, checks = {}, []
for record in records:
skus = _project_skus(assignments.get(record['id'], ()), index)
rollup = _period_rollup(skus, computed, period, stamp)
check = _validate_rollup(record['id'], rollup, grid.get(record['id'], ()),
period=period, period_label=period_label)
checks.append(check)
if not check['ok']:
continue
wanted[record['id']] = {
'rollup': rollup,
'impact': _project_impact(record, skus, today=day, reader=impact_reader, words=impact_state_words)}
if wanted:
backend.write_rollups(wanted)
return {'projects': len(wanted),
'measured': sum(v['impact']['netSales'] is not None and v['impact']['priorNetSales'] is not None
for v in wanted.values()),
'rejected': [{'project': c['project'], 'cause': c['cause']} for c in checks if not c['ok']],
'validation': checks}
def _grid_cells(backend, records):
"""Group independently queried persisted grid cells by project."""
grid = {}
for project_id, value in backend.grid_rollup_rows() if records else ():
grid.setdefault(project_id, []).append(value)
return grid
def _validate_rollup(project_id, rollup, values, *, period, period_label):
"""Compare a rollup with persisted grid cells without either rollup builder."""
cause = None
rollup = rollup or {}
try:
cells = [json.loads(value) if isinstance(value, str) else value for value in values]
if not cells:
cause = 'No persisted SKU grid cells match this project.'
elif not all(isinstance(cell, dict) for cell in cells):
cause = 'The persisted SKU grid contains invalid cells.'
elif rollup.get('period') != period or not rollup.get('asOf'):
cause = 'The project period or asOf is missing or does not match.'
elif period_label is not None and any(c.get('period') != period_label for c in cells):
cause = 'The persisted SKU grid period does not match the candidate.'
else:
for field, key in (('netSales', 'net_sales'), ('netProfit', 'net_profit'), ('units', 'units_sold')):
numbers = [Decimal(str(c[key])) for c in cells if c.get(key) not in (None, '')]
if any(not number.is_finite() for number in numbers):
cause = f'The persisted SKU grid contains an invalid {key} value.'
break
expected = sum(numbers, Decimal(0)) if len(numbers) == len(cells) else None
actual = rollup.get(field)
matches = actual is None if expected is None else (
actual is not None and Decimal(str(actual)) == expected)
if not matches:
cause = f'Project {field} does not match the persisted SKU grid.'
break
except (InvalidOperation, ValueError, TypeError):
cause = 'The project or persisted SKU grid contains an invalid value.'
return {'check': 'project rollups tie to the SKU grid', 'project': project_id,
'ok': cause is None, 'cause': cause}
def validate(rt, *, period, period_label=None):
"""Tie stored project rollups to persisted SKU grid cells through an independent SQL query."""
backend = _backend(rt)
records = backend.list_all()
grid = _grid_cells(backend, records)
return [_validate_rollup(record['id'], record.get('rollup'), grid.get(record['id'], ()),
period=period, period_label=period_label) for record in records]
# --------------------------------------------------------------------------- activity
def _pair_list(pairs):
return [{'grain': grain, 'key': key} for grain, key in pairs]
def _log(rt, project_id, by, field, before, after):
"""Append one project event. A failed append is logged, never undoes the committed write."""
from core import activity_log
try:
if field == 'dependencies':
from core import amazon_project_tasks
names = {task['id']: task['name'] for task in amazon_project_tasks.list_tasks(rt, project_id)}
before, after = [dict(value, from_name=names.get(value['from_task'], value['from_task']),
to_name=names.get(value['to_task'], value['to_task']))
if isinstance(value, dict) else value for value in (before, after)]
activity_log.append(rt, kind='app', object={'app': ACTIVITY_KEY, 'id': project_id},
by=_text(by, 'by'), changes={field: {'before': before, 'after': after}})
except Exception as exc: # noqa: BLE001
print(f'[amazon_projects] activity append failed for {project_id}: {type(exc).__name__}: {exc}',
file=sys.stderr)
def _log_members(rt, project_id, by, before, after):
"""Append one event for each contributor username added or removed."""
old = {member['username']: member for member in before}
new = {member['username']: member for member in after}
for username in sorted(old.keys() - new.keys()):
_log(rt, project_id, by, 'contributors', old[username], None)
for username in sorted(new.keys() - old.keys()):
_log(rt, project_id, by, 'contributors', None, new[username])
def _shown(value):
"""A target list as one line of text, else the value."""
if isinstance(value, list):
if value and isinstance(value[0], dict) and 'metric' in value[0]:
return ', '.join(v['metric'] for v in value)
return ', '.join(f'{GRAIN_LABELS.get(t.get("grain"), t.get("grain"))} {t.get("key")}'
for t in value if isinstance(t, dict))
if isinstance(value, dict) and 'username' in value:
return value['name']
identity = owner_identity(value)
return identity['name'] if identity else value
def _activity_value(value, task_names):
"""Show task and dependency names without exposing their stored record payloads."""
if isinstance(value, dict):
task = value.get('task', value)
if isinstance(task, dict) and 'name' in task and 'username' not in task:
return task['name']
if 'from_task' in value and 'to_task' in value:
return ' -> '.join(value.get(key.replace('_task', '_name')) or task_names.get(value[key], value[key])
for key in ('from_task', 'to_task'))
return _shown(value)
TASK_ACTIVITY_FIELDS = ('name', 'assignee', 'start_on', 'due_on', 'done')
CLIPPED = ' [truncated]'
def _skip_space(text, position):
while position < len(text) and text[position].isspace():
position += 1
return position
def _clipped_members(preview):
"""Read the members of a clipped JSON object that end before the cut, entering an open task."""
decoder, out, position = json.JSONDecoder(), {}, 1
while True:
try:
key, position = decoder.raw_decode(preview, _skip_space(preview, position))
except ValueError:
return out
position = _skip_space(preview, position)
if not isinstance(key, str) or preview[position:position + 1] != ':':
return out
position = _skip_space(preview, position + 1)
try:
value, position = decoder.raw_decode(preview, position)
except ValueError:
# A reschedule logs {shifted, task}, so its task is often the member the cut opens.
return {'task': _clipped_members(preview[position:])} if (
key == 'task' and preview[position:position + 1] == '{') else out
position = _skip_space(preview, position)
# A scalar that runs into the cut may itself be cut, so keep it only before a comma or brace.
if preview[position:position + 1] not in (',', '}'):
return out
out[key] = value
if preview[position] == '}':
return out
position += 1
def _task_fields(value, truncated, actor_names):
"""One task side as its person-readable fields, and whether the log clipped it."""
clipped = bool(truncated) and isinstance(value, str) and value.endswith(CLIPPED)
if clipped:
preview = value[:-len(CLIPPED)]
value = _clipped_members(preview) if preview.startswith('{') else {}
if not isinstance(value, dict):
return {}, True
if 'name' not in value and isinstance(value.get('task'), dict):
value = value['task']
fields = {key: value[key] for key in TASK_ACTIVITY_FIELDS
if key in value and (value[key] is None or isinstance(value[key], (str, bool)))}
if isinstance(fields.get('assignee'), str):
fields['assignee'] = actor_names.get(fields['assignee'], fields['assignee'])
return fields, clipped
def _task_change(before, after, truncated, actor_names):
"""A changed task as its name and each readable field that differs, never its stored record."""
(old, old_cut), (new, new_cut) = (_task_fields(side, truncated, actor_names) for side in (before, after))
partial = old_cut or new_cut
changed = {key for key in TASK_ACTIVITY_FIELDS if (key in old and key in new and old[key] != new[key])
or (not partial and (key in old) != (key in new))}
sides = []
for fields, cut in ((old, old_cut), (new, new_cut)):
side = {key: value for key, value in fields.items() if key == 'name' or key in changed}
# The marker tells the client a clipped side compares only the fields both sides kept.
sides.append(json.dumps(side, ensure_ascii=False) + CLIPPED if cut else side)
return sides
def _activity_target_pair(change, truncated):
"""Recover an absent side only from a bounded JSON target-list pair."""
before, after = change.get('before'), change.get('after')
marker = ' [truncated]'
if truncated and all(isinstance(value, str) and value.endswith(marker) for value in (before, after)):
old, new = before[:-len(marker)], after[:-len(marker)]
if old == 'null' and new.startswith('['):
before = None
elif new == 'null' and old.startswith('['):
after = None
# Retain the preview marker because clipped arrays cannot establish a complete target list.
return before, after
def _activity_target_preview(value, truncated):
"""Show complete target identities from a bounded JSON array without guessing its missing tail."""
marker = ' [truncated]'
if not truncated or not isinstance(value, str) or not value.endswith(marker):
return value
preview = value[:-len(marker)]
if preview and not preview.startswith('['):
return value
decoder, targets, position = json.JSONDecoder(), [], 1
while position < len(preview):
while position < len(preview) and preview[position].isspace():
position += 1
try:
target, position = decoder.raw_decode(preview, position)
except ValueError:
break
if (not isinstance(target, dict) or target.get('grain') not in GRAIN_LABELS
or not isinstance(target.get('key'), str) or not target['key']):
break
targets.append(target)
while position < len(preview) and preview[position].isspace():
position += 1
if position == len(preview) or preview[position] != ',':
break
position += 1
shown = _shown(targets)
return f'{shown}. Target list incomplete' if shown else 'Target list incomplete'
def _pdp_item_stages(value, pdp):
"""Accept complete typed workflow item identities before comparing historical stages."""
if not isinstance(value, dict) or value.get('type') != 'pdp' or not isinstance(value.get('items'), list):
return None
stages = {}
for item in value['items']:
if (not isinstance(item, dict) or not isinstance(item.get('asin'), str)
or not isinstance(item.get('stage'), str) or item['stage'] not in pdp.STAGE_PROGRESS
or item['asin'] in stages):
return None
stages[item['asin']] = item['stage']
return stages
def _pdp_activity_facts(event, field, before, after):
"""Project only C4 action facts retained in the bounded stored event."""
from core import amazon_pdp as pdp
if event.get('via') != pdp.VIA:
return None
unavailable = {'action': 'unavailable'}
if event.get('changes_truncated'):
return unavailable
old, new = (_pdp_item_stages(value, pdp) for value in (before, after))
if field == 'setup' and before is None and new is not None:
scope = after.get('scope')
scope = scope if isinstance(scope, list) and scope and all(key in pdp.SCOPE_PARTS for key in scope) else None
return {'action': 'created', 'scope': scope}
if old is not None and new is not None:
changed = {stage for asin, stage in new.items() if asin in old and old[asin] != stage}
if len(changed) == 1:
stage = next(iter(changed))
if field == ('publish' if stage == 'published' else stage):
return {'action': 'moved', 'stage': stage} if stage != 'published' else {'action': 'published'}
return {'action': 'setup'} if field in ('setup', 'competitors') and not changed else unavailable
if isinstance(after, dict) and isinstance(after.get('slot'), str) and after['slot'] in pdp.SLOTS:
part = pdp.SLOTS[after['slot']]['key']
if after.get('part') == part and field in ('draft', 'approve', 'request'):
return {'action': {'draft': 'saved', 'approve': 'approved', 'request': 'requested'}[field]}
return {'action': 'ai'} if field == 'ai' else unavailable
def _activity_entries(event, actor_names, task_names):
"""Project one log event into named activity entries."""
out = []
for index, (field, change) in enumerate((event.get('changes') or {}).items()):
before, after = change.get('before'), change.get('after')
pdp_facts = _pdp_activity_facts(event, field, before, after)
if field == 'project':
action, field = ('created' if before is None else 'deleted'), None
elif field == 'targets':
before, after = _activity_target_pair(change, event.get('changes_truncated'))
action = ('added' if before is None else 'removed' if after is None else 'changed') + ' targets'
before = _activity_target_preview(before, event.get('changes_truncated'))
after = _activity_target_preview(after, event.get('changes_truncated'))
elif field == 'contributors':
action = 'added contributor' if before is None else 'removed contributor'
elif field in ('tasks', 'dependencies'):
action = ('added' if before is None else 'removed' if after is None else 'changed') + ' ' + (
'task' if field == 'tasks' else 'dependency')
else:
action = 'changed'
if action == 'changed task':
shown = _task_change(before, after, event.get('changes_truncated'), actor_names)
else:
shown = [_activity_value(value, task_names) for value in (before, after)]
out.append({'id': f'loopable:{event["id"]}:{index}', 'at': event['occurred_at'],
'by': event['by'], 'byUsername': event['by'],
'byName': actor_names.get(event['by'], event['by']),
'action': action, 'field': field, 'from': shown[0], 'to': shown[1]})
if pdp_facts is not None:
out[-1]['pdp'] = pdp_facts
return out
def activity_page(rt, project_id, *, cursor=None, limit=ACTIVITY_LIMIT, read_changes=None,
actor_names=None, task_names=None):
"""Merge bounded log pages with an injected C4 reader under one snapshot cutoff."""
from core import activity_log
if type(limit) is not int or not 1 <= limit <= ACTIVITY_LIMIT:
raise ValueError(f'limit must be between 1 and {ACTIVITY_LIMIT}.')
state = cursor or {'until': _now(), 'app': None, 'amazon': None}
if (not isinstance(state, dict) or set(state) != {'until', 'app', 'amazon'}
or not isinstance(state['until'], str)):
raise ValueError('Reload activity to continue.')
until = datetime.fromisoformat(state['until']).astimezone(timezone.utc).isoformat()
actor_names, task_names = actor_names or {}, task_names or {}
wanted = {'app': ACTIVITY_KEY, 'id': _text(project_id, 'project_id')}
def app_entries():
position = state['app']
# Re-read the boundary event because one event can contain several fields.
before = [position[0], position[1] + 1] if position else None
while True:
page = activity_log.read(rt, can_read_object=lambda obj: obj == wanted, object=wanted,
cursor=before, until=until, limit=limit)
for event in page['records']:
for index, entry in enumerate(_activity_entries(event, actor_names, task_names)):
at = entry['at']
next_position = [at, event['id'], index]
if position and at == position[0] and event['id'] == position[1] and index <= position[2]:
continue
yield ((at, 1, event['id'], -index), entry, 'app', next_position)
before = page['next_cursor']
if before is None:
return
def amazon_entries():
position = state['amazon']
skipped = 0
def candidates():
nonlocal skipped
for change in read_changes(None) if read_changes is not None else ():
at = datetime.fromisoformat(change['detected_at']).astimezone(timezone.utc).isoformat()
if at > until:
continue
identity = hashlib.sha256(json.dumps(change, sort_keys=True, default=str).encode()).hexdigest()
key = (at, identity)
if position:
if key > tuple(position[:2]):
continue
if key == tuple(position[:2]) and skipped < position[2]:
skipped += 1
continue
yield key, change
# C4 has no page argument, so retain only this response's bounded candidate window.
selected = heapq.nlargest(limit + 1, candidates(), key=lambda item: item[0])
previous, count = None, 0
for (at, identity), change in selected:
key = (at, identity)
count = count + 1 if key == previous else (
position[2] + 1 if position and key == tuple(position[:2]) else 1)
previous = key
attribute = change['attribute']
field = LISTING_ACTIVITY_FIELDS.get(attribute, attribute.replace('_', ' ').capitalize())
entry = {'id': f'amazon:{identity}:{count}', 'at': at, 'by': 'Amazon',
'byUsername': 'amazon', 'byName': 'Amazon', 'source': 'amazon',
'action': 'listing changed', 'field': field,
'from': change['before_value'], 'to': change['after_value'],
'attribute': change['attribute'], 'before': change['before_value'],
'after': change['after_value'], 'detectedAt': change['detected_at'],
'detectionSource': change['source'], 'sku': change['sku'], 'asin': change['asin'],
'marketplaceId': change['marketplace_id']}
yield ((at, 0, identity, -count), entry, 'amazon', [at, identity, count])
merged = heapq.merge(app_entries(), amazon_entries(), key=lambda item: item[0], reverse=True)
rows = []
next_state = dict(state)
for _key, entry, source, position in merged:
if len(rows) == limit:
return {'activity': rows, 'next_cursor': next_state}
rows.append(entry)
next_state[source] = position
return {'activity': rows, 'next_cursor': None}
def activity(rt, project_id, limit=ACTIVITY_LIMIT, **kwargs):
"""Return the first merged activity page for legacy project detail consumers."""
return activity_page(rt, project_id, limit=limit, **kwargs)['activity']
# --------------------------------------------------------------------------- the move (R39)
def _loose_date(value):
text = str(value or '').strip()[:10]
try:
return _date(text, 'date') if text else None
except ValueError:
return None
def _loose_text(value, limit=1000):
text = ' '.join(str(value).split()) if isinstance(value, (str, int)) and not isinstance(value, bool) else ''
return text[:limit] or None
def moved_fields(row):
"""The store fields of one `ut_amazon_projects` row: title, one target, dates, R39 status."""
status, progress = MOVE_STATUS.get(str(row.get('status') or '').strip(), MOVE_STATUS['Open'])
grain = _GRAIN_WORDS.get(' '.join(str(row.get('grain') or '').split()).lower())
key = _loose_text(row.get('key'), 200)
return dict(name=_loose_text(row.get('title'), 300) or key or MOVED_NAME, status=status, progress=progress,
owner=_loose_text(row.get('owner'), 200), start_on=_loose_date(row.get('start_on')),
target_on=_loose_date(row.get('deadline_on') or row.get('deadline')),
completed_on=_loose_date(row.get('completed_on')) if status == COMPLETED else None,
targets=[(grain, key)] if grain and key else [])
def move_rows(rt, rows):
"""Copy each `{source id: row}` of `ut_amazon_projects` once into the store. Returns the copied count."""
if not rows:
return 0
have = source_ids(rt)
copied = 0
for source, row in (rows or {}).items():
source = str(source)
if source in have or not isinstance(row, dict):
continue
create(rt, source_id=source, **moved_fields(row))
have.add(source)
copied += 1
return copied
# --------------------------------------------------------------------------- the Amazon mirror
def _mirror_columns(cur, table):
try:
return {r[0] for r in cur.execute(
'SELECT column_name FROM information_schema.columns WHERE table_name = ?', [table]).fetchall()}
except Exception as exc: # noqa: BLE001
raise MirrorUnreadable(type(exc).__name__) from exc
def _mirror_read(cur, sql, args=()):
try:
return cur.execute(sql, list(args)).fetchall()
except Exception as exc: # noqa: BLE001
raise MirrorUnreadable(type(exc).__name__) from exc
def _catalog(cur):
"""SQL of one row per listed SKU: (sku, asin, parent, title). None without listings."""
cols = _mirror_columns(cur, LISTINGS)
if 'seller_sku' not in cols:
return None
order = 'COALESCE(CAST("_snapshot_date" AS VARCHAR), \'\')' if '_snapshot_date' in cols else '1'
asin = (f'arg_max(upper(trim(CAST("asin1" AS VARCHAR))), {order})' if 'asin1' in cols
else 'CAST(NULL AS VARCHAR)')
title = (f'arg_max(CAST("item_name" AS VARCHAR), {order})' if 'item_name' in cols
else 'CAST(NULL AS VARCHAR)')
if {'parent_asin', 'child_asin'} <= _mirror_columns(cur, PRICING):
parents = (f'SELECT upper(trim(CAST("child_asin" AS VARCHAR))) AS child, '
f'max(upper(trim(CAST("parent_asin" AS VARCHAR)))) AS parent FROM {PRICING} '
'WHERE NULLIF(trim(CAST("parent_asin" AS VARCHAR)), \'\') IS NOT NULL GROUP BY 1')
else:
parents = 'SELECT CAST(NULL AS VARCHAR) AS child, CAST(NULL AS VARCHAR) AS parent WHERE false'
return (f'WITH s AS (SELECT trim(CAST("seller_sku" AS VARCHAR)) AS sku, {asin} AS asin, {title} AS title '
f'FROM {LISTINGS} WHERE NULLIF(trim(CAST("seller_sku" AS VARCHAR)), \'\') IS NOT NULL GROUP BY 1), '
f'p AS ({parents}), '
'c AS (SELECT s.sku, NULLIF(s.asin, \'\') AS asin, p.parent, s.title '
'FROM s LEFT JOIN p ON p.child = s.asin) ')
def _listing(row):
return {'sku': row[0], 'asin': row[1], 'parent_asin': row[2], 'title': row[3]}
def search(cur, q, limit=25, offset=0):
"""(listings, total): one SKU-ordered page; `total` counts every match, even past the end (rule 1)."""
if type(limit) is not int or not 1 <= limit <= SEARCH_MAX:
raise ValueError(f'limit must be a whole number from 1 to {SEARCH_MAX}.')
if type(offset) is not int or offset < 0:
raise ValueError('offset must be a nonnegative whole number.')
catalog = _catalog(cur)
if catalog is None:
return [], 0
needle = ' '.join(str(q or '').split()).lower()
rows = _mirror_read(cur, catalog + ', matched AS (SELECT sku, asin, parent, title FROM c '
'WHERE contains(lower(sku), ?) OR contains(lower(COALESCE(asin, \'\')), ?) '
'OR contains(lower(COALESCE(parent, \'\')), ?) '
'OR contains(lower(COALESCE(title, \'\')), ?)), '
'page AS (SELECT * FROM matched ORDER BY sku LIMIT ? OFFSET ?) '
'SELECT page.sku, page.asin, page.parent, page.title, totals.total '
'FROM (SELECT count(*) AS total FROM matched) totals LEFT JOIN page ON true '
'ORDER BY page.sku',
(needle, needle, needle, needle, limit, offset))
return [_listing(r) for r in rows if r[0] is not None], (int(rows[0][4]) if rows else 0)
def lineage(cur, grain, key):
"""The (grain, key) pairs above a row: a SKU's ASIN and Parent ASIN, an ASIN's Parent ASIN."""
grain, key = _target(grain, key)
if grain == 'parent_asin':
return []
catalog = _catalog(cur)
if catalog is None:
return []
if grain == 'sku':
rows = _mirror_read(cur, catalog + 'SELECT asin, parent FROM c WHERE sku = ?', (key,))
asin, parent = rows[0] if rows else (None, None)
else:
rows = _mirror_read(cur, catalog + 'SELECT parent FROM p WHERE child = ?', (key,))
asin, parent = None, rows[0][0] if rows else None
return _targets([pair for pair in (('asin', asin), ('parent_asin', parent)) if pair[1]])
def describe(cur, pairs):
"""`{(grain, key): {sku, asin, parent_asin, title}}` for project targets, from the listings."""
wanted = _targets(pairs)
out = {(grain, key): {'sku': key if grain == 'sku' else None, 'asin': key if grain == 'asin' else None,
'parent_asin': key if grain == 'parent_asin' else None, 'title': None}
for grain, key in wanted}
catalog = _catalog(cur) if wanted else None
if catalog is None:
return out
found = []
for part in _chunks(wanted):
marks = ', '.join('?' for _ in part)
found += _mirror_read(cur, catalog + 'SELECT sku, asin, parent, title FROM c '
f'WHERE sku IN ({marks}) OR asin IN ({marks}) OR parent IN ({marks}) ORDER BY sku',
[key for _, key in part] * 3)
for grain, key in wanted:
column = {'sku': 0, 'asin': 1, 'parent_asin': 2}[grain]
row = next((r for r in found if r[column] == key), None)
if row is None:
continue
item = out[(grain, key)]
item['title'] = row[3]
if grain == 'sku':
item.update(asin=row[1], parent_asin=row[2])
elif grain == 'asin':
item['parent_asin'] = row[2]
return out
def resolve_target_skus(cur, pairs):
"""Resolve every assigned target to canonical listed SKUs and refuse incomplete membership."""
wanted = _targets(pairs)
skus = {key for grain, key in wanted if grain == 'sku'}
grouped = [(grain, key) for grain, key in wanted if grain != 'sku']
if not grouped:
return sorted(skus)
catalog = _catalog(cur)
if catalog is None:
raise MirrorUnreadable('Amazon listings are unavailable for project target membership.')
for part in _chunks(grouped):
marks = ', '.join('?' for _ in part)
rows = _mirror_read(cur, catalog + 'SELECT sku, asin, parent FROM c '
f'WHERE asin IN ({marks}) OR parent IN ({marks})',
[key for _, key in part] * 2)
for grain, key in part:
matched = {row[0] for row in rows if row[1 if grain == 'asin' else 2] == key}
if not matched:
raise MirrorUnreadable(f'Project target membership is unavailable for {grain} {key}.')
skus.update(matched)
return sorted(skus)
# --------------------------------------------------------------------------- storage
def _chunks(values, size=CHUNK):
for start in range(0, len(values), size):
yield values[start:start + size]
def _marks(count):
return ', '.join(['%s'] * count)
class PgProjectStore:
"""Guarded SQL storage in the schema of one explicit PgStore."""
def __init__(self, bound):
from core import store_pg
if not isinstance(bound, store_pg.PgStore):
raise TypeError('A bound PgStore is required.')
self.tenant_slug = bound.tenant_slug
self.schema = store_pg._schema_for(self.tenant_slug)
if not re.fullmatch(r'[a-zA-Z0-9_]+', self.schema):
raise ValueError('Invalid tenant schema.')
def t(self, name):
return f'"{self.schema}"."{name}"'
@contextmanager
def _connection(self):
from core import store_pg
store_pg._effective_slug(self.tenant_slug)
with store_pg._pool().connection() as con:
yield con
def _write_guard(self):
from core import store_pg
store_pg.check_write(self.tenant_slug, 'write Amazon projects')
def _exists(self, con):
return con.execute('SELECT to_regclass(%s)', (self.t(PROJECTS),)).fetchone()[0] is not None
def ensure_schema(self, con):
"""Create project tables and their indexes, only on a write."""
con.execute(f'''CREATE TABLE IF NOT EXISTS {self.t(PROJECTS)} (
id TEXT NOT NULL, tenant TEXT NOT NULL, name TEXT NOT NULL,
status TEXT NOT NULL {STATUS_CHECK},
priority TEXT NOT NULL CHECK (priority IN ({_in_sql(PRIORITIES)})),
owner TEXT, start_on DATE, target_on DATE,
progress INTEGER NOT NULL CHECK (progress BETWEEN 0 AND 100),
notes TEXT, completed_on DATE, source_id TEXT,
created_at TEXT NOT NULL, updated_at TEXT NOT NULL, created_by TEXT,
rollup JSONB, impact JSONB,
type TEXT NOT NULL DEFAULT 'project' CHECK (type IN ({_in_sql(TYPES)})),
listed BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (tenant, id), UNIQUE (tenant, source_id),
CHECK ((status = '{COMPLETED}' AND completed_on IS NOT NULL)
OR (status <> '{COMPLETED}' AND completed_on IS NULL)))''')
con.execute(f'''CREATE TABLE IF NOT EXISTS {self.t(TARGETS)} (
tenant TEXT NOT NULL, project_id TEXT NOT NULL,
grain TEXT NOT NULL CHECK (grain IN ({_in_sql(GRAINS)})),
key TEXT NOT NULL, added_at TEXT NOT NULL,
PRIMARY KEY (tenant, project_id, grain, key),
FOREIGN KEY (tenant, project_id) REFERENCES {self.t(PROJECTS)} (tenant, id) ON DELETE CASCADE)''')
con.execute(f'''CREATE TABLE IF NOT EXISTS {self.t(MEMBERS)} (
tenant TEXT NOT NULL, project_id TEXT NOT NULL,
username TEXT NOT NULL, name TEXT NOT NULL, added_at TEXT NOT NULL,
PRIMARY KEY (tenant, project_id, username),
FOREIGN KEY (tenant, project_id) REFERENCES {self.t(PROJECTS)} (tenant, id) ON DELETE CASCADE)''')
# One row's lookup is one indexed read on this index.
con.execute(f'CREATE INDEX IF NOT EXISTS "amazon_project_targets_entity" '
f'ON {self.t(TARGETS)} (tenant, grain, key, project_id)')
additions = {'rollup': 'JSONB', 'impact': 'JSONB',
'type': f"TEXT NOT NULL DEFAULT 'project' CHECK (type IN ({_in_sql(TYPES)}))",
'listed': 'BOOLEAN NOT NULL DEFAULT TRUE'}
for field, declaration in additions.items():
con.execute(f'ALTER TABLE {self.t(PROJECTS)} ADD COLUMN IF NOT EXISTS {field} {declaration}')
@contextmanager
def _write(self):
self._write_guard()
with self._connection() as con:
self.ensure_schema(con)
yield con
def _select_sql(self, where, prefix='', members='NULL'):
cols = ', '.join(f'p.{c}' for c in PROJECT_COLUMNS)
count = (f'(SELECT count(*) FROM {self.t(TARGETS)} AS c '
'WHERE c.tenant = p.tenant AND c.project_id = p.id)')
return (f'SELECT {prefix}{cols}, {count}, {self._rollup_projection()}, {members}, '
f'{self._type_projection()}, {self._listed_projection()} '
f'FROM {self.t(PROJECTS)} AS p WHERE p.tenant = %s{where}')
def _rollup_projection(self):
# Old schemas read blank figures until a guarded write adds the columns.
return "to_jsonb(p)->'rollup', to_jsonb(p)->'impact'"
def _type_projection(self):
return "COALESCE(to_jsonb(p)->>'type', 'project')"
def _listed_projection(self):
return "COALESCE((to_jsonb(p)->>'listed')::boolean, TRUE)"
def _members_projection(self, con):
if con.execute('SELECT to_regclass(%s)', (self.t(MEMBERS),)).fetchone()[0] is None:
return 'NULL'
return ("(SELECT jsonb_agg(jsonb_build_object('username', m.username, 'name', m.name)) "
f'FROM {self.t(MEMBERS)} AS m WHERE m.tenant = p.tenant AND m.project_id = p.id)')
def _one(self, con, project_id, lock=False):
row = con.execute(self._select_sql(' AND p.id = %s', members=self._members_projection(con))
+ (' FOR UPDATE OF p' if lock else ''),
(self.tenant_slug, project_id)).fetchone()
return _record(row)
def _targets(self, con, project_id):
rows = con.execute(f'SELECT grain, key FROM {self.t(TARGETS)} WHERE tenant = %s AND project_id = %s '
'ORDER BY added_at, grain, key', (self.tenant_slug, project_id)).fetchall()
return [{'grain': row[0], 'key': row[1]} for row in rows]
def _insert_targets(self, con, project_id, pairs, stamp):
cols = ('tenant',) + TARGET_COLUMNS
for part in _chunks(pairs):
con.execute(f'INSERT INTO {self.t(TARGETS)} ({", ".join(cols)}) VALUES '
+ ', '.join(f'({_marks(len(cols))})' for _ in part)
+ ' ON CONFLICT (tenant, project_id, grain, key) DO NOTHING',
tuple(v for grain, key in part for v in (self.tenant_slug, project_id, grain, key, stamp)))
def _replace_members(self, con, project_id, members, previous, stamp):
wanted = {member['username'] for member in members}
removed = sorted(member['username'] for member in previous if member['username'] not in wanted)
for part in _chunks(removed):
con.execute(f'DELETE FROM {self.t(MEMBERS)} WHERE tenant = %s AND project_id = %s '
f'AND username IN ({_marks(len(part))})', (self.tenant_slug, project_id, *part))
for part in _chunks(members, 100):
con.execute(f'INSERT INTO {self.t(MEMBERS)} (tenant, project_id, username, name, added_at) VALUES '
+ ', '.join(f'({_marks(5)})' for _ in part)
+ ' ON CONFLICT (tenant, project_id, username) DO UPDATE SET name = EXCLUDED.name',
tuple(value for member in part for value in
(self.tenant_slug, project_id, member['username'], member['name'], stamp)))
def _touch(self, con, project_id, stamp):
current = self._one(con, project_id, lock=True)
impact = _with_result_configs(None, current['result_configs'])
con.execute(f'UPDATE {self.t(PROJECTS)} SET updated_at = %s, rollup = NULL, impact = %s::jsonb '
'WHERE tenant = %s AND id = %s',
(stamp, json.dumps(impact), self.tenant_slug, project_id))
def _insert(self, con, record, pairs, stamp):
columns = (*PROJECT_COLUMNS, 'impact', 'type', 'listed')
values = (*[record[c] for c in PROJECT_COLUMNS],
json.dumps(_with_result_configs(None, record.get('result_configs', []))),
record.get('type', 'project'), record.get('listed', True))
con.execute(f'INSERT INTO {self.t(PROJECTS)} ({", ".join(columns)}) '
f'VALUES ({_marks(len(PROJECT_COLUMNS))}, %s::jsonb, %s, %s)', values)
self._insert_targets(con, record['id'], pairs, stamp)
self._replace_members(con, record['id'], record.get('contributors', []), [], stamp)
def insert(self, record, pairs, stamp):
with self._write() as con:
self._insert(con, record, pairs, stamp)
return self._one(con, record['id'])
def get(self, project_id):
with self._connection() as con:
if not self._exists(con):
raise KeyError('Project not found.')
return self._one(con, project_id)
def update(self, project_id, changes, stamp):
with self._write() as con:
current = self._one(con, project_id, lock=True)
if current['type'] == 'pdp' and set(changes) & {'progress', 'status', 'completed_on'}:
raise ValueError('PDP item stages control project progress and completion.')
candidate = _fields(changes, previous=current)
if 'contributors' in changes:
candidate['contributors'] = _member_values(changes['contributors'], self.tenant_slug)
fields = sorted(EDITABLE - {'result_configs', 'contributors'})
changed_anchor = any(candidate[k] != current[k] for k in ('status', 'completed_on', 'result_configs'))
impact = _with_result_configs(None if changed_anchor else current['impact'], candidate['result_configs'])
con.execute(f'UPDATE {self.t(PROJECTS)} SET '
+ ', '.join(f'{field} = %s' for field in fields) + ', updated_at = %s, impact = %s::jsonb'
+ ' WHERE tenant = %s AND id = %s',
tuple(candidate[field] for field in fields)
+ (stamp, json.dumps(impact), self.tenant_slug, project_id))
if 'contributors' in changes:
self._replace_members(con, project_id, candidate['contributors'], current['contributors'], stamp)
return self._one(con, project_id)
def _delete(self, con, project_id):
for table in (MEMBERS, TARGETS):
con.execute(f'DELETE FROM {self.t(table)} WHERE tenant = %s AND project_id = %s',
(self.tenant_slug, project_id))
row = con.execute(f'DELETE FROM {self.t(PROJECTS)} WHERE tenant = %s AND id = %s RETURNING id',
(self.tenant_slug, project_id)).fetchone()
if row is None:
raise KeyError('Project not found.')
def delete(self, project_id):
with self._write() as con:
self._delete(con, project_id)
return {'deleted': project_id}
def list_all(self):
with self._connection() as con:
if not self._exists(con):
return []
rows = con.execute(self._select_sql(f' AND {self._listed_projection()} ORDER BY p.created_at, p.id',
members=self._members_projection(con)),
(self.tenant_slug,)).fetchall()
return [_record(row) for row in rows]
def targets(self, project_id):
with self._connection() as con:
if not self._exists(con):
raise KeyError('Project not found.')
self._one(con, project_id)
return self._targets(con, project_id)
def projects_for(self, pairs):
out = {pair: [] for pair in pairs}
with self._connection() as con:
if not self._exists(con):
return out
members = self._members_projection(con)
for part in _chunks(pairs):
match = ' OR '.join(['(t.grain = %s AND t.key = %s)'] * len(part))
cols = ', '.join(f'p.{c}' for c in PROJECT_COLUMNS)
count = (f'(SELECT count(*) FROM {self.t(TARGETS)} AS c '
'WHERE c.tenant = p.tenant AND c.project_id = p.id)')
rows = con.execute(
f'SELECT t.grain, t.key, {cols}, {count}, {self._rollup_projection()}, {members}, '
f'{self._type_projection()}, {self._listed_projection()} '
f'FROM {self.t(TARGETS)} AS t '
f'JOIN {self.t(PROJECTS)} AS p ON p.tenant = t.tenant AND p.id = t.project_id '
f'WHERE t.tenant = %s AND {self._listed_projection()} AND ({match}) '
'ORDER BY p.created_at, p.id',
(self.tenant_slug, *(v for pair in part for v in pair))).fetchall()
for row in rows:
out[(row[0], row[1])].append(_record(row[2:]))
return out
def all_targets(self):
"""Every project's target pairs in one tenant-scoped query, for refresh only."""
out = {}
with self._connection() as con:
if not self._exists(con):
return out
rows = con.execute(f'SELECT project_id, grain, key FROM {self.t(TARGETS)} '
'WHERE tenant = %s', (self.tenant_slug,)).fetchall()
for project_id, grain, key in rows:
out.setdefault(project_id, []).append((grain, key))
return out
def write_rollups(self, wanted):
"""Write all current-period rollups and Impact in one guarded statement per tenant."""
self._write_guard()
with self._connection() as con:
columns = {row[0] for row in con.execute(
'SELECT column_name FROM information_schema.columns '
'WHERE table_schema = %s AND table_name = %s AND column_name IN (%s, %s)',
(self.schema, PROJECTS, 'rollup', 'impact')).fetchall()}
if columns != {'rollup', 'impact'}:
self.ensure_schema(con)
cases, args = [], []
current = {}
for pid in sorted(wanted):
try:
current[pid] = self._one(con, pid, lock=True)
except KeyError:
continue
wanted = {pid: values for pid, values in wanted.items() if pid in current}
if not wanted:
return
for field in ('rollup', 'impact'):
cases.append(f'{field} = CASE id ' + ' '.join('WHEN %s THEN %s::jsonb' for _ in wanted)
+ f' ELSE {field} END')
for project_id, values in wanted.items():
value = (_with_result_configs(values[field], current[project_id]['result_configs'])
if field == 'impact' else values[field])
args.extend((project_id, json.dumps(value, allow_nan=False)))
con.execute(f'UPDATE {self.t(PROJECTS)} SET ' + ', '.join(cases)
+ f' WHERE tenant = %s AND id IN ({_marks(len(wanted))})',
(*args, self.tenant_slug, *wanted))
def grid_rollup_rows(self):
"""Read the persisted SKU grid by an independent target EXISTS query, without the builder."""
with self._connection() as con:
if con.execute('SELECT to_regclass(%s)', (self.t('user_table_records'),)).fetchone()[0] is None:
return []
return con.execute(
f'SELECT p.id, r.value FROM {self.t(PROJECTS)} AS p '
f'JOIN {self.t("user_table_records")} AS r ON r.table_key = %s '
'WHERE p.tenant = %s AND EXISTS ('
f'SELECT 1 FROM {self.t(TARGETS)} AS t '
'WHERE t.tenant = p.tenant AND t.project_id = p.id AND t.key = '
"CASE WHEN t.grain = 'sku' THEN trim(r.value->>t.grain) "
'ELSE upper(trim(r.value->>t.grain)) END)',
('ut_amazon_sku_profit', self.tenant_slug)).fetchall()
def add_targets(self, project_id, pairs, stamp):
with self._write() as con:
self._one(con, project_id, lock=True)
self._insert_targets(con, project_id, pairs, stamp)
self._touch(con, project_id, stamp)
return {'project': self._one(con, project_id), 'targets': self._targets(con, project_id)}
def remove_targets(self, project_id, pairs, stamp):
with self._write() as con:
self._one(con, project_id, lock=True)
for part in _chunks(pairs):
match = ' OR '.join(['(grain = %s AND key = %s)'] * len(part))
con.execute(f'DELETE FROM {self.t(TARGETS)} WHERE tenant = %s AND project_id = %s AND ({match})',
(self.tenant_slug, project_id, *(v for pair in part for v in pair)))
self._touch(con, project_id, stamp)
return {'project': self._one(con, project_id), 'targets': self._targets(con, project_id)}
def source_ids(self):
with self._connection() as con:
if not self._exists(con):
return set()
rows = con.execute(f'SELECT source_id FROM {self.t(PROJECTS)} '
'WHERE tenant = %s AND source_id IS NOT NULL', (self.tenant_slug,)).fetchall()
return {row[0] for row in rows}
"""Per-tenant PostgreSQL Amazon projects, each assigned to many SKUs, ASINs and Parent ASINs.
No request reads the `user_tables` document for a project (C6). Core imports no harness module
(verify_layering) and no App module (rule 13). Tables are created on the first write.
A door called with `by` appends one `core.activity_log` event per create, field or target change.
"""
from contextlib import contextmanager
from datetime import date, datetime, timezone
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
import json
import hashlib
import heapq
import re
import sys
import uuid
PROJECTS = 'amazon_projects'
TARGETS = 'amazon_project_targets'
MEMBERS = 'amazon_project_members'
TABLES = (PROJECTS, TARGETS, MEMBERS)
GRAINS = ('sku', 'asin', 'parent_asin')
#: R18: the fixed status set of SDLC 82.
STATUSES = ('Backlog', 'Planned', 'In progress', 'Completed', 'Canceled')
COMPLETED = 'Completed'
CANCELED = 'Canceled'
PRIORITIES = ('No priority', 'Urgent', 'High', 'Medium', 'Low')
TYPES = ('project', 'pdp')
#: Health turns At risk this many days before the target date.
AT_RISK_DAYS = 7
CHUNK = 200
PROJECT_COLUMNS = ('id', 'tenant', 'name', 'status', 'priority', 'owner', 'start_on', 'target_on',
'progress', 'notes', 'completed_on', 'source_id', 'created_at', 'updated_at',
'created_by')
TARGET_COLUMNS = ('project_id', 'grain', 'key', 'added_at')
EDITABLE = frozenset(('name', 'status', 'priority', 'owner', 'start_on', 'target_on', 'progress', 'notes',
'completed_on', 'result_configs', 'contributors'))
RESULT_CONFIGS_KEY = '_resultConfigs'
DATES = ('start_on', 'target_on', 'completed_on')
NOTES_BYTES = 65536
#: Activity events use kind "app" on this object key. KINDS is a DB CHECK, so no kind is added.
ACTIVITY_KEY = 'amazon_projects'
ACTIVITY_LIMIT = 200
#: The camelCase name an activity entry carries for each editable field. Status is logged last.
ACTIVITY_FIELDS = {'name': 'name', 'priority': 'priority', 'owner': 'owner', 'start_on': 'startOn',
'target_on': 'targetOn', 'progress': 'progress', 'notes': 'notes',
'completed_on': 'completedOn', 'result_configs': 'resultConfigs', 'status': 'status'}
GRAIN_LABELS = {'sku': 'SKU', 'asin': 'ASIN', 'parent_asin': 'Parent ASIN'}
LISTING_ACTIVITY_FIELDS = {'title': 'Title', 'item_name': 'Title', 'image': 'Main image',
'main_image': 'Main image', 'main_image_url': 'Main image',
'main_product_image_locator': 'Main image',
'description': 'Description', 'product_description': 'Description',
'bullet_point': 'Bullet points'}
#: R39: an old `ut_amazon_projects` status becomes (status, progress).
MOVE_STATUS = {'Open': ('In progress', 0), 'Done': ('Completed', 100)}
_GRAIN_WORDS = {'sku': 'sku', 'asin': 'asin', 'parent asin': 'parent_asin', 'parent_asin': 'parent_asin'}
MOVED_NAME = 'Untitled project'
LISTINGS = 'amzn_listings'
PRICING = 'amzn_pricing'
SEARCH_MAX = 500
OWNER_PREFIX = 'loopable-user:'
def owner_value(tenant, identity):
"""Encode a tenant-bound canonical account identity in the existing owner text column."""
value = {'tenant': _text(tenant, 'tenant', limit=128),
'username': _text(identity.get('username'), 'ownerUsername', limit=128).lower(),
'name': _text(identity.get('name'), 'owner name', limit=512)}
return _text(OWNER_PREFIX + json.dumps(value, ensure_ascii=False), 'owner')
def owner_identity(value, tenant=None):
"""Decode a stored account identity while preserving every legacy text owner."""
if not isinstance(value, str) or not value.startswith(OWNER_PREFIX):
return None
try:
identity = json.loads(value[len(OWNER_PREFIX):])
if (not isinstance(identity, dict)
or not all(isinstance(identity.get(k), str) and identity[k]
for k in ('tenant', 'username', 'name'))
or (tenant is not None and identity['tenant'] != tenant)):
return None
return identity
except (ValueError, TypeError):
return None
def _member_values(values, tenant):
"""Normalize unique contributor identities bound to the project's tenant."""
if not isinstance(values, (list, tuple)):
raise ValueError('Contributors must be a list of workspace users.')
members = {}
for identity in values:
if not isinstance(identity, dict) or identity.get('tenant') != tenant:
raise ValueError('Each contributor must belong to this workspace.')