Mock CTO Interview

# ======================================================================
# Q: CTO: "How do you ensure that one customer cannot see another customer's
# orders?"
#
# You: "This is BOLA, the number 1 vulnerability in the OWASP API Top 10. I
# don't rely on the customer not knowing the IDs. The queryset is always
# filtered by tenant in get_queryset(), so a foreign id returns 404 and not 403,
# so it doesn't even leak that it exists. I have a test for every endpoint. I
# also use UUIDs for public IDs, but as an additional measure, not as the
# solution."
# ======================================================================

class OrderViewSet(viewsets.ReadOnlyModelViewSet):
    serializer_class = OrderV1Serializer
    permission_classes = [IsAuthenticated]

    def get_queryset(self):
        return Order.objects.filter(company_id=self.request.user.company_id)

def test_cannot_read_other_company_order(api_client, user_a, order_of_b):
    api_client.force_authenticate(user_a)
    r = api_client.get(f'/api/v1/orders/{order_of_b.pk}/')
    assert r.status_code == 404

# ======================================================================
# Q: CTO: "A large B2B customer wants a change in the response format. How do
# you do it without breaking the others?"
#
# You: "If the change is adding a field, it is not breaking and I ship it in v1.
# If it changes or removes fields, I release v2 with URL versioning. The
# business logic stays shared, only the serializer changes. On v1 I send
# Deprecation and Sunset headers, watch the logs to see who still uses it, and
# notify them before shutting it down."
# ======================================================================

class OrderViewSet(viewsets.ReadOnlyModelViewSet):
    def get_serializer_class(self):
        return OrderV2Serializer if self.request.version == 'v2' else OrderV1Serializer

    def finalize_response(self, request, response, *args, **kwargs):
        response = super().finalize_response(request, response, *args, **kwargs)
        if request.version == 'v1':
            response['Deprecation'] = 'true'
            response['Sunset'] = 'Wed, 31 Mar 2027 00:00:00 GMT'
        return response

# ======================================================================
# Q: CTO: "A report query takes 12 seconds. Show me how you approach it."
#
# You: "I get the SQL with queryset.explain(analyze=True, buffers=True) or from
# pg_stat_statements. I look for a seq scan on a large table, a big gap between
# estimated and actual rows, and a sort that spills to disk. Usually the fix is
# a composite index with the equality column first, partial if we always filter
# by status. I build the index CONCURRENTLY. If the report doesn't need fresh
# data, I send it to the read replica or to a materialized view refreshed by
# Celery."
# ======================================================================

qs = (Order.objects.filter(company_id=cid, status='paid',
                           created_at__gte=since)
      .values('created_at__date').annotate(total=Sum('total')))
print(qs.explain(analyze=True, buffers=True))

class Meta:
    indexes = [models.Index(
        fields=['company', 'created_at'], include=['total'],
        condition=Q(status='paid'), name='order_paid_company_created')]

# ======================================================================
# Q: CTO: "We want to add a mandatory column to a table with 50 million rows.
# How do you do it without downtime?"
#
# You: "In three releases. 1) A nullable column with lock_timeout, and the new
# code writes the value. 2) Backfill in batches with keyset pagination and a
# short pause, so I don't overload the database and the replicas. 3) A CHECK
# constraint NOT VALID, then VALIDATE in a separate migration, which does not
# block writes. Since PostgreSQL 12, SET NOT NULL uses the validated constraint
# and does not scan the table again."
# ======================================================================

# Step 2: management command for the backfill
last_pk = 0
while True:
    ids = list(Order.objects.filter(pk__gt=last_pk, channel__isnull=True)
               .order_by('pk').values_list('pk', flat=True)[:5000])
    if not ids:
        break
    Order.objects.filter(pk__in=ids).update(channel='web')
    last_pk = ids[-1]
    time.sleep(0.2)

# Step 3a: migration
operations = [AddConstraintNotValid('order', models.CheckConstraint(
    condition=Q(channel__isnull=False), name='order_channel_not_null'))]
# Step 3b: separate migration
operations = [ValidateConstraint('order', 'order_channel_not_null')]

# ======================================================================
# Q: CTO: "The product page receives 2,000 req/s. How do you cache it? What
# happens when the price changes?"
#
# You: "CDN for anonymous users, Redis with cache-aside for the data. When the
# price changes, I invalidate inside on_commit, so a price from a rolled-back
# transaction does not remain in the cache. I add jitter to the TTL so entries
# do not all expire together, and a lock or stale-while-revalidate for
# stampedes. The price at checkout is always read from the database, never from
# cache." Chapter 5.
# ======================================================================

def product_payload(pid):
    key = f'product:{pid}'
    data = cache.get(key)
    if data is None:
        data = ProductSerializer(Product.objects.get(pk=pid)).data
        cache.set(key, data, timeout=300 + random.randint(0, 60))  # TTL jitter
    return data

def change_price(product, new_price):
    with transaction.atomic():
        product.price = new_price
        product.save(update_fields=['price'])
        transaction.on_commit(lambda: cache.delete(f'product:{product.pk}'))