Skip to main content

Nexgen V3 permissions

There is a skill in the repo with a lot of details about how permissions work in V3.

Granting staff access to online modules​

This command will give a user access to the V3 modules without having to buy anything.

copilot svc exec -a nexgen-v3 -e staging -n nexgen-backend --command "sh -c 'su -s /bin/sh www-data -c "php artisan staff:grant-online-access cawood@axxya.com --dry-run"'"

Drop --dry-run to apply it, and the same wrapper works for --until and --revoke:

copilot svc exec -a nexgen-v3 -e staging -n nexgen-backend --command "sh -c 'su -s /bin/sh www-data -c "php artisan staff:grant-online-access cawood@axxya.com"'"

--dry-run: reports every row it would write and touches nothing. --years=N: subscription length, default 10. Only ever pushes the expiry later, so a plain re-run cannot shorten someone's access. --until=YYYY-MM-DD: exact expiry date. Authoritative, so it can bring the date forward. --revoke: turns the access off again.

--dry-run combines with any of the others, so --until=2027-01-31 --dry-run and --revoke --dry-run both preview without writing. And --revoke refuses to run alongside --until (exit 1), since one turns access off and the other sets an expiry; it silently ignores --years.

Granting a customer all label regions​

Every launched label region now sells for one price, so a customer who paid for Food Labeling should be able to work in all four of them: USA, Canada, UK/EU, and China/HK. India has never launched and is never granted. Use this when support reports that a customer is missing a region, including the common case of an account that was sold one of the old region-specific SKUs, or one whose second region stopped working after a renewal.

Region rights are the intersection of two grants: the per-user rows and the regions carried by the customer's active orders. See Entitlement follows the purchase for the catalog side. Widening one half alone does nothing, which is why this is a command rather than a one-line update.

The command attaches the client to a hidden, zero-cost product that carries every launched region. It does not touch Chargebee, the customer's own subscription, or their billing, and it does not widen the SKU they bought, which is shared with every other customer on it. The grant expires with the Food Labeling subscription it rides on, so it cannot outlive what the customer paid for.

Preview first. Nothing is written until you drop --dry-run:

copilot svc exec -a nexgen-v3 -e staging -n nexgen-backend --command "sh -c 'su -s /bin/sh www-data -c "php artisan customer:grant-label-regions customer@example.com --dry-run"'"

Then apply it:

copilot svc exec -a nexgen-v3 -e staging -n nexgen-backend --command "sh -c 'su -s /bin/sh www-data -c "php artisan customer:grant-label-regions customer@example.com"'"

The argument is the customer's login email, or a numeric user id. Every active login under that client is granted, and suspended or deleted logins are skipped.

--dry-run: reports every row it would write and touches nothing. --until=YYYY-MM-DD: exact expiry date. Only needed when the client has no active Food Labeling order; without it the command refuses, so a region grant can never quietly resurrect a lapsed account. --revoke: turns the grant off again. It deletes nothing, so re-granting is one command.

The command is additive and idempotent: a re-run on an account that already has the grant reports zero writes.

The customer must log out and back in. Regions, languages, and permissions are all resolved at login, so nothing changes in a session that is already open.

Production has no artisan

Prod often has no running nexgen-backend task, in which case copilot svc exec ... --name nexgen-backend fails with "found no running task". Either scale the service up first, or apply the same rows by hand from the bastion using the SQL below.

Checking what a customer actually has​

From the bastion, read-only. orders_subscriptions.userid is the client id, not the user id.

copilot svc exec --app nexgen-v3 --env prod --name bastion
mysql -h $DB_HOST -u $DB_USERNAME -p"$DB_PASSWORD" nexgen
SET @email = 'customer@example.com';
SET @client := (SELECT client_id FROM users WHERE email = @email);

SELECT o.id, o.contract_id, o.status, o.subscription_status, o.in_app_status, o.until_date,
       p.title, p.category_id, GROUP_CONCAT(pir.region_id ORDER BY pir.region_id) AS regions
FROM orders_subscriptions o
LEFT JOIN product_items pi ON pi.contract_id = o.contract_id
LEFT JOIN products p ON p.id = pi.product_id
LEFT JOIN product_item_regions pir ON pir.item_id = pi.id
WHERE o.userid = @client
GROUP BY o.id ORDER BY o.until_date DESC;

SELECT u.id, u.email, u.active,
       GROUP_CONCAT(DISTINCT cur.region_id ORDER BY cur.region_id) AS user_regions
FROM users u
LEFT JOIN client_user_regions cur ON cur.user_id = u.id
WHERE u.client_id = @client AND u.deleted_at IS NULL
GROUP BY u.id;

A region shows up in the app only when it is on both sides of that pair, on an order with in_app_status = 1 and subscription_status = 'A', and the login holds a user_subscriptions seat with status = 1 on that order.

Applying the grant by hand​

Only for when artisan is unavailable. Run the block in order, in one session, so the variables survive. Every statement is guarded, so re-running it writes nothing.

