runloopable / platform /core /amazon_projects.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
95fb175 verified
Raw History Blame Contribute Delete
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.')