content-tracker.ts

config/content-tracker.ts

1. /**
2. * The Content Creation tracker: the Google Sheet where the designers' and
3. * marketing's individual internal work actually lives.
4. *
5. * It is a source because most internal tasks live in this spreadsheet and
6. * nowhere else: Ninety carries one rollup row ("Weekly product photo refresh")
7. * for work that is a dozen separate jobs, so without the sheet the designers
8. * read as nearly idle. Reading the sheet directly beats pushing this work into
9. * Ninety (which would crowd it) or back into Bitrix tasks (which nobody works).
10. * The Ninety rollup stays; this supplies the detail underneath it.
11. *
12. * How it is read, and what that implies: Google's `gviz` endpoint returns CSV
13. * for a tab with no credential. That is what makes a Cloudflare Worker cron
14. * able to read it at all, and it is only true because the document is shared
15. * "anyone with the link": anyone holding this URL can read the sheet. If that
16. * sharing is ever tightened the source fails closed, loudly, which is correct,
17. * but nobody should be surprised by either state.
18. *
19. * No staleness signal (rule 11). The gviz response carries no `Last-Modified` and the
20. * document's `modifiedTime` needs the Drive API, which needs a credential the
21. * Worker does not have. So "how fresh is this" is genuinely unknown for this
22. * source, and renders as not tracked rather than as fresh. A hand-maintained
23. * spreadsheet is the input most likely to go quietly stale and the one where we
24. * can say the least about it, and that combination is stated on the page.
25. */
27. export const TRACKER_SHEET_ID = 'EXAMPLE_SHEET_ID_REPLACE_ME'
29. export const TRACKER_TITLE = 'Content Creation Tracker'
31. export const TRACKER_URL =
32. `https://docs.google.com/spreadsheets/d/${TRACKER_SHEET_ID}/edit`
34. /**
35. * The tabs that hold assignable work, by gid, as read from the document.
36. *
37. * Two tabs are deliberately absent, and that is the reason this is a list
38. * rather than "every tab":
39. *
40. * 100000004 Resources : image dimensions and gallery ordering.
41. * Reference material, no owner column, not work.
42. * 100000005 Product matrix : one row per SKU with per-image TRUE/FALSE
43. * coverage flags. It is a checklist about
44. * products, not work assigned to a person, and
45. * folding it in would attribute dozens of rows
46. * to nobody.
47. *
48. * A tab added to the sheet later will not appear here, and will therefore not
49. * appear on the board. That is a real gap and `unknownTabs` is not a thing we
50. * can detect without the Drive API, so it is written down here instead: if
51. * someone says their work is missing, check for a new tab first.
52. */
53. export const TRACKER_TABS: { gid: string; label: string }[] = [
54. { gid: '0', label: 'Images' },
55. { gid: '100000001', label: 'Video & Photo' },
56. { gid: '100000002', label: 'Copy & SEO' },
57. { gid: '100000003', label: 'Newsletter' },
58. ]
60. /**
61. * What a Status cell means for workload.
62. *
63. * Mapped onto the vocabulary the board already uses, not a new one. `ours` is
64. * work someone owes a move on; `waiting` is finished by them and blocked on a
65. * step somebody else takes; `terminal` is done. This is the same three-way split
66. * `classifyOrder()` applies to Plinth statuses, and it exists for the same reason:
67. * counting waiting-on-someone-else as backlog is how a dashboard makes a team
68. * look behind when it is not.
69. *
70. * "Ready to Launch" is the judgement call, and it is called out because it is
71. * the one a reader might disagree with. The design work is done; the launch is
72. * not. Read
73. * as `waiting` it says "they finished, the site has not caught up"; read as
74. * `ours` it would add items to a person's board for work they have already
75. * delivered. `waiting` is the reading that does not make someone look
76. * slow for something they finished.
77. *
78. * The sheet also has a `Completed` TRUE/FALSE column. It is not used: it
79. * disagrees with Status on live rows ("Ready to Launch" appears with both TRUE
80. * and FALSE) and nothing observable says which one is authoritative. Two
81. * conflicting signals, one of them picked silently, is how you get a
82. * confidently wrong number. Status is the one with meaning in its
83. * values, so Status is the one that decides, and the disagreement is counted and
84. * reported rather than resolved by guess.
85. */
86. export type TrackerClass = 'ours' | 'waiting' | 'terminal'
88. const STATUS_CLASS: Record<string, TrackerClass> = {
89. 'in progress': 'ours',
90. 'not started': 'ours',
91. 'in adjustments': 'ours',
92. 'ready to launch': 'waiting',
93. launched: 'terminal',
94. }
96. /**
97. * Classify a Status cell. Returns null for anything unrecognised, including a
98. * blank one, never a default.
99. *
100. * Rule 2 is the reason for the null: a default that stands in for knowledge
101. * must be surfaced every run. Blank and unknown rows are counted as
102. * unclassified and named on the page, not quietly assigned a meaning.
103. */
104. export function classifyTrackerStatus(status: string | null | undefined): TrackerClass | null {
105. const key = (status ?? '').trim().toLowerCase()
106. if (!key) return null
107. return STATUS_CLASS[key] ?? null
108. }