SET @email = 'customer@example.com';
SET @client := (SELECT client_id FROM users WHERE email = @email);
SET @until := (SELECT MAX(o.until_date) FROM orders_subscriptions o
               JOIN product_items pi ON pi.contract_id = o.contract_id
               JOIN products p ON p.id = pi.product_id
               WHERE o.userid = @client AND o.in_app_status = 1
                 AND LOWER(o.subscription_status) = 'a'
                 AND o.contract_id <> 9100011 AND p.category_id = 1);
SELECT @client, @until;  -- both must be non-null before you go on

INSERT INTO products (product_id, type, title, slug, features, benefits, description, long_description,
                      great_for, technical_specs, category_id, is_active, do_not_display, display_order,
                      constant_contact_id, meta_title, meta_keyword, meta_description, created_at, updated_at)
SELECT 1, 'web', 'Food Labels Online - All Regions (granted)', 'food-labels-online-all-regions-granted',
       '', '', 'Every launched label region, granted by support. Not for sale.', '', '', '',
       1, 0, 1, 0, 0, '', '', '', NOW(), NOW()
FROM dual WHERE NOT EXISTS (SELECT 1 FROM products WHERE title = 'Food Labels Online - All Regions (granted)');
SET @product := (SELECT id FROM products WHERE title = 'Food Labels Online - All Regions (granted)');

INSERT INTO product_items (product_id, contract_id, region_id, is_subscription, duration, duration_label, price, created_at, updated_at)
SELECT @product, 9100011, 1, 1, 365, '365 Days', 0.00, NOW(), NOW()
FROM dual WHERE NOT EXISTS (SELECT 1 FROM product_items WHERE contract_id = 9100011);
SET @item := (SELECT id FROM product_items WHERE contract_id = 9100011);

INSERT INTO product_item_regions (item_id, region_id)
SELECT @item, r.id FROM regions r
WHERE r.name <> 'India' AND r.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM product_item_regions x WHERE x.item_id = @item AND x.region_id = r.id);

INSERT INTO product_item_languages (item_id, language_id)
SELECT @item, l.id FROM languages l
JOIN regions r ON r.id = l.region_id AND r.name <> 'India' AND r.deleted_at IS NULL
WHERE l.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM product_item_languages x WHERE x.item_id = @item AND x.language_id = l.id);

INSERT INTO orders_subscriptions (userid, order_id, contract_id, quantity, is_subscription, contract_cost,
                                  status, subscription_status, in_app_status, until_date, serial_key, created_at, updated_at)
SELECT @client, -1, 9100011,
       (SELECT COUNT(*) FROM users WHERE client_id = @client AND active = 1 AND deleted_at IS NULL),
       1, 0.00, 'Approved', 'A', 1, @until, '', NOW(), NOW()
FROM dual WHERE NOT EXISTS (SELECT 1 FROM orders_subscriptions WHERE userid = @client AND contract_id = 9100011);
SET @sub := (SELECT id FROM orders_subscriptions WHERE userid = @client AND contract_id = 9100011);

INSERT INTO client_contracts (client_id, contract_id, no_of_units, purchased_date, expiry_date, status, created_at, updated_at)
SELECT @client, @item, 1, NOW(), @until, 1, NOW(), NOW()
FROM dual WHERE NOT EXISTS (SELECT 1 FROM client_contracts WHERE client_id = @client AND contract_id = @item);

INSERT INTO user_subscriptions (user_id, subscription_id, status)
SELECT u.id, @sub, 1 FROM users u
WHERE u.client_id = @client AND u.active = 1 AND u.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM user_subscriptions us WHERE us.user_id = u.id AND us.subscription_id = @sub);

INSERT INTO client_user_regions (user_id, region_id)
SELECT u.id, r.id FROM users u
JOIN regions r ON r.name <> 'India' AND r.deleted_at IS NULL
WHERE u.client_id = @client AND u.active = 1 AND u.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM client_user_regions x WHERE x.user_id = u.id AND x.region_id = r.id);

INSERT INTO client_user_languages (user_id, lang_id)
SELECT u.id, l.id FROM users u
JOIN languages l ON l.deleted_at IS NULL
JOIN regions r ON r.id = l.region_id AND r.name <> 'India' AND r.deleted_at IS NULL
WHERE u.client_id = @client AND u.active = 1 AND u.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM client_user_languages x WHERE x.user_id = u.id AND x.lang_id = l.id);

To undo it, deactivate the granted order and its seats. Nothing is deleted, so the grant can be turned back on by setting the same columns the other way:

UPDATE orders_subscriptions SET in_app_status = 0, status = 'In-Active', subscription_status = 'C', updated_at = NOW()
WHERE userid = @client AND contract_id = 9100011;

UPDATE user_subscriptions SET status = 0 WHERE subscription_id = @sub;

Verifying​

Re-run the two SELECTs above: the granted order should be Approved / A / in_app_status = 1, carrying all four regions, and every active login should hold all four client_user_regions rows and a seat on it. Then have the customer sign out and back in, open Create Label, and confirm all four region tabs appear with a language available in each. An empty language dropdown on a region means the language half of the grant did not land.